Develop an Excel model for Robert's Chiropractic Clinic - use the scenario provided below. Robert Berns...
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
Expert Answer:
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
Related Book For
Posted Date:
Students also viewed these programming questions
-
Question V Consider a forward contract for shares of company X. The current stock price of the share is $100. The share will pay quarterly dividends of $1/share, beginning in 3 months. The interest...
-
Accounting 1 J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY 1. Study ChaptDate Project J57 (hrs) 2.50 Project K52 (hrs) 2.00 December 3...
-
ACME Tech inc. Sales (SMillion) Product Line Q1 Q1% of YTD Q2 Q2% of YTD Q3 Q3% of YTD YTD Al 450 550 700 formula Robot 200 formula 150 220 Smart-Sensor 300 250 250 formula Company total...
-
Suppose that In Example 18.6 the electrical firm does not have enough prior information regarding the population mean length of life to be able to assume a normal distribution for p. The firm...
-
A company plans to begin production of a new small appliance. The manager must decide whether to purchase the motors for die appliance from a vendor at $7 each or to produce them in-house. Either of...
-
How much would an individual receive in benefits if she had a constant (wage-inflation adjusted) monthly income of $6,000, and how would that compare to someone who had an income one-third that size?
-
1.6 Mark Du Plessis in Johannesburg. Mark Du Plessis lives in Johannesburg, South Africa. He can buy a euro for R16.3700 (South African rand). At the same time, Richard Nolan, living in Paris, can...
-
Rojas Corporations comparative balance sheets are presented below. Additional information: 1. Net income was $22,630. Dividends declared and paid were $19,500. 2. All other changes in noncurrent...
-
. E 25-2 0 1227 PE 25-2B Discontinue a segment OBJ. 1 Product X has revenue of $94,800, variable cost of goods sold of $61,200, variable sell- ing expenses of $39,700, and fixed costs of $36,000,...
-
Alexander Smith and his wife Allison are married and file a joint tax return for 2019. The Smiths live at 1234 Buena Vista Drive, Orlando, FL 32830. Alexander is a commuter airline pilot but took 6...
-
Design and construct Band stop filter with the range from 72Hz to 160Hz.. Construct a Male-Female voice Recognition System: Recognize whether the voice is of a male or female. A MATLAB based GUI...
-
The contents of the report include: 1. Project Charter (Students can chose their FYP or any other sample project, in IT/Business domain) 2. Detailed Scope document 3. Work break down structure (WBS)...
-
Excel has many functions, that can do most of the calculationsyou'll want to use. However, there's one very useful and importantfunction lacking: simple linear interpolation. The easiest way to do...
-
Assume IBM is expected to pay a total cash dividend of $3.54 next year and dividends are expected to grow indefinitely by 2.8 percent a year. Assume the required rate of return (i.e. equity holder's...
-
Hw A Border has L= 300 m w=5m Q = 18.6115 Si= 0.7% n=0.2 Infiltration equation D=st 2 mm Infiltration equation D=5 2 Mw 0.6 0.6 I minutes C minutes. X (m) 0 30 60 90 120 150 180 210 240 270 300 X...
-
explain the concept of the neutral interest rate, also known as the equilibrium or natural interest rate, and how changes in factors such as productivity growth, demographic trends, and fiscal policy...
-
Selected data taken from a recent year's financial statements of trading card company Topps Company, Inc. are as follows (in millions). Net sales $375.7 Current liabilities, beginning of year 47.3...
-
For each of the following reactions, express the equilibrium constant: a) H20 (I) H2 (g) + 02 (g) Ke = 1.0x107 b) Fe2 (g) 2F (g) Ke= 4.9 x 10-21 c) C (s) + O2 (g) d) H2 (g) + C2H4 (g) C2H6 (g) Ke =...
-
Repeat Exercise 18 using the results of Exercise 16. In Exercise 18 a. y(0.54) and y(0.94)\ b. y(1.25) and y(1.93) c. y(1.3) and y(2.93) d. y(0.54) and y(0.94)
-
Find the approximations to within 104 to all the real zeros of the following polynomials using Newton's method. a. f (x) = x3 2x2 5 b. f (x) = x3 + 3x2 1 c. f (x) = x3 x 1 d. f (x) = x4 + 2x2 x...
-
The two-by-two linear system ax + by = e, cx + dy = f , Where a, b, c, d, e, f are given, can be solved for x and y as follows: Set m = c/a, provided a 0; d1 = d mb; f1 = f me; y = f1/d1; x = (e ...
-
\(\sqrt{396}\) Simplify the square root by expressing it in lowest terms.
-
\(\sqrt{2,940}\) Simplify the square root by expressing it in lowest terms.
-
\(\sqrt{2,400}\) Simplify the square root by expressing it in lowest terms.
Study smarter with the SolutionInn App