Shelly Cashman Excel 365 | Module 1: SAM Project B New Age Pharmaceutical

GETTING STARTED

1. Save the file SC_EX365_1B_FirstLastName_1.xlsx as SC_EX365_1B_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_1B_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. Sasha Egan manages the West Coast office for New Age Pharmaceutical, a pharmaceutical sales company. She has created a worksheet summarizing the revenue and expenses for the last six months of the year, and asks for your help in determining what the West Coast office needs to do in the rest of the year to meet its profit goals. Go to the Budget worksheet. Cut the worksheet subtitle from cell B1 and paste it in cell A2 to display the subtitle in its expected location.

2. In cell H2, add the text consultants so that the complete text appears as "Number of consultants" and more clearly identifies the value in cell J2.

3. Enter Aug in cell C11, Sept in cell D11, Oct in cell E11, Nov in cell F11, and Dec in cell G11.

4. In cell J12, enter a formula that subtracts the value in cell I12 from the value in cell H12 to determine how much revenue the West Coast office needs to earn from January to June to reach its goal for the following year.

5. Sasha wants to calculate the gross margin from July to December and for the year to date. Provide this information as follows:

a. In cell B14, enter a formula that divides the value in cell B13 (the gross profit for July) by the value in cell B12 (the revenue in July).

b. Use the Fill Handle to fill the range C14:H14 with the formula in cell B14 to find the gross margin for August to December and for the year to date.

6. Sasha needs to sum the sales for each West Coast state for the year to date. Provide this information as follows:

a. In cell H4, enter a formula that uses the SUM function to total the range B4:G4 to calculate the year-to-date sales for California.

b. Use the Fill Handle to fill the range H5:H7 with the formula in cell H4 to find the year-to-date sales for the other states.

7. In cell B17, enter 449,000 as the general expenses for July.

8. Sasha wants to determine how much revenue each state needs to generate from January to June to reach its target for the year. Enter this information as follows:

a. In cell J4, enter a formula that subtracts the value in cell I4 from the value in cell H4 to find the amount of California sales Sasha needs to meet the goal.

b. Copy the formula in cell J4, and then paste it in the range J5:J9, pasting the formulas only, to find the differences for the other states, the total, and the revenue per consultant.

9. Sasha is interested in how much revenue the nine consultants each generate on average per month and for the year to date. Find this information as follows:

a. Edit cell B9 to include a formula that divides the value in cell B8 (the total sales for California in July) by the value in cell J2 (the number of consultants). Don't change the reference to cell J2 that is already there.

b. Fill the range C9:H9 with the formula in cell B9 to determine the average revenue per consultant from August to December and for the year to date.

10. Sasha 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 as follows:

a. In cell B19, enter a formula that divides the value in cell B18 (the operating profit or loss in July) by the value in cell B12 (the revenue in July).

b. Use the Fill Handle to fill the range C19:H19 with the formula in cell B19 to find the operating margin for August to December and for the year to date.

11. Sasha wants to visualize the total year to date sales separated into states. Insert a chart to provide this visualization as follows:

a. Create a Pie Chart (choose the first option in the pie chart menu) based on the nonadjacent ranges A3:A7 and H3:H7.

b. Enter Total Year to Date Sales by State as the chart title.

c. Reposition and resize the chart so its upper-left corner is in cell K3 and its bottom-right corner is in cell O15.

d. Apply Style 4 to the chart.

12. Hide the gridlines for the Budget 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 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.