Question

In: Operations Management

The process of break-even analysis examines the relationship between revenue and costs for different factors of...

The process of break-even analysis examines the relationship between revenue and costs for different factors of production. The main emphasis is in determining the number of units and the sales volume at which the company will recover its costs. At this level of production and sales the company's profits are zero. By examining this initial break-even point companies can analyze the risk of a particular project as well as any potential profits that can be garnered.
In a spreadsheet analysis of break-even problem, the advantage is that many "what if" scenarios can be examined without lengthy computation times and fear of computational errors are virtually eliminated. In addition, by taking advantage of the graphical capabilities of spreadsheet programs, the results can be shown graphically as well as numerically.
A well designed spreadsheet would accommodate cells for the input data such as fixed costs, variable cost per unit, price per unit, etc. It is possible to even provide for raw observational data to be used such as sales volume, total cost, and quantity. However, we will leave that model for a subsequent analysis. In addition, the model should provide an output range to include relevant factors like break-even quantity, break-even dollars, and target profit, etc. The model should be designed such that as the input values are changed the corresponding output values are automatically modified to reflect these changes. This process involves setting up relative formulas for the computation of break-even quantity, break-even dollars, and projected profits. Finally, the model should provide a graphical representation of the problem. This would best be represented as a linear graph of revenue, cost, and profit displayed as a function of quantity.
Some useful hints for creating a well-designed break-even computer model include:
1. The basic computational equation for break-even quantity is: Break-Even Quantity = Fixed Costs / (Price per unit - Variable Cost per Unit) 2. Break-even dollars can be computed using the revenue or cost formula evaluated at the break-even quantity. 3. Projected profits can be evaluated using the profit function evaluated at the planned production level. 4. Each of the above formulas should be written with relative references to the cells of the spreadsheet that contain the input data of price per unit, variable cost per unit, fixed costs, and planned production level.
Break-Even Analysis Project - Saint Francis Hospital
Saint Francis Hospital has an operating room used only for eye surgery. The annual cost of rent, heat, and electricity for the operating room and its equipment is $275,000, and the annual salaries for the people who staff this room total $1,270,000. These costs are the same


Page 2 of 2

regardless how many surgeries are performed. Each surgery performed requires the use of $1,375 worth of medical supplies and drugs. To promote goodwill, every patient receives a bouquet of flowers the day 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 operation performed. Last year the hospital performed 950 operations and plans to continue at this level of production.
Identify the revenue per case (price per unit) and the annual fixed and variable costs for running the operating room. Set up your spreadsheet so that theses inputs can readily be changed. Set up an output range to calculate the break-even quantity and dollar amount for total revenue and costs. Also, set up an output range to display the projected profits resulting from different levels of production. How many eye operations must the hospital perform each year in order to break even? What would the annual profits be if they perform 950 operations each year?
One of the nurses has just learned about a machine that would reduce the cost of medical supplies needed by $580 per patient. It can be leased for $475,000 annually. Keeping in mind the financial costs and benefits, advise the hospital on whether or not they should lease this machine. Use the spreadsheet to identify the break-even point and the level of profit associated with 950 operations per year. Modify the fixed and variable costs as appropriate and examine the break-even quantity and profits again.
An advertising agency has proposed to the hospital's president that she spend $10,000 per month on television and radio advertising to persuade people that Saint Francis Hospital is the best place to have any eye surgery performed. The advertising firm estimates that such publicity would increase business by 30 operations per month. If they are correct, what impact would this advertising have on hospital's profit? What would happen to the break-even point? In case the advertising agency is being overly optimistic, what would the decision be if the advertising campaign only increased the number of operation per month by 5? What is the maximum amount the Hospital would be willing to pay for the advertising if the ads generated 30 additional operations each month? Consider this option independent 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 do these decisions have on profits and risk for the hospital?
Prepare a written report summarizing your results and recommendations. Include an explanation of the effects of changing the price, variable cost, and fixed costs on the break-even point and profits. The report should include printouts of the various spreadsheets and graphs to support your conclusions.
Deliverables
1. Executive summary report to address relevant problem definition, assumptions, alternative solutions, and optimal solutions selection. A Microsoft Word file entitled, Project1.docx 2. Detailed numerical analysis with appropriate calculations and charts for the various scenarios. A Microsoft Excel file entitled, Project1.xlsx

Solutions

Expert Solution

There are 4 Scenarios in this case

  • Scenario 1 - Normal calculations for 950 operations
  • Scenario 2 - 950 operations with Machine leasing
  • Scenario 3 - 950 + 360 (30*12) operations with advertising
    • Scenario 3a - 950 + 60 (5*12) operations with advertising
  • Scenario 4 -  950 + 360 (30*12) operations with advertising and machine leasing

Please refer below images for responses and report.

Scenario 1 and formulas

Scenario 2 and formulas

Scenario 3,3A and formulas

Scenario 4 and formula

Comparison Graph - All 4 scenarios profit and breakven


Related Solutions

Here is a break-even analysis model. Break-even Analysis Revenue Selling Price per unit $20 Costs Fixed...
Here is a break-even analysis model. Break-even Analysis Revenue Selling Price per unit $20 Costs Fixed Costs per unit $210,000 Variable Cost per unit $8 Break-even Point Quantity (Q) 17,500.00 Do the following two tasks. a. Create a strategy table showing how the quantity changes as the selling price varies from $18 to $22. b. Create a strategy table showing how the quantity changes as the selling price varies from $18 to $22 and the variable cost per unit changes...
Break-even analysis attempts to determine the volume of sales necessary for a business to cover costs, or to make revenue equal costs.
BREAK-EVEN ANALYSIS EXERCISEBreak-even analysis attempts to determine the volume of sales necessary for a business to cover costs, or to make revenue equal costs. It is helpful in setting prices, estimating profit or loss potentials, and planning for the coming period.The general formula for calculating break-even units is:Break-even Units = Fixed Costs / Price – Unit Variable Cost1. An important distinction is made in the calculation between fixed and variable costs. Fixed costs do not change with the units sold (at...
describe applications of break-even analysis in practice. provide limitations of break-even analysis.
describe applications of break-even analysis in practice. provide limitations of break-even analysis.
What is break-even? How is break-even calculated? How is a break-even analysis used? What are the...
What is break-even? How is break-even calculated? How is a break-even analysis used? What are the risks if break-even is not analyzed carefully?
Break even analysis is at the core of unit costs. Do you believe the fixed costs...
Break even analysis is at the core of unit costs. Do you believe the fixed costs used as a numerator is the best method for this analysis? Why or why not? Answer needs to be over 5 lines, thank so much.
The objective of break-even analysis is:
The objective of break-even analysis is:A.determine the number of units to produce that will equate total profit with total costB.determine the number of units to produce that will equate total revenue with total costC.determine the number of units to produce that will equate variable cost with fixed costD.determine the number of units to produce to maximize profit
Break-even Analysis : Break-even analysis attempts to determine the volume of sales necessary for a manufacturer...
Break-even Analysis : Break-even analysis attempts to determine the volume of sales necessary for a manufacturer to cover costs, or to make revenue equal costs. It is helpful in setting prices, estimating profit or loss potentials, and determining the discretionary costs that should be incurred. The general formula for calculating break-even units is: Break-even Units = ( Total fixed costs ) / ( Unit selling price - Unit variable cost ) In StratSim, total fixed costs can be broken into...
What is the purpose of break even Analysis?
What is the purpose of break even Analysis?
Consider the concept of break even analysis and target income. In order to apply break even...
Consider the concept of break even analysis and target income. In order to apply break even analysis, why would the expenses reported in external financial reports need to be reorganized into categories based on cost behavior? How do these analytical tools relate to product pricing and cost management (i.e., why would this analysis be useful to management)?
Explain break-even analysis, its purpose, and whether break-even analysis can be used in manufacturing and/or service...
Explain break-even analysis, its purpose, and whether break-even analysis can be used in manufacturing and/or service industry.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT