1 / 14

Useful EXCEL Functions

Useful EXCEL Functions. 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

buffy-berg
Download Presentation

Useful EXCEL Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Useful EXCEL Functions

  2. 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

  3. 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.

  4. 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

  5. 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.

  6. 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)

  7. Break-Even Analysis Case Problem - (A) p. 21

  8. Break-Even Analysis Solution to the Case Problem

  9. Break-Even Analysis What-If Analysis

  10. Break-Even Analysis Model Variations

  11. Break-Even Analysis Model Variations

  12. Break-Even Analysis Model Variations

  13. Break-Even Analysis Sensitivity Analysis with Two-input Data Tables

  14. Break-Even Analysis Sensitivity Analysis with Two-input Data Tables

More Related