Shelly Cashman Excel 365 | Module 3: SAM Project B Book Cloud

GETTING STARTED

1. Save the file SC_EX365_3B_FirstLastName_1.xlsx as SC_EX365_3B_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_3B_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. Maria Soltez is the director of sales for Book Cloud in Salem, Oregon. She has been analyzing sales and dividends for finance professionals in an Excel workbook and has asked you to help her complete the analysis. Go to the Sales worksheet. Rename the Sales worksheet as Sales and Dividends, which is a more accurate name.

2. Unfreeze column A so it is not displayed when you scroll horizontally.

3. Center-align the contents of the merged cell A1 to improve the appearance of the worksheet title.

4. In cell I3, insert a formula that uses the NOW function to display today's date.

5. Fill the range C13:G16 with the formatting from the range B13:B16 to use a consistent number format for the sales data.

6. Maria wants you to show the sales trend for each subscription type and month from January to June. Provide this information as follows:

a. In cell H13, insert a Column sparkline based on the data in the range B13:G13.

b. Use the Fill Handle to fill the range H14:H16 without formatting based on the contents of cell H13.

c. Change the color of the sparklines in the range H13:H16 to Dark Purple, Accent 2 (6th column, 1st row of the Theme Colors palette) to emphasize them.

d. Show the First Point and Last Point in the sparklines to make those values easy to identify.

7. Copy the formula in cell I13 and paste it in the range I14:I16, pasting only the formula and number formatting, to include sales totals.

8. Maria wants you to determine how the sales of each month contributed to the total sales. Calculate this information for her as follows:

a. In cell B19, insert a formula without using a function that divides the total sales for January (cell B16) by the total sales to date (cell I16).

b. Use an absolute reference to cell I16 in the formula.

c. Use the Fill Handle to fill the range C19:G19 with the formula in cell B19.

9. Maria would like to increase the average number of subscription sales per month. Because yearly subscriptions are the most profitable, Maria wants to know how many yearly subscriptions she needs to reach the goal of 2,000 subscriptions per month. Use Goal Seek to set the average number of product sales for all subscriptions (cell I9) to the value of 2000 by changing the average number of yearly subscriptions (cell I6).

10. Format the text in cell J5 to clarify what it refers to as follows:

a. Merge and center the range J5:J9.

b. Rotate the text down to -90 degrees in the merged cell so that the text reads from top to bottom.

c. Change the width of column J to 6.00.

11. Maria also wants you to calculate the dividends earned each month. If the company earns $130,000 or more in a month, the dividend is 25% of the sales. If the company earns less than $130,000 in a month, the dividend is 20% of the sales. Calculate the dividends as follows:

a. In cell B23, enter a formula that uses the IF function and tests whether the total sales for January (cell B16) is greater than or equal to 130000.

b. If the condition is true, multiply the total sales for January (cell B16) by 0.25 to calculate a dividend of 25%.

c. If the condition is false, multiply the total sales for January (cell B16) by 0.20 to calculate a dividend of 20%.

d. Use the Fill Handle to fill the range C23:G23 with the formula in cell B23 to calculate the dividends for February through June.

12. Change the sparklines in the range H23:H24 as follows to use a more meaningful format:

a. Change the Column sparklines to Line sparklines.

b. Apply the sparkline style Rose, Sparkline Style Accent 6, Lighter 40% (6th column, 4th row of the Sparkline Styles palette) [Mac Hint - 3rd column, 4th row of the Sparkline Styles palette] to make the sparklines easier to see.

13. Delete row 33, which contains information Maria does not need anymore.

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: Sales and Dividends 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.