Loading...

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

The Adobe Flash plugin is needed to view this content

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

years) - 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 UNIT
- (PRICE 70) 0 70

2400 - (PRICE 70) gt 0

INCREASE DECREASE - (PRICE 70) lt 0

DECREASE INCREASE - Change in number of units - 37 (PRICE - 70)
- Using the second assumption, then
- Number of unit 2400 Change in number of

units - 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

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

factors) - 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

nonlinear - 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

changed.

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

(B2-C2)2. - 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

D6(E6B5)(F6B52)(G6B53) - 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