7/23/2019 Caso Watson Trucks

1/18

ModelingProcess Page1

ModelingProcess

StructuredModelingProcess(PowellandBatt,ModelingforInsight,Wiley)

1. FrametheProblem

2.

Diagramthe

Problem

3. BuildaModel

4. GenerateInsight

ModelingTools(PowellandBatt,ModelingforInsight,Wiley)

1. InfluenceDiagrams

2. SpreadsheetEngineering

a. Design

i. SketchtheSpreadsheet

ii. OrganizetheSpreadsheetintoModules

iii. StartSmall

iv.

IsolateInput

Parameters

v. DesignforUse

vi. KeepItSimple

vii. DesignforCommunication

viii. DocumentImportantDataandFormulas

b. Build

i. FollowaPlan

ii. BuildOneModuleataTime

iii. PredicttheOutcomeofeachFormula

iv. CopyandPasteFormulasCarefully

v. UseRelativeandAbsoluteAddressingtoSimplifyCopying

vi. UsetheFunctionWizardtoEnsureCorrectSyntax

vii. UseRangeNamestoMakeFormulasEasiertoRead

viii. UseDummyInputDatatoMakeErrorsStandOut

c. Test

i. MaintainaSkepticalAttitude

ii. CheckthatNumericalResultsLookPlausible

iii. CheckthatFormulasareCorrect

iv. TestModelPerformance

d. Analyze

i. BaseCaseAnalysis

ii. WhatIfAnalysis

1. DataSensitivity

2. TornadoChart

iii. BreakevenAnalysis

iv. OptimizationAnalysis

v. SimulationAnalysis

3. Parameterization

7/23/2019 Caso Watson Trucks

2/18

ModelingProcess Page2

4. SensitivityAnalysis

5. StrategyAnalysis

6. IterativeModeling

Watson

Truck

Rental

(adaptedfromMoore&Weatherford2001)

Consistingof50largetrucksrentedbyindustrialcontractors,theWatsonTruckRentalCompanyisforsalefor

$1,000,000.EricWatson,theseller,wantsyoutodevelopathreeyeareconomicanalysistoassistpotentialbuyers

inevaluatingthecompany.

Watsonpayspropertytaxesof$35,000peryear,anditcosts$4800pertruckperyeartoadministerandmaintain

thefleet.Thepropertytaxesareexpectedtogrowatarateof4%peryear,andthemaintenancecostsare

expectedtogrowat7%peryear.

Truckrental

rate

is

currently

$1000

per

month

each.

At

this

rental

rate

on

average

60%

of

the

trucks

are

rented

eachmonth.Watsonbelievesthatifheloweredtherentby$100pertruckpermonth,hewouldincreasethe

averagerentalpercentagebysevenpercentagepointsandthatthisincrementwouldapplytoeachadditional

reductioninrentrateof$100(orfractionthereof).Forexample,ata$600truckrentalrate88%ofthetrucks

wouldberentedeachmonth.Whatevertruckrentalrateissetforthefirstyearwillbeincreasedby9%peryear

foryears2and3. Averagepercentoftrucksrentedinyears2and3willbethesameasdeterminedinthefirst

year,regardlessoftheincreasedrentalrateinthoseyears.

Attheendofthreeyears,Watsonassumesthebuyerwillresellthetruckbusinessforcashataprofit.Theselling

priceatthattimeisassumedtobethreetimestherevenueinyear3.Cashflowineachyearisassumedtobethe

sameasthenetincome(revenueexpenses)forthatyear. Effectsofdepreciationandotherfactorsrelatingto

incometaxes

can

be

ignored

for

this

analysis.

Cashflowinyear3includesinadditionthecashfromtheresaleofthebusiness.Overallinvestmentprofitis

definedtobetheNetPresentValueoftheannualcashflows(discountrate=10%)includingthepurchasepriceat

thebeginningofyear1(assumeotherflowsoccurattheendoftheirrespectiveyears).Assumenotrucksare

boughtorsoldduringthethreeyears.

7/23/2019 Caso Watson Trucks

3/18

ModelingProcess Page3

DevelopInfluenceChartandPseudocode

7/23/2019 Caso Watson Trucks

4/18

ModelingProcess Page4

ModelOutline

7/23/2019 Caso Watson Trucks

5/18

ModelingProcess Page5

InputsandFormulas

7/23/2019 Caso Watson Trucks

6/18

ModelingProcess Page6

OneWayDataTableAnalysis

NPV vs. Initial Rental Rate

($600,000)

($500,000)

($400,000)

($300,000)

($200,000)

($100,000)

$0

$100,000

$200,000

$300,000

$0 $200 $400 $600 $800 $1,000 $1,200 $1,400 $1,600

Rental Rate

NPV

7/23/2019 Caso Watson Trucks

7/18

ModelingProcess Page7

OneWayTables(Some,NotAll)forSensitivityChart

CompletedSensitivity(Spider)Chart

Sensitivity Analysis

$-

$100,000.00

$200,000.00

$300,000.00

$400,000.00

$500,000.00

80% 90% 100% 110% 120%Percent

NPV

Purchase Cost

Maint Cost/Unit

Property Taxes

Sales Multiplier

7/23/2019 Caso Watson Trucks

8/18

ModelingProcess Page8

UsingScenarioManager

SupposewewanttoexaminethreescenariosthatweclassifyasBestCase,MostLikelyandWorst

Casefortheinputvariablesshowninthefollowingtable:

WorstCase MostLikely BestCase

PurchasePrice $1,250,000 $1,000,000 $750,000

VarCost/Truck $4,000 $4,800 $5500

PropTaxGrowth 6% 4% 3%

TruckCostGrowth 10% 7% 5%

RentalRateInflation 7% 9% 12%

BusinessSale

Multiplier

1.5 3 4

7/23/2019 Caso Watson Trucks

9/18

ModelingProcess Page9

continuetheprocessuntilallscenariosaredefined

ThenexamineresultsusingSummary

Choosingoneormoreresultcells:

7/23/2019 Caso Watson Trucks

10/18

ModelingProcess Page10

7/23/2019 Caso Watson Trucks

11/18

ModelingProcess Page11

PracticeExample

{Moore&Weatherford,DecisionModeling.}

Astartupcompany,SimonPiecompanygeneratesprofitfromcombiningtwopurchasedingredients(

fruitandfrozendough)intoapplepies,processingthepies(cookingpackaging,deliveryetc),andselling

themtoalocalgrocerystore.ThecompanysfounderSamuelSimonintendstobuildanexcelmodelto

explorehisoptions.

SimonsInitialModelInputValues

PiePrice $8

Piesdemandedandsold 16

Unitpieprocessingcost($perpie) $2.05

Unitcost,

fruit

filling

($

per

pie)

$3.48

Unitcost,dough($perpie) $0.30

Fixedcost($000sperweek) $12

BlackBoxView

InfluenceDiagram

7/23/2019 Caso Watson Trucks

12/18

ModelingProcess Page12

KeyRelationships:

SketchSpreadsheetLayout:

BuildModel:

7/23/2019 Caso Watson Trucks

13/18

ModelingProcess Page13

Model:

After

some

reflection,

Simon

concludes

that

at

a

pie

price

of

$12

there

would

be

no

demand

for

his

pies

andthatbelowa$12pricehewouldgainanadditional4000piessoldperweekforeach$1reductionin

price.So,forsimplicity,heassumesthatademandrelationshipisexpressedbyalinearequation,

producingthefollowingequationforSimonsweeklypiedemandinthousandsofpies:

DevelopSketchandlinearmodel

7/23/2019 Caso Watson Trucks

14/18

ModelingProcess Page14

Incorporatingthischangeinourmodel:

7/23/2019 Caso Watson Trucks

15/18

ModelingProcess Page15

Wewanttoexaminetheimpactofpiepriceonprofitusingadatatable.

ExampleContinued

Examiningthemodel,itappearsthatactualweeklypieprofitsaresomewhatlessthanprojected. In

reviewingthemodelsbehaviorforlowerpieprices,SimonsuspectsthatthemodelsProcessingCost

formulaproducesthecorrecthistoricalcostforthebasecaseof12,000PiesDemanded,butnotfor

othervaluesofPiesDemanded.

So,wecollectdataonActualProcessingCostfordifferentlevelsofpieproduction(seebelow).

Developamodelforprocessingcostbasedondatacollected. Revisedyourmodeltoincorporatethis

revisedrelationshipbetweenproductionandprocessingcost.

7/23/2019 Caso Watson Trucks

16/18

ModelingProcess Page16

RevisingModel

FinalFormulas:

7/23/2019 Caso Watson Trucks

17/18

ModelingProcess Page17

AnotherDataTableandXYScatter

SensitivityAnalysis

Weexaminetheimpactofinitialparametersvaryingacrossarangeof80%to120%oforiginal

estimatedvalueandobservedtheimpactonProfitthroughacombinationofonewaydatatablesand

XYscatter.

Developaspiderchartofunitcostfruitfilling,unitcostdough,demandintercept,demandslopeand

fixedcost.

Comment

on

insights

you

can

gain

from

this

chart:

7/23/2019 Caso Watson Trucks

18/18

Profit Sensitivity

($2,000)

($1,800)

($1,600)

($1,400)

($1,200)

($1,000)

($800)

($600)

($400)

($200)

$0

80% 90% 100% 110% 120%

% Change in Base Value

Profit

Unit Cost, Filling

Unit cost, Dough

Demand Intercept

Demand Slope

Fixed Cost