ComputerScienceExpert
$18/per page/
ATTENTION
Anyone with expertise in Microsoft Office Programs, this assignment is for you.
Attached are two separate projects,
Microsoft Office WORD Capstone: and EXCEL
Following instructions to make
*Use the 4IL_EX16_CS1-4a_ColtonMentzer_1 to work from, and have it replicate the final result shown on the last page of the instructions. The instructions page will provide every step you need to complete this
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file
from the SAM website. Illustrated Excel 2016 | Modules 1–4: SAM Capstone Project 1a Merchandise Projections
FORMATTING, FORMULAS, AND CHARTS
Colton Mentzer ot edit this sheet. If your name does not appear in cell B6, please download a new copy of the file
from the SAM website. A Little Purple Productions
Unit Averages Aggregate Sales
Discs
DVDs
CD Soundtracks
Clothing
Hoodies
T-shirts
Tank tops
Hats
Baseball Caps
Bennies
Bags
Plushies
Posters
Buttons
Vinyl Decals
TOTAL Units Sold rcent of Sales Selling Price Production Cost $49,093
$52,637 12.9500
7.9500 $1.05
$0.70 $75,863
$22,298
$20,074 29.9500
15.9500
13.9500 $13.80
$7.19
$5.34 $37,525
$34,413
$53,835
$19,853
$25,991
$29,923
$40,724
$462,229 24.9500
18.9500
14.9500
17.9500
11.9500
5.9500
4.9500 $8.69
$3.51
$4.65
$10.02
$4.47
$1.28
$1.93 High:
Low:
Average: s Averages
Profit
$11.90
$7.25
$16.15
$8.76
$8.61
$16.26
$15.44
$10.30
$7.93
$7.48
$4.67
$3.02 Convention Booth - Sales Forecast
Discs
Clothing
Hats
Bags
Plushies
Posters
Buttons
Vinyl Decals
Total 2018
101730.4
118235.5
71938
53834.95
19852.7
25991.25
29922.55
40723.65 2019
104500
119000
74000
54000
20500
28000
32000
43000 Trend
107200
123000
76000
57200
22000
30000
39000
44500 109900
127000
78000
58000
23500
36000
44000
47000 112600
131000
81000
59500
25000
40000
47000 55000 Sales Forecast by Year
$113K Vinyl Decals
Buttons
Posters
Plushies
Bags
Hats
Clothing
Discs2019 2018
$- $131K $110K $127K $107K $123K $105K $119K $102K $118K
$100,000 $81K $78K $58K $76K $74K $72K
$200,000 $60K $57K $54K $54K
$300,000 $25K $40K $24K $36K $22K $30K $400,000 $44K $39K $21K $28K $32K $20K $26K $30K $47K $47K $45K $43K $41K
$500,000 $40K 36K 0 $44K $39K 32K K $47K $47K $45K $43K $41K
$500,000 $600,000
Illustrated Excel 2016 | Modules 1–4: SAM Capstone Project 1a Merchandise Projections
FORMATTING, FORMULAS, AND CHARTS GETTING STARTED Open the file IL_EX16_CS1-4a_FirstLastName_1.xlsx, available for download
from the SAM website. Save the file as IL_EX16_CS1-4a_FirstLastName_2.xlsx by changing the “1”
to a “2”.
o If you do not see the .xlsx file extension in the Save As dialog box, do not
type it. The program will add the file extension for you automatically. With the file IL_EX16_CS1-4a_FirstLastName_2.xlsx still open, ensure that
your first and last name is displayed in cell B6 of the Documentation sheet.
o If cell B6 does not display your name, delete the file and download a new
copy from the SAM website. PROJECT STEPS
1. Glenn Mills is an intern at A Little Purple Productions, a web-based comedy
production company that sells clothing and other products at conventions to
promote their shows. Glenn’s supervisor asked him to make a worksheet
detailing how various products have sold.
Switch to the Sheet1 worksheet. Make the following formatting changes: 2. 3. a. Merge and center the range C3:E3. Apply the Heading 2 cell style to
the merged cell, and then change the fill color to Blue, Accent 5,
Lighter 60% (9th column, 3rd row of the Theme Colors palette). b. In the range B20:H20, apply the Blue, Accent 1 fill color (5th column, 1st
row of the Theme Colors palette). c. Apply the Thick Outside Borders border style to the range B3:H20. d. Add a Left Border to the ranges C3:C20 and F3:F20. Make the following formatting changes to the range C4:H4:
a. Change the font to Verdana. b. Change the font color to Blue-Gray, Text 2 (4th column, 1st row of the
Theme Colors palette). c. Change the font size to 10 pt. d. Bold and center the cell contents. e. Apply wrap text formatting. Italicize the text in the nonadjacent ranges B6:B7, B9:B11, and B13:B14. Illustrated Excel 2016 | Modules 1–4: SAM Capstone Project 1a
4. Glenn notices that some of the sales figures weren’t calculated correctly.
In cell D6, create a formula to calculate the number of units of DVDs sold by
dividing the total sales values (C6) by the selling price value (F6).
Copy the formula from cell D6 to cell D7, and the ranges D9:D11, and D13:D19. 5. In cell D20, create a formula using the SUM function that calculates the total
number of units sold (D6:D19). 6. In cell E6, create a formula to calculate the percentage of total sales accounted
for by DVDs. The formula will divide DVD sales in C6 by total sales in C20. Use
an absolute reference to the total sales in cell C20, and a relative reference
to DVD sales in cell C6.
Copy the formula from cell E6 to cell E7, and the ranges E9:E11, and E13:E19. 7. Apply the Percentage number format with zero decimal places to the range
E6:E19. 8. Apply the Currency number format with two decimal places to the range
F6:F19. 9. Glenn would like the worksheet to have a summary section for some key sales
numbers.
In cell H22, create a formula using the MAX function that calculates the highest
average unit profit across A Little Purple Productions’ product lines (H6:H19). 10. In cell H23, create a formula using the MIN function that calculates the lowest
average unit profit across A Little Purple Productions’ product lines (H6:H19). 11. In cell H24, create a formula using the AVERAGE function that calculates the
average unit profit across A Little Purple Productions’ product lines (H6:H19). 12. Use conditional formatting to apply Solid Fill Orange Data Bars to the range
H6:H19. 13. In the worksheet, find and replace the misspelled word “Bennies” with the
correctly spelled word Beanies. 14. Rename the Sheet1 worksheet Merchandise Sales, then apply the Green,
Accent 6 tab color (10th column, 1st row of the Theme Colors palette) to the
sheet tab. 15. To help figure out a company strategy for the coming years, Glenn decides to
work with some sales projections from his boss.
Go to the Projection Data worksheet. Move the contents of cell G14 to cell G11. 16. Fill the range E3:G3 with a number series based on the contents of the range
C3:D3. 17. Make the following changes to the column widths and row heights: 18. a. Change the width of column B to best fit its contents (using AutoFit). b. Change the width of columns C through G to 14.0 characters. c. Change the height of row 2 to 40 pt. Format the range C4:G12 with the Accounting number format with zero
decimal places and use $ as the symbol. (Hint: Depending on how you complete
this action, the number format may appear as Custom instead of Accounting.) Illustrated Excel 2016 | Modules 1–4: SAM Capstone Project 1a
19. In cell C12, create a formula using the SUM function to total the values in the
range C4:C11.
Copy the formula from cell C12 to the range D12:G12. 20. Apply the Thick Outside Borders border style to the range B2:H12. 21. Insert a 2-D pie chart based on the range B4:C11 to graph the 2018 sales by
product line. Update the pie chart as described below: 22. 23. a. Apply the Style 11 chart style to the 2-D pie chart. b. Enter the text 2018 Sales by Product as the chart title. c. Reposition the chart so its upper-left corner is within cell B15 and its lowerright corner is within cell G29. Select the range B3:G11 and create a clustered column chart showing the
sales forecasted from 2018 through 2022 for A Little Purple Productions’ product
line. (Hint: The data should be grouped by product in your chart.) Update the
chart as described below:
a. Apply the Style 7 chart style to the clustered column chart. b. Enter the text Sales Forecast by Product as the chart title. c. Add a primary vertical axis title with the text Amount and a primary
horizontal axis title with the text Product. d. Resize and reposition the chart so that its upper-left corner is in cell J2 and
its lower-right corner is in cell R15. Insert a header in the worksheet. Type Projection Data in the center header
section and 8/24/2018 in the right header section.
Switch back to Normal View. 24. Hide the gridlines in the worksheet. 25. Go to the Projection Chart worksheet. Make the following changes to the
stacked bar chart:
a. Reposition the chart legend from the Left position to the Right position. b. Add primary major vertical gridlines to the chart. c. Add a primary vertical axis title with the text Year and a primary
horizontal axis title with the text Sales Amount. d. Add an Offset Bottom Outer shadow Shape Effect to the title. [Mac Hint:
In the Format Chart Title pane, change the Shadow Distance to 3pt.] Your workbook should look like the Final Figures on the following pages. Save your
changes, close the workbook, and then exit Excel. Follow the directions on the SAM
website to submit your completed project. Illustrated Excel 2016 | Modules 1–4: SAM Capstone Project 1a
Final Figure 1: Merchandise Sales Worksheet Final Figure 2: Projection Data Worksheet Illustrated Excel 2016 | Modules 1–4: SAM Capstone Project 1a
Final Figure 3: Projection Chart Worksheet