1 / 15

Spreadsheet Models - DSS

Learn to analyze and model a motel business scenario with fixed and variable costs, conduct breakeven analysis, sensitivity analysis, and determine optimal pricing strategies for maximizing profit.

rnathaniel
Download Presentation

Spreadsheet Models - DSS

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. Spreadsheet Models - DSS Basic Profit Models What-if, Sensitivity Analysis

  2. Lecture Objectives • You should be able to : • Analyze a business situation and draw an influence diagram. • Build basic profitability models on a spreadsheet. • Perform what-if, sensitivity analyses.

  3. Breakeven Analysis Consider a relatively simple situation: Sally owns a motel with a hundred rooms. Fixed daily cost is $1000 (includes mortgage, staff salaries, maintenance). Variable cost per room is $10 per day (includes extra utility cost, room cleanup, etc). At a fixed room price of $50 per day, what is the breakeven point? • Draw an influence diagram leading up to your profit. • Compute the breakeven point.

  4. Influence Diagram The boxes that cannot be split any further (for this simple example) are the basic inputs for the analysis. How is Number of Rooms Rented different from the rest of the inputs? Are there any other dependencies that are not shown above?

  5. Breakeven Analysis

  6. Breakeven Point

  7. Crossover Point You have the option of subcontracting to improve room quality and the surroundings, but that would increase fixed costs to $1800, with no change to variable costs. You will, however, be able to charge $70 per room per day. At what point will you be indifferent between your current mode of operation and the new option?

  8. Crossover Analysis – Point of Indifference

  9. Crossover Analysis

  10. Pricing Analysis – Demand Function • If the demand for rooms depends on the price as follows: • Quantity Demanded = 200 - 3*price, • what price should Sally charge for a room? • Assume Fixed Cost is still $1000 per day and Variable cost is $10 per day per room. • Determine the Goal. • How would Sally get such a demand equation for her business? • Determine the best price to help her reach her goal.

  11. Price and Profit What is the best price?

  12. Profit Vs. Price

  13. Sensitivity Analysis If the estimate of Variable Costs ($10 per room per day) is inaccurate, how does it affect the solution?

  14. Sensitivity to Variable Costs

  15. Extend the Analysis How would this entire analysis change if you were analyzing a larger hotel like the Marriott instead of a motel?

More Related