Introduction Steps for a TWO-WAY data table.

1 Determine whether the paramters, calculations, and outputs are correctly linked.
DECISIONS 2 Decide which parameters we wish to analyse. We will start with a TWO-WAY data table to change the two decision cells
Selling price $1,200 Location A's store size (sq ft): 100, 150, 200, 250, …, 450, 500
Selling price: $800, $900, …, $1300, $1500
Question: What happens to the total monthly income?
Store size 200 200
3 Construct the data table on the same worksheet as our model. Set up a table where:
OUTPUT i) the first row represents different values of Location A's store size (cells J28-R28).
ii) the first column represents different values selling price (cells I29-I36)
Monthly income $ (4,642.13) $ 11,977.14 $ 7,335.00 We will use these values as INPUTS to our model.

PARAMETERS 4 In the upper left corner of the table (I28), reference the OUTPUT that we want to report.
Does not vary by store Set cell I28 to E11 (the total income).
Cost per backpack $ 600.00
Salary (per store) $ 20,000.00 5 Highlight the entire table (cells I28 to R36).
pricing factor 0.0007 On the menu bar, go to Data-> Forecast-> What-if Analysis-> Data Table.
Row input cell: Which number in the model will we vary based on the first row of our data table (i.e. Loc A store size)?
Varies by store Answer - Select cell C7
locationPopularity 500 $ 1,700.00 Column input cell: Which number in the model will we vary based on the first column of our data table (i.e. selling price)?
Cost per sq ft $ 75.00 120 Answer - Select cell C4

6 You can use a max function to determine the maximum monthly income in the table.
CALCULATIONS

Conversion rate 0.16 0.16 Change store sizes

Traffic 316.23 583.10 Location A, store size
Estimated Demand 51 93 7,335 100 150 200 250 300 350 400 450 500
800 (510) 162 140 (325) (1,105) (2,125) (3,333) (4,696) (6,188)
Revenue (Selling price * Demand) 60,715.73 111,954.28 900 18,044 19,872 20,825 21,218 21,214 20,908 20,364 19,625 18,724
Cost of production (Cost per backpack * Demand) 30,357.87 55,977.14 1,000 25,304 27,585 28,919 29,648 29,947 29,921 29,637 29,142 28,471
Gross profit (Revenue - Cost) 30,357.87 55,977.14 Selling 1,100 21,271 23,300 24,422 24,965 25,095 24,914 24,486 23,855 23,056
price 1,200 5,943 7,018 7,335 7,168 6,658 5,887 4,910 3,764 2,477
Rent (Store size * cost per sq feet) 15,000.00 24,000.00 1,300 (20,678) (21,262) (22,343) (23,741) (25,365) (27,160) (29,091) (31,131) (33,265)
Other fixed costs (salary) $ 20,000.00 $ 20,000.00 1,400 (58,593) (61,539) (64,611) (67,764) (70,974) (74,227) (77,515) (80,831) (84,170)
Operating expenses (rent+fixed cost) 35,000.00 44,000.00 1,500 (107,802) (113,813) (119,470) (124,899) (130,168) (135,314) (140,364) (145,335) (150,239)
Max monthly income 29,947
Monthly income(Gross profit - operating expense)$ (4,642.13) $ 11,977.14

Recommendation: we will considers store size: 300sq feet and selling price: $1000 for location A to get the largest monthly income
Scenario Summary
Current Values: Very Optimistic Optmistic Pessimistic
Changing Cells:
$C$21 $ 75.00 $ 70.00 $ 74.00 $ 80.00
$C$16 $ 20,000.00 $ 21,000.00 $ 20,000.00 $ 22,000.00
$C$17 0.0007 0.0006 0.0007 0.0008
Result Cells:
$E$11 $ 29,947.22 $ 68,262.96 $ 49,655.09 $ (14,368.52)
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in grey.
Introduction

DECISIONS Steps for a ONE-WAY data table. Steps for Scenario Manager
Selling price $1,000 1 Determine whether the paramters, calculations, and outputs are correctly linked. 1 Name the cells that we want to change, as well as the output cell (optional). Go to the name box at the top left of Excel.
2 Decide which parameters we wish to analyse.
Salary cost 2 Go to Data-> Forecast-> What-if Analysis-> Scenario Manager.
Store size 300 200 Question: What happens to the total income if slary cost changes?
3 Click "Add" and follow the instructions to construct each scenario.
OUTPUT You can select more than one "changing cells" and use commas to separate them.
3 Construct the data table on the same worksheet as our model. Set up a table where:
Monthly income $ 3,975.80 $ 25,971.42 $ 29,947.22 i) the column represents different values of "salary" that we want to consider (cells J27-J34)
We will use these values as INPUTS to our model.
PARAMETERS
Does not vary by store
Cost per backpack $ 600.00 4 At the TOP of the table, reference the OUTPUT that we want to report.
Salary (per store) $ 20,000.00 Set cell K26 to E11 (the total income).
pricing factor 0.0007
5 Highlight the entire table (cells J26 to K34).
Varies by store On the menu bar, go to Data-> Forecast-> What-if Analysis-> Data Table.
locationPopularity 500 $ 1,700.00 Row input cell: Which number in the model will we vary based on the first row of our data table?
Cost per sq ft $ 75.00 120 Answer - n/a - leave this blank
Column input cell: Which number in the model will we vary based on the first column of our data table (i.e. salary)?
Answer - Select cell C16
CALCULATIONS

Conversion rate 0.30 0.30 29,947

Traffic 387.30 583.10 18,000 33,947
Estimated Demand 116 175 19,000 31,947
20,000 29,947 4 Click "Summary" when you are done.
Revenue (Selling price * Demand) 116,189.50 174,928.56 21,000 27,947 Choose to output E11 (i.e. total monthly income) as the "result cell".
Salary
Cost of production (Cost per backpack * Demand) 69,713.70 104,957.13 22,000 25,947
Gross profit (Revenue - Cost) 46,475.80 69,971.42 23,000 23,947 You can hide columns H to Q to see the
24,000 21,947 instructions for Scenario Manager.
Rent (Store size * cost per sq feet) 22,500.00 24,000.00 25,000 19,947
Other fixed costs (salary) $ 20,000.00 $ 20,000.00 Max monthly income 33,947
Operating expenses (rent+fixed cost) 42,500.00 44,000.00

Monthly income(Gross profit - operating expense)$ 3,975.80 $ 25,971.42