Shelly Cashman Excel 2016 | Module 3: SAM Project 1b Speakerbox Electronics WORKING WITH LARGE WORKSHEETS, CHARTING, AND WHAT-IF analysis GETTING STARTED Open the file SC_EX16_3b_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Shelly Cashman Excel 2016 | Module 3: SAM Project 1b

Speakerbox Electronics

WORKING WITH LARGE WORKSHEETS, CHARTING, AND WHAT-IF analysis

GETTING STARTED

Open the file SC_EX16_3b_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as SC_EX16_3b_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 SC_EX16_3b_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. Roz is a sales associate for an electronics store called Speakerbox Electronics. She is conducting a detailed analysis of her sales for the first half of 2018, including graphical representations of this information. Switch to the 2018 Jan-Jun Sales worksheet, and then remove the panes from the worksheet. (Hint: Deselect the Split option from the View tab.)

2. Freeze rows 1 and 2. (Hint: Select cell A3 before freezing panes.)

3. Change the width of column A to 35.00. (Hint: Do not use AutoFit.)

4. Select the range B5:D5 and, using the Fill Handle, fill the range E5:G5 with consecutive months.

5. In cell H6, create a Line Sparkline based on the data in the range B6:G6. Copy the Line Sparkline you created in cell H6 to the range H7:H10.

6. Using the Format Painter, copy the formatting from the range G6:G10 to the range H6:H10.

7. Copy the contents of range B5:G5 to the range B12:G12.

8. Apply the Percentage number format with zero decimal places to the range B13:G13 and the nonadjacent cell B33.

9. Select the merged range A15:A27, and then rotate the cell content to 0 degrees.

10. In the stacked column chart (with the title Monthly Sales per Item), add the primary horizontal axis title Month and the primary vertical axis title Sales Amount.

11. In the stacked column chart (with the title Monthly Sales per Item), change the number format of the vertical axis to Accounting style with zero decimal places.

12. In the 3-D pie chart (titled Share of Total Sales), change the chart style to Style 7 (7th column, 1st row [Mac Hint: 3rd column, 2nd row] of the Chart Styles palette).

13. Add data labels to the 3-D pie chart using the Outside End style.

14. Roz wants to calculate her gross monthly sales commissions. The rule is that she gets a 2.7% commission on the total sales amount every month, and she receives 3.5% if her sales exceeded $80,000 in a given month. In cell B31, create a formula using the IF function to check whether the value of cell B10 is greater than 80000.

a. If this condition is true, the function should multiply cell B10 by 3.5% to determine Roz’s commission. (Hint: For the if true value, use B10*.035.)

b. If this condition is false, the function should multiply the value in cell B10 by 2.7% to determine Roz’s commission. (Hint: For the if false value, use B10*.027.) Copy the formula in B31 to the range C31:G31.

15. The net amount of Roz’s commissions after all deductions is 85% (cell B33). In cell B32, enter a formula that multiplies the value of cell B31 (gross monthly commission) by cell B33 (net commission percentage). Use an absolute reference to cell B33 (because the net commission percentage won’t change) and a relative reference to cell B31. Copy the formula in cell B32 to the range C32:G32.

16. In the range H31:H32, update the Column Sparklines as described below:

a. Change the Sparkline type to Line.

b. Change the Sparkline style to Sparkline Style Dark #4 (4th column, 5th row of the Sparkline Styles palette [Mac Hint: 7th column, 4th row]).

17. In the range A31:A32, increase the indent of the cell contents once.

18. Roz would also like to close 2018 with a net commission totaling $30,000, using the estimate that her gross commission rate will be an average of 3.1% (cell B37) from July through December. Select cell B39, and then use Goal Seek to determine what value of cell B36 (Estimated July-Dec Sales Total) is necessary to set the value of cell B39 to 30000. Leave the result of the Goal Seek analysis as the new value for cell B36.

19. In cell B40, insert a formula using the NOW function to enter the system current date into the cell, and then apply the Short Date number format (e.g., 5/18/2019).

20. Use the Recommended Charts tool to create a Clustered Column chart based on the range A30:G32 with the following options:

a. Move the chart to its own chart sheet, and then enter Sales Commissions as the name of the new chart sheet. If necessary, reposition the worksheet so it is located after the 2018 Jan-Jun Sales worksheet.

b. Change the chart title to Sales Commissions 2018.

c. In the clustered column chart add the primary horizontal axis title Month and the primary vertical axis title Commission.

 

Your workbook should look like the Final Figures on the following pages. (The value in cell B36 generated by the Goal Seek analysis has been intentionally blurred in Final Figure 1.) Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.