spreadsheet demonstration walton’s bookstore simulation
TRANSCRIPT
Spreadsheet DemonstrationSpreadsheet DemonstrationSpreadsheet DemonstrationSpreadsheet Demonstration
Walton’s Bookstore SimulationWalton’s Bookstore SimulationWalton’s Bookstore SimulationWalton’s Bookstore Simulation
2
Walton’s bookstore simulationWinston Example 12.1
In August, Walton Bookstore must decide how many of next year’s nature calendars to order. Each calendar costs the bookstore $7.50 and is sold for $10. After February 1 all unsold calendars are returned to the publisher for a refund of $2.50 per calendar. Walton believes that the number of calendars it can sell by February 1 follows the probability distribution. Walton wants to maximize the expected profit from calendar sales. Specifically, the company wants to use simulation to determine the number of calendars to order in August.
In August, Walton Bookstore must decide how many of next year’s nature calendars to order. Each calendar costs the bookstore $7.50 and is sold for $10. After February 1 all unsold calendars are returned to the publisher for a refund of $2.50 per calendar. Walton believes that the number of calendars it can sell by February 1 follows the probability distribution. Walton wants to maximize the expected profit from calendar sales. Specifically, the company wants to use simulation to determine the number of calendars to order in August.
3
Walton can order calendars once for demand in coming year
Demand is random Walton wants to achieve best trade-off between
ordering too few and too many
Walton can order calendars once for demand in coming year
Demand is random Walton wants to achieve best trade-off between
ordering too few and too many
Walton’s bookstore simulationBasic problem
4
Only uncertainty is demand for calendars Modelled as a discrete distribution with given
probabilities
Only uncertainty is demand for calendars Modelled as a discrete distribution with given
probabilities
Walton’s bookstore simulationUncertainty
5
Unit cost of purchasing calendars Unit price of selling calendars Unit refund for any calendars left over at the end of
the season
Unit cost of purchasing calendars Unit price of selling calendars Unit refund for any calendars left over at the end of
the season
Walton’s bookstore simulationMonetary inputs
6
Only decision is how many calendars to order Decision criterion: maximize the expected profit
Only decision is how many calendars to order Decision criterion: maximize the expected profit
Walton’s bookstore simulationDecision variable
7
Step 1: Enter any trial value of order quantity and all inputs, including: Monetary inputs Probability distribution of demand (enter cumulative
probabilities as well)
Step 1: Enter any trial value of order quantity and all inputs, including: Monetary inputs Probability distribution of demand (enter cumulative
probabilities as well)
Developing the spreadsheet model(See Excel “Step 1” sheet)
8
Step 2: Generate a random number with the RAND function
Step 3: Generate a demand with the VLOOKUP function Depends on the random number from step 2 and the
“lookup table” including cumulative probabilities and demands
Step 2: Generate a random number with the RAND function
Step 3: Generate a demand with the VLOOKUP function Depends on the random number from step 2 and the
“lookup table” including cumulative probabilities and demands
Developing the spreadsheet model(See Excel “Steps 2-7” sheet)
9
Steps 4-7: Based on the random demand from step 3, calculate the revenue, purchase cost, refund, and profit
Steps 4-7: Based on the random demand from step 3, calculate the revenue, purchase cost, refund, and profit
Developing the spreadsheet model(See Excel “Steps 2-7” sheet)
10
Step 8: Copy row 13 to 49 other rows to obtain 49 new replications of the simulation Note how the random numbers (and hence everything
else) vary from row to row
Step 8: Copy row 13 to 49 other rows to obtain 49 new replications of the simulation Note how the random numbers (and hence everything
else) vary from row to row
Developing the spreadsheet model(See Excel “Step 8” sheet)
11
Step 9: Calculate summary measures (average, stdev, max, min) of the 50 simulated profits
Step 10: Calculate a confidence interval for the expected profit, based on the 50 simulated profits
Step 9: Calculate summary measures (average, stdev, max, min) of the 50 simulated profits
Step 10: Calculate a confidence interval for the expected profit, based on the 50 simulated profits
Developing the spreadsheet model(See Excel “Steps 9,10” sheet)
12
Construct a data table for the average profit versus a list of possible order quantities The “column input” cell for this table is the original
order quantity cell For these particular 50 demands, an order quantity of
150 maximizes average profit It wouldn’t necessarily be best for other randomly
generated demands
Construct a data table for the average profit versus a list of possible order quantities The “column input” cell for this table is the original
order quantity cell For these particular 50 demands, an order quantity of
150 maximizes average profit It wouldn’t necessarily be best for other randomly
generated demands
Developing the spreadsheet model(See Excel “DataTable 1” sheet)
13
Construct a bar chart of the average profits from the data table Again shows how 150 is the best order quantity
Construct a bar chart of the average profits from the data table Again shows how 150 is the best order quantity
Developing the spreadsheet model(See Excel “BarChart 1” sheet)
14
Another way to generate replications is with a data table Enter the typical formula to replicate (formula for
profit) Enter replication number (1 to 50) in a column Generate a data table with “column input” cell equal
to any blank cell In Tools/Options, check the “Automatic Except Tables”
option under the Calculation tab
Another way to generate replications is with a data table Enter the typical formula to replicate (formula for
profit) Enter replication number (1 to 50) in a column Generate a data table with “column input” cell equal
to any blank cell In Tools/Options, check the “Automatic Except Tables”
option under the Calculation tab
Developing the spreadsheet model(See Excel “DataTable 2” sheet)
15
Go one step further: create a two-way data table that replicates the profit 50 times for each of several order quantities Column input cell is any blank cell, row input cell is the
order quantity cell Calculate the average profit (and any other summary
measures) for each order quantity
Go one step further: create a two-way data table that replicates the profit 50 times for each of several order quantities Column input cell is any blank cell, row input cell is the
order quantity cell Calculate the average profit (and any other summary
measures) for each order quantity
Developing the spreadsheet model(See Excel “DataTable 3” sheet)
16
Create a bar chart of the average profits from the two-way data table Again, 150 appears to be the best order quantity in
terms of average profit
Create a bar chart of the average profits from the two-way data table Again, 150 appears to be the best order quantity in
terms of average profit
Developing the spreadsheet model(See Excel “BarChart 2” sheet)
17
Use random numbers to simulate a single replication Use the copy command or a data table (with blank
column input cell) to replicate this single simulation Calculate summary measures from the replications
and create relevant graphs
Use random numbers to simulate a single replication Use the copy command or a data table (with blank
column input cell) to replicate this single simulation Calculate summary measures from the replications
and create relevant graphs
Developing the spreadsheet modelSummary of basic steps