GETTING STARTED
 Open the file SC_EX19_1a_FirstLastName_1.xlsx, available for download from the SAM website.
 Save the file as SC_EX19_1a_FirstLastName_2.xlsx by changing the “1” to a “2”.
o 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_1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. Josh Bartel is the senior director of the Pennsylvania field office for
   New Era Medical, a global company that manufactures medical
   equipment. He has created a worksheet summarizing the revenue
   and expenses for the first three quarters of the year. He asks for your
   help in determining what the Pennsylvania office needs to do to meet
   its targeted profit goals in the fourth quarter.
   Go to the Budget Summary worksheet. Cut the worksheet title from
   cell I1 and paste it in cell A1 to display the title in its expected
   location. B
2. In cell E2, add the text Number of so that the complete text appears
   as “Number of salespeople:” and more clearly identifies the value in
   cell G2.
3. Enter Quarter 2 in cell C4 and Quarter 3 in cell D4.
4. In cell G5, enter a formula without a function that subtracts the target
   revenue (cell F5) from the year to date revenue (cell E5) to determine
   how much revenue the field office needs to earn in Quarter 4 to reach
   its target for the year.
5. Josh wants to calculate the gross margin for Quarters 1–3 and the
   year to date. Provide this information as follows:
   a. In cell B7, enter a formula without a function that divides the
   gross profit for Quarter 1 (cell B6) by the revenue in Quarter 1
   (cell B5).
   b. Fill the range C7:E7 with the formula in cell B7 to find the gross
   margin for Quarters 2 and 3 and for the year to date.
6. Josh needs to sum the sales for each Pennsylvania region for the year
   to date. Provide this information as follows:
   a. In cell E10, enter a formula that uses the SUM function to total
   the regional sales data for the Northeast region (the range
   B10:D10) to calculate the year-to-date sales.
   b. Use the Fill Handle to fill the range E11:E13 with the formula in
   cell E10 to find the year-to-date sales for the other three
   regions.
7. In cell F10, enter the value 925000 to provide the targeted annual
   sales amount for the Northeast region.
8. Josh wants to determine how much revenue each region needs to
   generate in Quarter 4 to reach its target for the year. Enter this
   information as follows:
   a. In cell G10, enter a formula without a function that subtracts the
   target sales for the Northeast region (cell F10) from its year to
   date regional sales (cell E10) to find the amount of sales the
   Northeast region needs to generate to meet its target.
   b. Copy the formula in cell G10, and then paste it in the range
   G11:G14, pasting the formulas only, to find the variances for the
   other regions and for the total.
9. Josh is interested in how much revenue each of the 12 salespeople
   generates on average in each quarter and for the year to date. Find
   this information as follows:
   a. Edit cell B15 to include a formula without a function that divides
   the total regional sales in Quarter 1 (cell B14) by the number of
   salespeople (cell G2). Don’t change the reference to cell G2 that
   is already there.
   b. Fill the range C15:E15 with the formula in cell B15 to determine
   the average revenue per salesperson in Quarters 2 and 3 and
   for the year to date.
10. Josh also needs to calculate the operating margin, which is the ratio of
    operating profit or loss to revenue and indicates how much the office
    makes after paying for expenses. Calculate the operating margin as
    follows:
    a. In cell B20, enter a formula without a function that divides the
    operating profit or loss in Quarter 1 (cell B19) by the revenue
    from Quarter 1 (cell B5).
    b. Use the Fill Handle to fill the range C20:E20 with the formula in
    cell B20 to find the operating margin for Quarters 2 and 3 and
    for the year to date.
11. Josh wants to include a visualization of the total year to date sales
    separated by region. Insert a chart to provide this visualization as
    follows:
    a. Create a 2-D Pie Chart based on the nonadjacent ranges
    A9:A13 and E9:E13.
    b. Enter Total Year to Date Sales by Region as the chart title.
    c. Resize and reposition the chart so its upper-left corner is in cell
    I4 and its bottom-right corner is in cell L18.
    d. Apply Style 3 to the chart.
12. Hide the gridlines for the Budget Summary worksheet to make it
    easier to read.