Develop an Excel model for Robert's Chiropractic Clinic - use the scenario provided below. Robert Berns...

 

Transcribed Image Text:

Develop an Excel model for Robert's Chiropractic Clinic - use the scenario provided below. Robert Berns runs a Chiropractic Clinic in Belle Jardin in St. Louis. His annual fixed operating costs are $200,000, while the variable cost per each patient treated is estimated at $58. The price billed for each visit is $122. Currently he has 3,000 visits per year. Is he making a profit? How many patients would he need to see each year to break even? You will need to work on 4 sheets of the attached template. The sheet named Break even point analysis has built-in comments that should help you in creating 3 data tables (use DATA tab, then What-if), developing formulas for total costs and revenues. On this sheet create also an annotated XY (scatter) chart to illustrate the break even point. The chart should display your name in the title. The other 3 sheets for goal seek have necessary explanations as well. O P A B D E F G H L M R 1 Robert's Clinic 2 Annual Fixed Operating Costs 3 Variable Cost per Patient 4 Price Blled per Visit 5 # patients seen annually Data Table 1 200,000 Recalculation of profit/loss with annual fixed operating costs ranging from 198,000 to 212,000 by $2,000 58 MEzvan: point to the cal with formula for ed costs vary but variable costs and price billed per visit stay constant proftloss 122 198,000 3,000 200,000 batients = 3000 6 202.000 7 Total annual cost 204.000 8 Total annual revenues 9 Profit/loss 10 Break even point 206,000 MIRA: Enter appropriate formulas in lcells D7 through D10 208,000 210,000 11 212,000 fixed cost MIRA: Enter formulas for Total cost and 12 revenues in cells B14, C14. Copy the formulas. # patients seen 13 annually Total cost 3,075 revenue 14 Data Table 2 Break even point recalculated when variable costs vary from $52 to $60 15 3,080 16 3,085 52 MEzvan: Ipoint to the cell with formula for BE 17 3,090 53 18 3,095 54 19 3,100 3,105 3,110 56 20 56 21 57 22 3,115 3,120 58 23 59 24 3,125 60 25 3,130 var cost 26 3,135 3,140 3,145 Data Table 3 Break even point recalculated when price billed per visit and variable operating costs vary variable cost 27 28 29 3,150 3,155 3,160 3,165 54 55 56 57 58 59 60 61 62 2$ 120.00 30 31 122.00 32 24 124.00 33 3,170 126.00 34 3,175 128.00 24 $4 24 35 3,180 130.00 36 3,185 3,190 132.00 37 134.00 3,196 3,200 38 price charged per visit 39 40 MIRA: Create an annotated XY (scatter) chart to show the break even point display your name in the title 41 42 43 44 45 A В C E F G H J 1 Robert's Clinic Annual Fixed Operating Costs $200,000.00 3 Variable Cost per Patient $ 58.00 Price Billed per Visit $ 122.00 # patients seen annually 3,000 6. 7 Total annual cost $374,000.00 Total annual revenues $366,000.00 Profit/loss -8,000.00 10 11 12 Use goal seek to find the break even point 13 14 15 16 17 18 19 20 21 A В C F G H J K M N On this sheet use goal seek to find out how much the variable cost per patient would have to change to allow the clinic to break even at 3100 patients annually. 2 Assume that the fixed cost stays at $200,000 and the price billed per visit is $122. 1 3 5 6. 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 P. A C D E F G J K L M P 1 On this sheet use goal seek to find out how much the price billed per visit would have to change to allow the clinic to break even at 3000 patients annually. 2 Assume that the variable cost per patient stays at $58 and the fixed cost stays at 200,000. 4 6. 8 9. 10 11 12 13 14 15 16 17 B. Develop an Excel model for Robert's Chiropractic Clinic - use the scenario provided below. Robert Berns runs a Chiropractic Clinic in Belle Jardin in St. Louis. His annual fixed operating costs are $200,000, while the variable cost per each patient treated is estimated at $58. The price billed for each visit is $122. Currently he has 3,000 visits per year. Is he making a profit? How many patients would he need to see each year to break even? You will need to work on 4 sheets of the attached template. The sheet named Break even point analysis has built-in comments that should help you in creating 3 data tables (use DATA tab, then What-if), developing formulas for total costs and revenues. On this sheet create also an annotated XY (scatter) chart to illustrate the break even point. The chart should display your name in the title. The other 3 sheets for goal seek have necessary explanations as well. O P A B D E F G H L M R 1 Robert's Clinic 2 Annual Fixed Operating Costs 3 Variable Cost per Patient 4 Price Blled per Visit 5 # patients seen annually Data Table 1 200,000 Recalculation of profit/loss with annual fixed operating costs ranging from 198,000 to 212,000 by $2,000 58 MEzvan: point to the cal with formula for ed costs vary but variable costs and price billed per visit stay constant proftloss 122 198,000 3,000 200,000 batients = 3000 6 202.000 7 Total annual cost 204.000 8 Total annual revenues 9 Profit/loss 10 Break even point 206,000 MIRA: Enter appropriate formulas in lcells D7 through D10 208,000 210,000 11 212,000 fixed cost MIRA: Enter formulas for Total cost and 12 revenues in cells B14, C14. Copy the formulas. # patients seen 13 annually Total cost 3,075 revenue 14 Data Table 2 Break even point recalculated when variable costs vary from $52 to $60 15 3,080 16 3,085 52 MEzvan: Ipoint to the cell with formula for BE 17 3,090 53 18 3,095 54 19 3,100 3,105 3,110 56 20 56 21 57 22 3,115 3,120 58 23 59 24 3,125 60 25 3,130 var cost 26 3,135 3,140 3,145 Data Table 3 Break even point recalculated when price billed per visit and variable operating costs vary variable cost 27 28 29 3,150 3,155 3,160 3,165 54 55 56 57 58 59 60 61 62 2$ 120.00 30 31 122.00 32 24 124.00 33 3,170 126.00 34 3,175 128.00 24 $4 24 35 3,180 130.00 36 3,185 3,190 132.00 37 134.00 3,196 3,200 38 price charged per visit 39 40 MIRA: Create an annotated XY (scatter) chart to show the break even point display your name in the title 41 42 43 44 45 A В C E F G H J 1 Robert's Clinic Annual Fixed Operating Costs $200,000.00 3 Variable Cost per Patient $ 58.00 Price Billed per Visit $ 122.00 # patients seen annually 3,000 6. 7 Total annual cost $374,000.00 Total annual revenues $366,000.00 Profit/loss -8,000.00 10 11 12 Use goal seek to find the break even point 13 14 15 16 17 18 19 20 21 A В C F G H J K M N On this sheet use goal seek to find out how much the variable cost per patient would have to change to allow the clinic to break even at 3100 patients annually. 2 Assume that the fixed cost stays at $200,000 and the price billed per visit is $122. 1 3 5 6. 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 P. A C D E F G J K L M P 1 On this sheet use goal seek to find out how much the price billed per visit would have to change to allow the clinic to break even at 3000 patients annually. 2 Assume that the variable cost per patient stays at $58 and the fixed cost stays at 200,000. 4 6. 8 9. 10 11 12 13 14 15 16 17 B.

Answer rating: 100% (QA)

Roberts Clinic Recalculation of Profit Loss Annual Fixed Operating Cost 200000 Variable cost per patient 58 198000 6000 price 122 200000 8000 patient ... View the full answer