Useful EXCEL Functions 38 HMP 654EXECMAS BreakEven Analysis

Useful EXCEL Functions -38 - HMP 654/EXECMAS

Break-Even Analysis • Break even analysis determines the volume of service needed to ensure that revenue generated will exceed costs. • Applications in health care • Basic Model – Revenue growths with increasing service volume – Total cost growths with increasing service volume but at a slower rate than revenue -39 - HMP 654/EXECMAS

Break-Even Analysis – The break-even point is the level of service volume at which total revenues equal total costs. – A service volume higher than the break-even point implies that revenues exceed costs. -40 - HMP 654/EXECMAS

Break-Even Analysis • Basic Linear Model – Total Revenue = Unit-revenue x Service volume TR = REV x N – Total Costs = Fixed Cost + Variable Cost TC = FC + VC – Variable Cost = Unit-cost x Service volume VC = COST x N -41 - HMP 654/EXECMAS

Break-Even Analysis Basic Linear Model • Fixed costs are those that are incurred regardless of how much service is provided. • Variable costs are items of expense that relate to the direct cost of providing care and are expressed as costs per unit of service delivered. -42 - HMP 654/EXECMAS

Break-Even Analysis • Solution to the Basic Linear Model TR = REV x N TC = FC + VC VC = COST x N Want to find N* for which TR = TC • N* = FC/(REV - COST) -43 - HMP 654/EXECMAS

Break-Even Analysis Case Problem - (A) p. 21 -44 - HMP 654/EXECMAS

Break-Even Analysis Solution to the Case Problem -45 - HMP 654/EXECMAS

Break-Even Analysis What-If Analysis -46 - HMP 654/EXECMAS

Break-Even Analysis Model Variations -47 - HMP 654/EXECMAS

Break-Even Analysis Model Variations -48 - HMP 654/EXECMAS

Break-Even Analysis Model Variations -49 - HMP 654/EXECMAS

Break-Even Analysis Sensitivity Analysis with Two-input Data Tables -50 - HMP 654/EXECMAS

Break-Even Analysis Sensitivity Analysis with Two-input Data Tables -51 - HMP 654/EXECMAS