GETTING STARTED
 Save the file SC_EX365_2021_3a_FirstLastName_1.xlsx as
SC_EX365_2021_3a_FirstLastName_2.xlsx
 Edit the file name by changing “1” to “2”.
 If you do not see the .xlsx file extension, do not type it. The file extension will
be added for you automatically.
 With the file SC_EX365_2021_3a_FirstLastName_2.xlsx open, ensure that
your first and last name is displayed in cell B6 of the Documentation worksheet.
 If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS

1. Lindsey Sawyer is the director of recruiting and business development for
   Raybridge Recruiting in Washington, D.C. She has been analyzing sales and
   commissions for recruiters 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 the contents of the merged cell A1 to improve the appearance of
   the worksheet title.
4. In cell B3, insert a formula that uses the NOW function to display today’s date.
5. Fill the range C6:G10 with the formatting from the range B6:B10 to use a
   consistent number format for the sales data.
6. Lindsey wants to show the sales trend for each position and month from January
   to June. Provide this information as follows:
   a. In cell H6, insert a Line sparkline based on the data in the range B6:G6.
   b. Use the Fill Handle to fill the range H7:H10 without formatting based on
   the contents of cell H6.
   c. Change the color of the sparklines in the range H6:H10 to Dark Purple,
   Text 2 (4th column, 1st row of the Theme Colors palette) 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 cell I6 and paste it in the range I7:I10, pasting only the
   formula and number formatting, to include sales totals.

Cashman Excel 365/2021 | Module 3: SAM Project 1a 8. Lindsey wants to determine how the sales of each month contributed to the
total sales. Calculate this information for her as follows:
a. In cell B13, insert a formula without using a function that divides the total
sales for January (cell B10) by the total sales to date (cell I10).
b. Use an absolute reference to cell I10 in the formula.
c. Use the Fill Handle to fill the range C13:G13 with the formula in cell B13.

9. Lindsey also needs to calculate the dividends earned each month. If the
   company earns $200,000 or more in a month, the dividend is 35% of the sales.
   If the company earns less than $200,000 in a month, the dividend is 27% of the
   sales. Calculate the dividends as follows:
   a. In cell B17, enter a formula that uses the IF function and tests whether
   the total sales for January (cell B10) is greater than or equal to 200000.
   b. If the condition is true, multiply the total sales for January (cell B10) by
   0.35 to calculate a dividend of 35%.
   c. If the condition is false, multiply the total sales for January (cell B10) by
   0.27 to calculate a dividend of 27%.
   d. Use the Fill Handle to fill the range C17:G17 with the formula in cell B17 to
   calculate the dividends for February through June.
10. Change the sparklines in the range H17:H18 as follows to use a more
    meaningful format:
    a. Change the Column sparklines to Line sparklines.
    b. Apply the sparkline style Dark Gray, Sparkline Style Dark #3 (3rd
    column, 5th row of the Sparkline Styles palette) to make the sparklines
    easier to see.
11. Lindsey would like to increase the average number of placements per month.
    Because executive placements are the most profitable, Lindsey wants to know
    how many executive placements she needs to reach the goal of 30 placements
    per month.
    Use Goal Seek to set the average number of placements for all positions (cell
    I27) to the value of 30 by changing the average number of executive
    placements (cell I23).
12. Format the text in cell J22 to clarify what it refers to as follows:
    a. Merge and center the range J22:J27.
    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.
13. Delete row 35, which contains information Lindsey does not need anymore.

Cashman Excel 365/2021 | Module 3: SAM Project 1a 14. Lindsey also wants to compare the estimated sales of July and December. Insert
a chart that provides this information as follows:
a. Create a Clustered Column chart based on the nonadjacent ranges
A22:B26 and G22:G26.
b. Enter Estimated Sales: July and December as the chart title.
c. Resize and reposition the chart so its upper-left corner is in cell A35 and its
lower-right corner is in cell E49.
d. Change the chart layout to Layout 2 to display the data values above the
bars instead of including a vertical axis.
(Note: The value in cell B3 of the sales and dividends worksheet may
differ from that shown below.)