Homework answers / question archive / Shelly Cashman Excel 2019 | Module 3: SAM Critical Thinking Project 1c Raybridge Recruiting CREATE A SALES REPORT GETTING STARTED Open the file SC_EX19_CT3c_FirstLastName_1

Shelly Cashman Excel 2019 | Module 3: SAM Critical Thinking Project 1c Raybridge Recruiting CREATE A SALES REPORT GETTING STARTED Open the file SC_EX19_CT3c_FirstLastName_1

MS Excel

Share With

Shelly Cashman Excel 2019 | Module 3: SAM Critical Thinking Project 1c

Raybridge Recruiting

CREATE A SALES REPORT

• GETTING STARTED

• Open the file SC_EX19_CT3c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_CT3c_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file SC_EX19_CT3c_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• 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 dividends 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 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 containing the text "Raybridge Recruiting" to improve the appearance of the worksheet title.
4. Insert a formula in the appropriate cell that uses the NOW function to display today's date 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. Lindsey wants to show the sales trend for each position and month from January to June. Provide this information as follows:
   1. In the Monthly Sales table, in the Executives:Trend cell, insert a Line sparkline based on the data in the range January through June.
   2. Use the Fill Handle to fill the remaining Trend cells without formatting based on the contents of the Executives:Trend cell.
   3. Change the color of the sparklines in the Trend column to Dark Purple, Text 2 to emphasize them.
   4. Show a high point and a low point in the sparklines to make those values easy to identify.
7. Copy the formula in the Executives:Total cell and paste it in the remaining Total cells, pasting only the formula and number formatting, to include sales totals.
8. Lindsey wants to determine how each month's sales contributed to the total sales. Calculate this information for her as follows:
   1. 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.
   2. Use an absolute reference to the Total sales cell in the formula.
   3. 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. 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:
   1. 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.
   2. If the condition is true, multiply the total sales for January by 0.35 to calculate a dividend of 35%.
   3. If the condition is false, multiply the total sales for January by 0.27 to calculate a dividend of 27%.
   4. 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.
10. Change the sparklines in the Amount:Trend and Net Amount:Trend cells as follows to use a more meaningful format:
    1. Change the Column sparklines to Line sparklines.
    2. Apply the Dark Gray, Sparkline Style Dark #3 sparkline style 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 in order to reach the goal of 30 placements per month.
    Use Goal Seek to set the average number of placements for all positions (Total:Avg. Placements cell) to the value of 30 by changing the average number of executive placements (Executives:Avg. Placements cell).
12. Format the "Estimates only" cell text to clarify what it refers to as follows:
    1. Merge and center the range J22:J27.
    2. Rotate the text down to -90 degrees in the merged cell so that the text reads from top to bottom.
    3. Change the width of column J to 6.00.
13. Delete row 35, which contains information Lindsey does not need anymore.
14. Lindsey also wants to compare the estimated sales for July and December. Insert a chart that provides this information as follows:
    1. Create a Clustered Column chart based on the nonadjacent ranges A22:B26 and G22:G26.
    2. Enter Estimated Sales: July and December as the chart title.
    3. Resize and reposition the chart so its upper-left corner is in cell A35 and its lower-right corner is in cell E49.
    4. Change the chart layout to Layout 2 to display the data values above the bars instead of including a vertical axis.

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 SAM website to submit your completed project.