Shelly Cashman Excel 365 | Module 3: End of Module Project 2 Petrol One

GETTING STARTED

1. Save the file SC_EX365_EOM3-2_FirstLastName_1.xlsx as SC_EX365_EOM3-2_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_EX365_EOM3-2_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

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

PROJECT STEPS

1. Sandra Glindmyer is a financial analyst for Petrol One, a company in Minneapolis, Minnesota, that provides businesses with petroleum handling equipment. Sandra 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 and center the contents of the merged cell A1.

2. Bold 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 A2 to the merged cell A14. Fill the range C4:E9 with only the formatting from the range B4:B9.

4. In cell F4, insert a Line sparkline based on the data in the range B4:E4. Fill the range F5:F9 without formatting based on the contents of cell F4. Change the color of the sparklines to Dark Red, Accent 1 and then add markers to the sparklines. Change the color of the markers to Blue-Gray, Accent 5.

5. Copy the formula in cell G4 and paste it in the range G5:G9, pasting only the formula and number formatting.

6. Use Goal Seek to set the average number of projects (cell H9) to the value of 78 by changing the average number of manufacturing component projects (cell H6).

7. Merge and center the range I3:I9. Rotate the text down in the merged cell, and then change the width of column I to 10.25.

8. In cell B12, insert a formula that uses the IFERROR function to divide the total sales for Q1 in cell B9 by the total sales in cell G9 and display "Incorrect" in case of an error. Use an absolute reference to cell G9 in the formula, and then fill the range C12:E12 with the formula in cell B12.

9. In cell B16, enter a formula that uses the IF function and tests whether the total sales for Q1 (cell B9) is greater than or equal to 100000. If the condition is true, multiply the total sales for Q1 by 0.20 to calculate a commission of 20%. If the condition is false, multiply the total sales for Q1 by 0.12 to calculate a commission of 12%.

10. Fill the range C16:E16 with the formula in cell B16 to calculate the commissions for the other three quarters.

11. In cell F16, insert a Column sparkline based on the data in the range B16:E16. Fill cell F17 without formatting based on the contents of cell F16. Display the High Point and the Low Point in the sparklines.

12. Create a clustered column chart based on the range A3:E8. Switch the Row/Column Data to place the quarters as the horizontal axis labels and the product categories as the legend entries. Resize and reposition the chart so its upper-left corner is in cell A19 and its lower-right corner is in cell G30. Change the chart layout to Layout 5 in the Quick Layout dropdown menu. Enter Projected Sales as the chart title. Enter Sales Amount as the primary vertical 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 website to submit your completed project.

The value in cell I1 of the Sales Projections worksheet has been intentionally blurred as it will never be constant.

Final Figure 1: Sales Projections Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.