The process of break-even analysis examines the relationshipbetween revenue and costs for different factors of production. Themain emphasis is in determining the number of units and the salesvolume at which the company will recover its costs. At this levelof production and sales the company's profits are zero. Byexamining this initial break-even point companies can analyze therisk of a particular project as well as any potential profits thatcan be garnered.
In a spreadsheet analysis of break-even problem, the advantage isthat many "what if" scenarios can be examined without lengthycomputation times and fear of computational errors are virtuallyeliminated. In addition, by taking advantage of the graphicalcapabilities of spreadsheet programs, the results can be showngraphically as well as numerically.
A well designed spreadsheet would accommodate cells for the inputdata such as fixed costs, variable cost per unit, price per unit,etc. It is possible to even provide for raw observational data tobe used such as sales volume, total cost, and quantity. However, wewill leave that model for a subsequent analysis. In addition, themodel should provide an output range to include relevant factorslike break-even quantity, break-even dollars, and target profit,etc. The model should be designed such that as the input values arechanged the corresponding output values are automatically modifiedto reflect these changes. This process involves setting up relativeformulas for the computation of break-even quantity, break-evendollars, and projected profits. Finally, the model should provide agraphical representation of the problem. This would best berepresented as a linear graph of revenue, cost, and profitdisplayed as a function of quantity.
Some useful hints for creating a well-designed break-even computermodel include:
1. The basic computational equation for break-even quantity is:Break-Even Quantity = Fixed Costs / (Price per unit - Variable Costper Unit) 2. Break-even dollars can be computed using the revenueor cost formula evaluated at the break-even quantity. 3. Projectedprofits can be evaluated using the profit function evaluated at theplanned production level. 4. Each of the above formulas should bewritten with relative references to the cells of the spreadsheetthat contain the input data of price per unit, variable cost perunit, fixed costs, and planned production level.
Break-Even Analysis Project - Saint Francis Hospital
Saint Francis Hospital has an operating room used only for eyesurgery. The annual cost of rent, heat, and electricity for theoperating room and its equipment is $275,000, and the annualsalaries for the people who staff this room total $1,270,000. Thesecosts are the same
Page 2 of 2
regardless how many surgeries are performed. Each surgery performedrequires the use of $1,375 worth of medical supplies and drugs. Topromote goodwill, every patient receives a bouquet of flowers theday after surgery. In addition, all patients require dark glasses,which the hospital provides free-of-charge. It costs the hospital$55 for each bouquet of flowers and $25 for each pair of glasses.The hospital receives a payment of $3,500 for each eye operationperformed. Last year the hospital performed 950 operations andplans to continue at this level of production.
Identify the revenue per case (price per unit) and the annual fixedand variable costs for running the operating room. Set up yourspreadsheet so that theses inputs can readily be changed. Set up anoutput range to calculate the break-even quantity and dollar amountfor total revenue and costs. Also, set up an output range todisplay the projected profits resulting from different levels ofproduction. How many eye operations must the hospital perform eachyear in order to break even? What would the annual profits be ifthey perform 950 operations each year?
One of the nurses has just learned about a machine that wouldreduce the cost of medical supplies needed by $580 per patient. Itcan be leased for $475,000 annually. Keeping in mind the financialcosts and benefits, advise the hospital on whether or not theyshould lease this machine. Use the spreadsheet to identify thebreak-even point and the level of profit associated with 950operations per year. Modify the fixed and variable costs asappropriate and examine the break-even quantity and profitsagain.
An advertising agency has proposed to the hospital's president thatshe spend $10,000 per month on television and radio advertising topersuade people that Saint Francis Hospital is the best place tohave any eye surgery performed. The advertising firm estimates thatsuch publicity would increase business by 30 operations per month.If they are correct, what impact would this advertising have onhospital's profit? What would happen to the break-even point? Incase the advertising agency is being overly optimistic, what wouldthe decision be if the advertising campaign only increased thenumber of operation per month by 5? What is the maximum amount theHospital would be willing to pay for the advertising if the adsgenerated 30 additional operations each month? Consider this optionindependent of the machine purchase described above.
Assuming the hospital decided to use the advertising program,should the hospital then also purchase the machine? What impact dothese decisions have on profits and risk for the hospital?
Prepare a written report summarizing your results andrecommendations. Include an explanation of the effects of changingthe price, variable cost, and fixed costs on the break-even pointand profits. The report should include printouts of the variousspreadsheets and graphs to support your conclusions.
Deliverables
1. Executive summary report to address relevant problem definition,assumptions, alternative solutions, and optimal solutionsselection. A Microsoft Word file entitled, Project1.docx 2.Detailed numerical analysis with appropriate calculations andcharts for the various scenarios. A Microsoft Excel file entitled,Project1.xlsx
