Homework answers / question archive / 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

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

MS Excel

Share With

---

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”.

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 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:

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.

2. 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.

3. Italicize the text in the nonadjacent ranges B6:B7, B9:B11, and B13:B14.

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:

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.

18. 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.)

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: 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 lower-right corner is within cell G29.

22. 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.

23. 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.

Final Figure 1: Merchandise Sales Worksheet

Final Figure 2: Projection Data Worksheet

Final Figure 3: Projection Chart Worksheet