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 Saves worksheet. Rename the Sares worksheet as sales and Dividends, which is a more accurate name.

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

Middle$-$align the contents of the merged cell A$1$ to improve the appearance of the worksheet title.

In cell B$3,$ insert a formula that uses the NoW function to display today's date.

Fill the range C$6$:G$10$ with the formatting from the range B$6$:B$10$ to use a consiatent number format for the sales data.

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 B$6$:G$6.$

b$.$ Use the Fill Handle to fill the range $H7$:$H10$ without formatting based on the contents of cell H$6.$

c$.$ Change the color of the sparklines in the range H$6$:H$1$O to Dark Purple, Text $2(4$th column, $1$ st 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.

Copy the formula in cell $16$ and paste it in the range $17$:$110,$ pasting only the formula and number formatting, to include sales totals.

Shelly Cashman Excel $2019$ i Module S: SAM Project $1$a

$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 B$13,$ insert a formula without using a function that divides the total sales for January $($cell B$10)$ by the total sales to date $($cell I$10).$

b$.$ Use an absolute reference to cell I$10$ in the formula.

c$.$ Use the Fill Handle to fill the range $G13$:$G13$ with the formula in cell $B13.$

$9.$ Lindsey also needs to calculate the dividehds 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 lollows:

a$.$ In cell B$17,$ enter a formula that uses the IF function and tests whether the total sales for January $($cell B$10)$ is greater than or equal to $200000.$

b$.$ If the condition is true, multiply the total sales for January $($cell B$10)$ by $0\mathrm{.}35$ to calculate a dividend of $35\%.$

c$.$ If the condition is false, multiply the total sales for January $($cell B$10)$ by $0\mathrm{.}27$ to calculate a dividend of $27\%.$

d$.$ Use the Fill Handle to fill the range $C17$:$G17$ with the formula in cell $817$ to calculate the dividends for February through June.

$10.$ Change the sparklines in the range $H17$:$H1B$ as follows to use a more meaninglul Tormat:

a$.$ Change the Column sparklines to Line sparklines.

b$.$ Apply the sparkline style Dark Gray, Sparkline Style Dark a$3(3$nd oolumn, $5$ th 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 $127)$ to the value of $30$ by changing the average number of executive placements $($ceill $123).$

$12.$ Format the lext in cell $322$ to clarify what it reiers to as foliows:

a$.$ Merge and center the range $122$:$327.$

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

c$.$ Change the width or column I to $6\mathrm{.}00.$

$13.$ Delete row $35,$ which contains informatian Lindsey daes not need apymore