80.2K

Verified Solution

Link Copied!

a) 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.

1. 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:
   1. 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.
   2. 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.
2. 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:
   1. 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.
   2. 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.
3. 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:
   1. 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).
   2. 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

PLEASE USE EXCEL FORMULAS TO SOLVE

B C E F G 12 Variance (131,500) 0.0% Variance A New Era Medical 2 Budget Summary - Pennsylvania Center Number of salespeople: 3 4 Profit and Loss Summary Quarter 1 Quarter 2 Quarter 3 Year to Date Target 5 Revenue 1,003,500 1,007,750 1,100,150 3,111,400 4,200,000 6 Gross profit 125,500 130,500 132,500 388,500 520,000 7 Gross margin 12.5% 12.9% 12.0% 12.5% 12.5% 8 9 Regional Sales Quarter 1 Quarter 2 Quarter 3 Year to Date Target 10 Northeast 215,225 251,325 230,137 696,687 11 Southeast 376,517 338,575 384,550 1,099,642 1,200,000 12 Central 95,605 100,345 115,228 311,178 325,000 13 West 316,153 317,505 370,235 1,003,893 1,050,000 14 Total 1,003,500 1,007,750 1,100,150 3,111,400 2,575,000 15 Revenue per salesperson 12 250,000 16 17 Expenses Quarter 1 Quarter 2 Quarter 3 Year to Date Target 18 General expenses 82,090 85,587 85,600 253,277 330,000 19 Operating profit/loss 20,415 25,689 28,880 74,984 110,000 20 Operating margin 2.5% Variance (76,723) (35,016) -2.5%

Answer & Explanation Solved by verified expert

See Answer