Shelly CashmanExcel 2016 | Module 3: SAM Project 1b Speakerbox Electronics WORKING WITH LARGE WORKSHEETS, CHARTING, AND WHAT-IF ANALYSIS GETTING STARTED Open the fleSC_EX16_3b_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought10

Share With

Shelly CashmanExcel 2016 | Module 3: SAM Project 1b

Speakerbox Electronics

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

GETTING STARTED

Open the fleSC_EX16_3b_FirstLastName_1.xlsx, available ±or download±rom the SAM website.

Save the fle asSC_EX16_3b_FirstLastName_2.xlsxby changing the “1” to a“2”.

oI± you do not see the.xlsxfle extension in the Save As dialog box, do nottype it. The program will add the fle extension ±or you automatically.

With the fleSC_EX16_3b_FirstLastName_2.xlsxstill open, ensure that yourfrst and last name is displayed in cell B6 o± the Documentation sheet.oI± cell B6 does not display your name,

delete the fle and download a newcopy ±rom the SAM website.

PROJECT STEPS

1.Roz is a sales associate ±or an electronics store called Speakerbox Electronics.She is conducting a detailed analysis o± her sales ±or the frst hal± o± 2018,including graphical representations o± this in±ormation.Switch to the2018 Jan-Jun Salesworksheet, and then remove the panes ±romthe worksheet. (Hint: Deselect the Split option ±rom the View tab.)

2.Freeze rows 1 and 2. (Hint: Select cell A3 be±ore ±reezing panes.

3.Change the width o± column A to35.00. (Hint: Do not use AutoFit.)

4.Select the range B5:D5 and, using the Fill Handle, fll the range E5:G5 withconsecutive months.

5.In cell H6, create aLine Sparklinebased on the data in the rangeB6:G6.Copy the Line Sparkline you created in cell H6 to the range H7:H10.

6.Using the Format Painter, copy the ±ormatting ±rom the range G6:G10 to therange H6:H10.

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

8.Apply thePercentagenumber ±ormat withzerodecimal places to the rangeB13:G13 and the nonadjacent cell B33.

9.Select the merged range A15:A27, and then rotate the cell content to0degrees.

10.In the stacked column chart (with the title Monthly Sales per Item), addMonthas the primary horizontal axis title andSales Amountas the primary verticalaxis title.

11.In the stacked column chart (with the title Monthly Sales per Item), change thenumber format of the vertical axis toAccountingstyle withzerodecimalplaces.

12.In the 3-D pie chart (titled Share of Total Sales), change the chart style toStyle7(7thcolumn, 1strow[Mac Hint: 3rdcolumn, 2ndrow]of the Chart Styles palette).

13.Add data labels to the 3-D pie chart using theOutside Endstyle.

14.Roz wants to calculate her gross monthly sales commissions. The rule is thatshe gets a 2.7% commission on the total sales amount every month, and shereceives 3.5% if her sales exceeded $80,000 in a given month.In cell B31, create a formula using theIFfunction to check whether the value ofcellB10isgreater than 80000.

a.If this condition is true, the function should multiply cellB10by3.5%todetermine Roz’s commission. (Hint: For the if true value, useB10*.035.)

b.If this condition is false, the function should multiply the value in cellB10by2.7%to determine Roz’s commission. (Hint: For the if false value, useB10*.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 cellB31(gross monthlycommission) by cellB33(net commission percentage). Use anabsolutereference to cell B33 (because the net commission percentage won’t change)and arelativereference 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 toLine.

b.Change the Sparkline style toSparkline Style Dark #4(4th column, 5throw of the Sparkline Styles palette[Mac Hint: 7thcolumn, 4throw]). (Hint:Depending on your version of O±ce, the Sparkline style may appear asBlack, Sparkline Style Dark #4.)

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

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

19.In cell B40, insert a formula using theNOWfunction to enter the system currentdate into the cell, and then apply theShort Datenumber format (e.g.,5/18/2019).

20.Use the Recommended Charts tool to create aClustered Columnchart basedon the rangeA30:G32with the following options:

a.Move the chart to its own chart sheet, and then enterSalesCommissionsas the name of the new chart sheet. If necessary, reposition the worksheet so it is located after the2018 Jan-Jun Salesworksheet.

b.UseSales Commissions2018as the chart title.

c.In the clustered column chart addMonthas the primary horizontal axistitle andCommissionas the primary vertical axis title.

Your workbook should look like the Final Figures on the following pages. (The value in cellB36 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 theSAM website to submit your completed project.

Final Figure 1: 2018 Jan-Jun Sales Worksheet

Final Figure 2: Sales Commissions Worksheetf