Homework answers / question archive /   Shelly Cashman Excel 2016 | Module 3: SAM Project 1a Pick Up Motors WORKING WITH LARGE WORKSHEETS, CHARTING, AND WHAT-IF ANALYSIS GETTING STARTED ? Open the file SC_EX16_3a_ FirstLastName _1

  Shelly Cashman Excel 2016 | Module 3: SAM Project 1a Pick Up Motors WORKING WITH LARGE WORKSHEETS, CHARTING, AND WHAT-IF ANALYSIS GETTING STARTED ? Open the file SC_EX16_3a_ FirstLastName _1

MS Excel

Share With

---

 

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

Pick Up Motors

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

GETTING STARTED

?

Open the file

SC_EX16_3a_

FirstLastName

_1.xlsx

, available for download

from the SAM website.

?

Save the file as

SC_EX16_3a_

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

SC_EX16_3a_

FirstLastName

_2.xlsx

still open, ensure that your

first and last name is displayed in cell B6 of the Documentation worksheet.

o

If cell B6 does not display your name, delete the file and download a new

copy from the SAM website.

PROJECT STEPS

1.

Charlie is a salesman at Pick Up Motors car dealership. He is performing a

detailed analysis of his sales for the first half of 2018, including representing his

sales and commissions with graphs.

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

31.00

. (

Hint

: Do not use AutoFit.)

4.

Select the range B4:D4 and, using the Fill Handle, fill the range E4:G4 with

consecutive months.

5.

In cell H5, create a

Column Sparkline

based on the data in the range

B5:G5

.

Copy the Sparkline you created in cell H5 to the range H6:H9.

6.

Using the Format Painter, copy the formatting from the range G5:G9 to the

range H5:H9.

7.

Copy the contents of the range B4:G4 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 contents to

90

degrees

(

Hint

: The text should read from bottom to top).

10.

In the stacked column chart (with the title Monthly Sales per Vehicle Type), add

the primary horizontal axis title

Month

and the primary vertical axis title

Sales

Amount

.

 

Shelly Cashman

Excel

2016 |

Module 3: SAM Project 1a

11.

In the stacked column chart (with the title Monthly Sales per Vehicle Type),

change the number format of the vertical axis to

Accounting

style with

zero

decimal places.

12.

In the 3-D pie chart (titled Monthly Sales Contribution to Six-Month Sales Total)

change the chart style to

Style 1

(1

st

column, 1

st

row of the Chart Styles

palette).

13.

Add data labels to the 3-D pie chart using the

Outside End

style.

14.

Charlie wants to calculate his gross monthly sales commissions. The rule is that

he receives a commission of 2.5% of his total monthly sales if he sold more than

$400,000 worth of vehicles. Otherwise, he only gets 1.5% of his total monthly

sales amount.

In cell B31, create a formula using the

IF

function to check whether the value of

cell

B9

is

greater than

400000

.

a.

If this condition is true, the function should multiply cell

B9

by

2.5%

to

determine Charlie’s commission. (

Hint

: For the if true value, use

B9*0.025

.)

b.

If this condition is false, the function should multiply the value in cell

B9

by

1.5%

to determine Charlie’s commission. (

Hint

: For the if false value,

use

B9*0.015

.)

Copy the formula in B31 to the range C31:G31.

15.

After deductions, Charlie’s net monthly commission is only 85% of his gross

monthly commission.

In cell B32, create a formula that multiplies the value of cell

B31

(Charlie’s

gross monthly commission) by the value of cell

B33

(Charlie’s 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 Line Sparklines as described below:

a.

Change the Sparkline type to

Column

.

b.

Change the Sparkline style to

Sparkline Style Dark #4

(4th column, 5th

row of the Sparkline Styles palette

[Mac Hint: 7

th

column, 4

th

row]

).

17.

In the range A31:A32,

increase the indent of the cell contents

once

.

18.

Charlie’s goal is to make a net commission of $60,000 for the second half of

2018 (July through December), using the estimate that his gross commission

rate will be an average of 2% (cell B37) across the second half of the year.

Select cell B39, and then use

Goal Seek

to determine what value of cell

B36

(Estimated 6 Month Sales Total)

is necessary to set the value of cell

B39

to

60000

.

Leave the result of the Goal Seek analysis as the new value of 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.,

3/15/2019).

20.

Use the Recommended Charts tool to create a

Clustered Column

chart based

on the range

A30:G32

with the following options:

 

Shelly Cashman

Excel

2016 |

Module 3: SAM Project 1a

a.

Move the chart to its own chart sheet, and then enter

Commissions

2018

as the name of the new chart sheet. If necessary, reposition the

worksheet so it is located before the

2018 Jan-Jun Sales

worksheet.

b.

Change the chart title to

Sales Commissions - January to June 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.

Final Figure 1: 2018 Jan-Jun Sales Worksheet

Final Figure 2: Commissions 2018 Worksheet