GETTING STARTED
 Save the file SC_EX365_2021_CT1c_FirstLastName_1.xlsx as
SC_EX365_2021_CT1c_FirstLastName_2.xlsx
o Edit the file name by changing “1” to “2”.
o 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_CT1c_FirstLastName_2.xlsx open, ensure that
your first and last name is displayed in cell B6 of the Documentation worksheet.
o If cell B6 does not display your name, delete the file and download a new
copy.
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 must do to meet its targeted profit goals in the fourth
   quarter.
   Go to the Budget Summary worksheet. Cut the “New Era Medical” worksheet
   title and paste it in cell A1 to display the title in its expected location.
2. In cell E2, edit the text so that the complete text appears as Number of
   salespeople:
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
   from the year-to-date revenue to determine how much revenue the field office
   must 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 by the revenue in Quarter 1.
   b. Fill the Gross margin cell range 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 proper function to total the three
   quarters of regional sales data for the Northeast region to calculate the
   year-to-date sales.
   b. Use the Fill Handle to fill the remaining three regions 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 must 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 from its year-to-date regional sales to find
   the amount of sales the Northeast region must generate to meet its
   target.
   b. Copy the formula in cell G10, and then paste it in the remaining empty
   cells in the Variance column (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 by the number of salespeople. Don’t change
   the reference to cell G2 that is already there.
   b. Fill the remaining empty cells in the Revenue per salesperson row in the
   table 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 by the revenue from Quarter 1.
    b. Use the Fill Handle to fill the remaining empty cells in the Operating
    margin row in the table 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
    lower-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.