Planning Models - PowerPoint PPT Presentation


PPT – Planning Models PowerPoint presentation | free to download - id: 556e66-MmYwM


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

Planning Models


Planning Models Operations Analysis Using MS Excel Chapter 4 Planning Models Planning modeling Chapter Outline: The basic planning problem The basic pricing problem 3. – PowerPoint PPT presentation

Number of Views:54
Avg rating:3.0/5.0
Slides: 23
Provided by: WEYoun
Learn more at:


Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Planning Models

Chapter 4
  • Planning Models

Operations Analysis Using MS Excel
Planning modeling
  • Chapter Outline
  • The basic planning problem
  • The basic pricing problem
  • 3. Nonlinear cost and demand functions
  • XP Function
  • Mathematical model of an XY Function
  • Spreadsheet Model of XY Function
  • Approximating the cost with a Cubic Function
  • Preparing a Five year Plan
  • The Impact of Pricing

The Basic Planning Problem
Down is a skeleton model for a five year
projection of profit for a corporation.
  • Assumption
  • Selling price is 60 (not change over next five
  • Fixed cost 1500 (grow at constant rate)
  • Number of units 80 (grow at a constant rate)
  • Variable cost 45 per unit (not change over next
    five years)

The NPV function calculates the net present value
based on a series of cash flows. The syntax of
this function is NPV(rate,value1,value2,...)
The annual cash flows are the (profit revenues
minus costs) generated from the investment during
its lifetime. NPV compares the value of a
dollar today to the value of that same dollar in
the future, taking inflation and returns into
account. These cash flows are discounted or
adjusted by incorporating the uncertainty and
time value of money. NPV is one of the most
robust financial evaluation tools to estimate the
value of an investment. NPV gt 0 the investment
would add value to the firm, so the project may
be accepted NPV lt 0 the investment would
subtract value from the firm, so the project
should be rejected NPV 0 the investment would
neither gain nor lose value for the firm, so we
should be indifferent in the decision whether to
accept or reject the project.
The Basic Planning Problem
The Basic Planning Problem
Data table is created to view What-if Analysis on
the growth rates for fixed costs and units sold
The Basic Pricing Problem
  • The central issue in pricing is determining how
    quantity sold depends on the price.
  • Demand in most cases is elastic, that is when the
    price increases, the demand decreases.
  • The simplest assumption is that demand is a
    linearly decreasing function of price.
  • Organization assumptions or judgments
  • At price 70, sales will be 2,400
  • One dollar increase in price 37 units decrease
    in the sale
  • Using the above information to express the number
    of units sold as a function of price.
  • (PRICE 70) 0 70
  • (PRICE 70) gt 0
  • (PRICE 70) lt 0
  • Change in number of units - 37 (PRICE - 70)
  • Using the second assumption, then
  • Number of unit 2400 Change in number of
  • Number of units 2400 - 37 (PRICE - 70)
    4990 ( 37 PRICE )

The Basic Pricing Problem
4990 37 Price
50000 35 Quantity
Price Quantity
Revenue Total Cost
Marketers are interested in a price range of 60
to 100. They expect fixed cost to be 50000,
with a unit cost of 35.
Maximum profit occurs around 42,750 at the price
85. More accurate value can be generated by
entering more numbers in the column providing the
input to the table. However exactness might be
misleading because of the uncertainty in the
demand function.
Nonlinear Cost and Demand Function
  • Most applications in real life have nonlinear
    relationships. They follow a curved, nonlinear XY
  • EX.
  • Torrington Corporation, deciding whether they
    should introduce a new product.
  • Production prepares a cost estimate for making up
    to 150 units. (variable cost will not be a linear
    function of quantity)
  • Graph is prepared to show the curve representing
    variable cost
  • The problem is to develop formulas to give the
    cost values for any value of the quantity.

Nonlinear Cost and Demand Function Mathematical
Models of an XY Functions
Suppose Torrington wishes to determine the cost
associated with the quantity 70. Find the slope
of any line segment. Considering the cost at 50
with 3,000 then slope is calculated as Slope
(Y2-Y1)/(X2-X1) (4,500-3,000)/(100-
50) 30 Cost Y1 Slope ( X-X1) Quantity
(X) 70, X150, and Cost Y1 3,000 Then Cost
for X 3,000 30 (70 50) 3,600
Nonlinear Cost and Demand Function Spreadsheet
Models of an XY Functions
  • Cells A3 to C7 (Number of units, variable cost,
    slope) contain a lookup table that Excel uses to
    find the necessary parameters for a given number
    of units.
  • Cells B9 to B11 are user-entered data.(Input
  • Cells B12 to B14 use the lookup table to find
    required items of data.
  • Cell B15 computes the variable cost for the
    number of units entered in cell B10,
  • Variable cost UP ( NUMBER OF UNIT LEFT )
    SLOPE using the formula B13 ( B10-B12)B14
  • Cell B16 computes the total cost by adding the
    fixed and variable costs using B11 B15
  • Cell B17 compute revenue using B9B10
  • Cell B18 profits using B17-B16

Nonlinear Cost and Demand Function Spreadsheet
Models of an XY Functions
The table in the left shows the data table
comparing number of units with profits Traini
ng Exercise Calculate profits against number of
units being sold, where number of units start
from 0 up to 150 with increment of 10 units.
Find the break-even point using Goal seeker?
Nonlinear Cost and Demand Function Approximating
the Cost with a Cubic Function
  • Curves are a good facility for representing
  • functions. Polynomial is a class of functions
    that are often
  • satisfactory.
  • The linear (first-order) function has the
    following form
  • 2 (10 X)
  • Quadratic function assumes the form
  • -24 (56 X2)
  • Cubic function assumes the form
  • (5.3 X2) ( 21.6 X3)

Nonlinear Cost and Demand Function Approximating
the Cost with a Cubic Function
The general approach 1- Try polynomials,
quadratic, cubic, and so on, on the spreadsheet
representing the situation. 2- Calculate the
values for the given curve 3- Calculate the
square of the deviations (differences), add
them 4- Minimize the sum with Excel Solver by
allowing the coefficient of function to be
Nonlinear Cost and Demand Function Approximating
the Cost with a Cubic Function
  • Cell A3 uses the formula AVERAGE(A2, A4).
  • Column C calculates the cubic function based on
    the current coefficients in cells F3 to I3. For
    example Cell C2 uses the formula
  • F3 (G3A2)(H3A22) (I3A23).
  • Column D calculates the squared difference
    between the variable cost and the cube function.
    For example, cell D2 uses the formula
  •  Cell D10 shows the original sum of deviations.

Nonlinear Cost and Demand Function Approximating
the Cost with a Cubic Function
  • The target cell D10 needs to be minimized. There
    are no constraints. The cells to vary are the
    cubic coefficient in cells F3 to I3.
  • The better way to judge how good the
    approximation is to compare the given curve with
    the calculated curve.
  • If the management feels that the approximation is
    not good enough, a fourth, fifth order polynomial
    or other type of function can be tried.

Preparing a Five-Year plan
  • The lookup table is no longer required as cubic
    equation replaces it
  • Cell B7 in the cubic model now computes the
    variable cost using the cubic function
  • This Modified Model can be used to perform
    scenario analysis

Preparing a Five-Year plan
  • The management is particularly interested in
    three scenarios
  • They are also interested in growth over the next
    five years

Preparing a Five-Year plan
The Impact of Pricing
  • Apply the cubic function approach to the analysis
    of pricing
  • The Marketing suggests the following pegs to
    approximate the price versus quantity
  • The first step is to develop a cubic function for
    quantity based on price.

Price Quantity 20 250 40 150
60 100 80 60
The Impact of Pricing