Homework answers / question archive / Shelly Cashman Excel 2019 | Module 3: End of Module Project 2 Avero International CREATE A SALES PROJECTION WORKSHEET GETTING STARTED ? Open the file SC_EX19_EOM3-2_ FirstLastName _1

Shelly Cashman Excel 2019 | Module 3: End of Module Project 2 Avero International CREATE A SALES PROJECTION WORKSHEET GETTING STARTED ? Open the file SC_EX19_EOM3-2_ FirstLastName _1

MS Excel

Share With

---

Shelly Cashman Excel 2019 | Module 3: End of Module Project 2

Avero International

CREATE A SALES PROJECTION WORKSHEET

GETTING STARTED

?

Open the file

SC_EX19_EOM3-2_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

SC_EX19_EOM3-2_

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_EX19_EOM3-2_

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.

Janelle Duong is a financial analyst for Avero International, a global company

in San Diego, California, that provides automation products to manufacturing

businesses. Janelle has been creating sales projections in an Excel workbook,

and has asked you to help her complete the worksheet.

Go to the

Sales

Projections

worksheet. Middle-align the contents of the

merged cell A1.

2.

Italicize the contents of cell H1. In cell I1, insert a formula that uses the NOW

function to display today's date.

3.

Apply the formatting in the merged cell D2 to the merged cell A13. Fill the

range C4:E8 with only the formatting from the range B4:B8.

4.

In cell F4, insert a Line sparkline based on the data in the range B4:E4. Fill the range

F5:F8 without formatting based on the contents of cell F4. Change the color of the

sparklines to Dark Blue, Text 2, and then add markers to the sparklines.

5.

Copy the formula in cell G4 and paste it in the range G5:G8, pasting only the

formula and number formatting.

6.

Use Goal Seek to set the average number of projects (cell

H8

) to the value of

120

by

changing the average number of manufacturing component projects (cell

H6

).

7.

Merge and center the range I3:I8. Rotate the text down to -90 degrees in the merged

cell, and then change the width of column I to

9.25

.

8.

In cell B11, insert a formula that uses the IFERROR function to divide the total

sales for Q1 in cell B8 by the total sales in cell G8 and display "Incorrect" in

case of an error. Use an absolute reference to cell G8 in the formula, and then

fill the range C11:E11 with the formula in cell B11.

9.

In cell B15, enter a formula that uses the IF function and tests whether the

total sales for Q1 (cell B8) is greater than or equal to 1000000. If the condition

is true, multiply the total sales for Q1 by 0.18 to calculate a commission of

18%. If the condition is false, multiply the total sales for Q1 by 0.10 to

calculate a commission of 10%.

Shelly Cashman Excel 2019 | Module 3: End of Module Project 2

10.

Fill the range C15:E15 with the formula in cell B15 to calculate the

commissions for the other three quarters.

11.

In cell F15, insert a Column sparkline based on the data in the range B15:E15. Fill cell

F16 without formatting based on the contents of cell F15. Display the High Point and the

Low Point in the sparklines.

12.

Create a clustered column chart based on the range A3:E7. Resize and reposition the

chart so its upper-left corner is in cell A18 and its lower-right corner is in cell G29.

Change the chart layout to Quick Layout 9. Enter

Projected Sales

as the chart title.

Enter

Sales Amount

as the primary vertical axis title. Remove the primary horizontal

axis title.

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.

Shelly Cashman Excel 2019 | Module 3: End of Module Project 2

Final Figure 1: Sales Projections Worksheet