In: Finance
SHOW WORK
1. A surgery center has a procedure whose gross charge is $1,200 of which $800 is collected. The variable cost per procedure is $300. The monthly fixed cost assigned to doing this procedure is $40,000. The center wants to generate a $2,500 profit each month in doing this procedure.
a. How many procedures per month, rounding up to the next highest whole number if necessary, would it need to do to achieve its profit target?
b. How many procedures per month, rounding up to the next highest whole number, would it need to do to break-even?
c. On your Excel spreadsheet note the algebraic formula you used to solve these two problems. Using the Excel skills developed in the program so far, make your spreadsheet so that it prints on one page, and is readable. Use virtual cell references so that your work can be easily adapted to test other assumptions. Label each tab. Put your name in a footer on each tab. And, include your name in the filename.
a. Number of procedures per month, it would need to do to achieve its profit target:
Charge collected per procedure=$800
Variable Cost per procedure=$300
Unit Contribution Margin=800-300=$500
Break even number of procedures=Fixed Cost/Unit Contribution Margin=40000/500=80
At 80 procedures there is no profit and no loss
Required profit=$2500
Number of procedures required to be performed above Break Even Point=2500/500=5
. Number of procedures per month, it would need to do to achieve its profit target=80+5=85
b. Number of procedures per month,it would need to do to break-even:
Break even number of procedures=Fixed Cost/Unit Contribution Margin=40000/500=80
At 80 procedures it would break even
c.Break Even Calculation and formula in excel attached. Profit=$0 is the Break Even Point