Shelly Cashman Excel 365 | Module 1: SAM Critical Thinking Project C EnviroEnergy

GETTING STARTED

1. Save the file SC_EX365_CT1C_FirstLastName_1.xlsx as SC_EX365_CT1C_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_CT1C_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. Sundar Toth is the senior director of the Connecticut field office for EnviroEnergy, an industrial company that manufactures energy producing equipment. He has created a worksheet summarizing the revenue and expenses for the first three quarters of the year. He asks for your help in determining what the Connecticut office needs to do to meet its targeted profit goals in the fourth quarter. Go to the Budget Summary worksheet. Cut the worksheet title from cell I1 and paste it in cell A1 to display the title in its expected location.

2. In cell E1, add the text Number of so that the complete text appears as "Number of salespeople:" and more clearly identifies the number above the tables.

3. Enter Quarter 1 and Quarter 3 in the header section of the Profit and Loss Summary table.

4. The variance is the amount of revenue the field office needs to earn in Quarter 4 to reach its target for the year. To find the revenue variance in the Profit and Loss Summary table, enter a formula without a function that subtracts the target revenue from the year to date revenue.

5. Sundar wants to calculate the gross margin for Quarters 1–3 and the year to date. Provide this information in the Profit and Loss Summary table.

a. To find the gross margin for Quarter 1, enter a formula without a function that divides the gross profit for Quarter 1 by the revenue in Quarter 1.

b. Fill the formula across the row to find the gross margin for Quarters 2 and 3, and the year to date.

6. Sundar needs to add the sales for each energy product type to calculate the year-to-date sales. Provide this information in the Energy Sales table.

a. To find the Year to Date sales for Gas Generators, enter a formula that uses the SUM function to total the three quarters of gas generator sales.

b. Fill the formula down the column to find the year-to-date sales for the other three products.

7. To provide the targeted annual sales amount for gas generators, enter the value 6000000 in the appropriate cell.

8. Sundar wants to determine how much revenue each energy product needs to generate in Quarter 4 to reach its target for the year. Enter this information as directed.

a. To find the variance for Gas Generators, enter a formula without a function that subtracts the target sales for gas generators from its year-to-date sales.

b. Fill the formula down the column to find the variances for the other energy products and for the total.

9. Sundar is interested in how much revenue each of the 22 salespeople generates on average in each quarter and for the year to date. Find this information.

a. Edit the Quarter 1 Revenue per Salesperson to include a formula without a function that divides the total energy sales in Quarter 1 by the number of salespeople above the tables. Keep the reference to cell G1 that is already there.

b. Fill the formula across the row to show the average revenue per salesperson in Quarters 2 and 3, and for the year to date.

10. Sundar also needs to calculate the operating margin, which is the ratio of operating profit or loss to revenue and indicates how much the office makes after paying for expenses. Calculate the operating margin in the Expenses table.

a. To find the Quarter 1 Operating margin, enter a formula without a function that divides the operating profit or loss in Quarter 1 by the Profit and Loss Summary revenue from Quarter 1.

b. Filll the formula across the row to find the operating margin for Quarters 2 and 3, and for the year to date.

11. Sundar wants to include a visualization of the total year-to-date energy sales separated by type. Insert a chart to provide this visualization.

a. Create a Doughnut Chart to display the year to date sales values for the four energy products.

b. Enter Total Year-to-Date Sales by Energy Type as the chart title.

c. Resize and reposition the chart so its upper-left corner is in cell I4 and its bottom-right corner is in cell L18.

d. Apply Style 4 to the chart.

12. Hide the gridlines for the Budget Summary worksheet to make it easier to read.

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.

Final Figure 1: Budget Summary 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.