Shelly Cashman Excel 365 | Module 3: SAM Critical Thinking Project C Freedom Financial

GETTING STARTED

1. Save the file SC_EX365_CT3C_FirstLastName_1.xlsx as SC_EX365_CT3C_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_CT3C_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. Alexi Fisher is the director of recruiting and business development for Freedom Financial in San Diego, California. 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. Middle-align and center align the contents of the merged cell containing the text "Freedom Financial" to improve the appearance of the worksheet title.

4. In the appropriate cell, insert a formula that uses a function to display today's date and time for the date the workbook was last updated.

5. Fill the Monthly Sales column for February through June with the formatting from the Monthly Sales column for January to use a consistent number format for the sales data.

6. Alexi wants to show the sales trend for each position and month from January to June. Provide this information as follows:

a. In the Monthly Sales table, in the Position Type: Trend cell, insert a Line sparkline based on the data in the range January through June.

b. Use the Fill Handle to fill the remaining Trend cells without formatting based on the contents of the Position Type: Trend cell.

c. Change the color of the sparklines in the Trend column to Olive Green, Text 2 to emphasize them.

d. Show a high point and a low point in the sparklines to make those values easy to identify.

7. Copy the formula in the Position Type: Total cell and paste it in the remaining Total cells, pasting only the formula and number formatting, to include sales totals.

8. Alexi wants to determine how each month's sales contributed to the total sales. Calculate this information for her as follows:

a. In the Share cell for January, insert a formula without using a function that divides the total sales for January by the total sales to date.

b. Use an absolute reference to the Total sales cell in the formula.

c. Use the Fill Handle to fill the range in the Share cells for February through June with the formula in the Share cell for January.

9. Alexi would like to increase the average number of product sales per month, and specifically how many representative financial products she needs to reach the goal of 20 products per month. Use Goal Seek to set the average number of product sales for all positions (Total:Avg. Products cell) to the value of 20 by changing the average number of representative products.

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

a. Merge and center the range J16:J21.

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 7.00.

11. Alexi also needs to calculate the dividends earned each month. If the company earns $200,000 or more in a month, the dividend is 30% of the sales. If the company earns less than $200,000 in a month, the dividend is 25% of the sales. Calculate the dividends as follows:

a. In the Dividends Paid table, in the Amount cell for January, enter a formula that uses the appropriate function to test whether the sales total for January is greater than or equal to 200000.

b. If the condition is true, multiply the total sales for January by 0.30 to calculate a dividend of 30%.

c. If the condition is false, multiply the total sales for January by 0.25 to calculate a dividend of 25%.

d. Use the Fill Handle to fill the range in the Amount cells for February through June with the formula in the Amount cell for January to calculate the dividends for February through June.

12. Change the sparklines in the Amount:Trend and Net Amount:Trend cells as follows to use a more meaningful format:

a. Change the Column sparklines to Line sparklines.

b. Apply the Dark Blue, Sparkline Style Dark #6 sparkline style (6th column, 5th row of the Sparkline Styles palette) [Mac Hint: 2nd column, 5th row of the Sparkline Styles palette] to make the sparklines easier to see.

13. Delete row 35, which contains information Alexi 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.

The value in cell I3 of the Sales and Dividends worksheet has been intentionally blurred as it will never be constant.

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.