1 / 27

Basic Profit Models

Basic Profit Models. Chapter 3 Part 1 – Influence Diagram. SPREADSHEET MODELING. In building spreadsheets for deterministic models, we will look at:. ways to translate the black box representation into a spreadsheet model. recommendations for good spreadsheet model design and layout.

igor-guerra
Download Presentation

Basic Profit Models

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. Basic Profit Models Chapter 3 Part 1 – Influence Diagram

  2. SPREADSHEET MODELING In building spreadsheets for deterministic models, we will look at: • ways to translate the black box representation into a spreadsheet model. • recommendations for good spreadsheet model design and layout • suggestions for documenting your models • useful features of Excel for modeling and analysis

  3. SPREADSHEET MODELING Two ingredients combine to make Apple Pies: Fruit and frozen dough Step 1: Study the Environment and Frame the Situation Critical Decision: Setting the wholesale pie price Decision Variable: Price of the apple pies (this plus cost parameters will determine profits) Example 1: Simon Pie The Pies are then processed and sold to local grocery stores in order to generate a profit. Follow the three steps of model building.

  4. SPREADSHEET MODELING Step 2: Formulation Using “Black Box” diagram, specify cost parameters Model Pie Price Exogenous Variables Unit Cost, Filling Unit Cost, Dough Unit Pie Processing Cost Fixed Cost An Influence Diagram pictures the connections between the model’s exogenous variables and a performance measure (e.g., profit). profit The next step is to develop the relationships inside the black box.A good way to approach this is to create an Influence Diagram.

  5. SPREADSHEET MODELING start with a performance measure variable. Decompose this variable into two or more intermediate variables that combine mathematically to define the value of the performance measure. Further decompose each of the intermediate variables into more related intermediate variables. Continue this process until an exogenous variable is defined (i.e., until you define an input decision variable or a parameter). To create an Influence Diagram:

  6. SPREADSHEET MODELING Decompose this variable into the intermediate variables Revenue and Total Cost Profit performance measure variable Start here:

  7. SPREADSHEET MODELING Total Cost Revenue Profit Now, further decompose each of these intermediate variables into more related intermediate variables ...

  8. SPREADSHEET MODELING Total Cost Processing Cost Ingredient Cost Required Ingredient Quantities Pies Demanded Unit Pie Processing Cost Unit Cost Filling Unit Cost Dough Pie Price Fixed Cost Profit Revenue

  9. SPREADSHEET MODELING Step 3: Model Construction Based on the previous Influence Diagram, create the equations relating the variables to be specified in the spreadsheet.

  10. SPREADSHEET MODELING Profit Total Cost Revenue Profit = Revenue – Total Cost

  11. SPREADSHEET MODELING Profit Revenue Revenue = Pie Price * Pies Demanded Pies Demanded Pie Price

  12. SPREADSHEET MODELING Profit Total Cost Processing Cost Ingredient Cost Total Cost = Processing Cost + Ingredients Cost + Fixed Cost Fixed Cost

  13. SPREADSHEET MODELING Profit Total Cost Processing Cost Processing Cost = Pies Demanded * Unit Pie Processing Cost Pies Demanded Unit Pie Processing Cost

  14. SPREADSHEET MODELING Profit Total Cost Ingredients Cost = Qty Filling * Unit Cost Filling + Qty Dough * Unit Cost Dough Ingredient Cost Required Ingredient Quantities Unit Cost Filling Unit Cost Dough

  15. SPREADSHEET MODELING Pie Price Pies Demanded and sold Unit Pie Processing Cost ($ per pie) Unit Cost, Fruit Filling ($ per pie) Unit Cost, Dough ($ per pie) Fixed Cost ($000’s per week) $8.00 16 $2.05 $3.48 $0.30 $12 Simon’s Initial Model Input Values

  16. Chapter 3Part 2Break-Even and Cross-Over Analysis MGS 3100

  17. Background • The Generalized Profit Model: • A decision-maker will break-even when profit is zero. • Set the generalized profit model equal to zero, and then solve for the quantity (Q). • For simplicity, assume that the quantity produced is equal to the quantity sold. This assumption will be relaxed in the module on decision analysis.

  18. Basic Relationships • Profit (π) = Revenue (R) - Cost (C) • Revenue (R)= Selling price (SP) x Quantity (Q) • Cost (C) = [Variable cost (VC) x Quantity (Q)] + Fixed Cost (FC) • Remember quantity produced = quantity sold

  19. Basic Relationships con’t • By substitution: • π = (SP x Q) –((VC x Q) + FC) • π = SP*Q - VC*Q – FC • π = (SP-VC)*Q - FC Notice sign reversal when parentheses are removed! Just a bit of algebraic reorganization…

  20. Contribution Margin • If Contribution Margin (CM) = SP-VC, then by substitution… • π = CM*Q – FC • In case you want to figure the quantity at break-even, you just need to rearrange

  21. Break-Even Quantity • π = CM*Q – FC • π + FC = CM*Q • (π + FC)/CM = (CM*Q)/CM • (π + FC)/CM = Q • Q = (π + FC)/CM • In the case of break-even, where π =0, the formula boils down to: • Q = FC/CM

  22. Quantity and Profit Example • Again, Q = (FC + π)/CM • If fixed cost is $150,000 per year, selling price per unit (SP) is $400, and variable cost per unit (VC) is $250, what quantity (Q) will produce a profit of $300,000? • Q = ($150,000+$300,000)/($400-$250) • Q = $450,000/$150 • Q = 3000

  23. Cross-Over Point • The cross-over point (or indifference point) is found when we are indifferent between two plans. • In other words, the quantity when profit is the same for each of two plans.

  24. Cross-Over Point, con’t • To find the cross-over point for Plan A and B, set the profit formulas for each plan equal to each other: • πplanA = πplanB, so • (CM*Q – FC) planA = (CM*Q – FC)planB • QAtoB = (FCA - FCB)/(CMA – CMB)

  25. Cross-Over Point, con’t • So all you need are the fixed costs and contribution margins (selling price and variable cost) to solve. • For example, here are three plans

  26. Cross-Over Point, con’t What is the profit at each of these points? Cross-Over Points A to B B to C QCO(150,000-450,000)/(150-250) (450,000-2,850,000)/(250-300) = 3000 units = 48,000 units

  27. Calculating Profit at the Cross-Over • After calculating cross-over, we have a quantity that can be plugged back into the formula to find profit at the cross-over point πB = CMB*Q - FCB = 250(48,000) - 450,000 = $11,550,000, or πC = 300(48,000) - 2,850,000 = $11,550,000 πA = CMA*Q – FCA = 150(3000) - 150,000 = $300,000, or πB = 250(3000) - 450,000 = $300,000

More Related