1. Bass model. For this question use HW 2.xlsx, sheets “bass 0” through “bass 5”. You have been tasked with forecasting the sales path for a new online digital movie access platform that can be purchased and downloaded for a price. You don’t know the best price to set because you don’t have any existing sales data to work with here. But you are still interested in how quickly this new product will grow to its long-run steady sales level. You believe that the coefficient of imitation (q) is .25. You expect a long-run sales level to reach 500,000. You hope to reach this level in the next 3 to 4 years (24 to 36 months). You have access to some data which has tracked another similar product’s innovator parameter (p) at various prices. In your excel spreadsheets for this question, each period represents 1 month).
a. In bass 0, estimate a regression with p as a function of price for this other similar product (put your output in D2). Use this equation to determine p for prices equal to $10, $20, and $30. Put those p numbers in N6 through Q6. (that is, you will estimate an intercept (a) and slope coefficient (b). this will give you a an equation p = a + b(price). Then you can calculate p = a+b(10), etc.).
. In Bass 1, produce your n(t) and N(t-1) forecasts for price $10. In Bass 2, for price = $20, and Bass 3, price = $30.
c. In Bass 4, produce a graph of all three cumulative sales forecasts together.
d. How many sales are expected after 1 year if the price is $10?
e. Approximately how many additional months are needed before this your sales value rom part d is reached if the price is $30?
f. Suppose your boss insists that you reach long run market sales after 2 years. What level of p will get us to 500,000 after 2 years (try different values of p, starting with .15 first – I have gotten you started in cell D3. Then add or subtract from this value in D3 until period 24 (E26) gives you 500,000)? Use your estimated price equation, and your selected p value if D3 to calculate the implied price. Based on this results, what would you tell boss about what the price would be in order to do this?
SAVE your Excel file. Call it ‘YOURNAME’_HW2.xlsx. Save this Word file. Call it YOURNAME_HW2.docx.
2. Markov. For this question use HW2.xlsx, sheets “Markov”. City managers have been collecting data on traffic delays for resident’s morning and afternoon commutes at the interchange between I 680 and I 80 in Omaha Ne
aska between Mondays and Fridays.
There are three possible outcomes: a 15 minute delay, a 30 minute delay, and a 45 minute delay in average commute times in a given week. They have found that on average, for a given week, if there is a 15 minute delay on one day, there is a 65 percent chance there will be a 15 minute delay the next day, and a 20 percent chance that there will be a 30 minute delay the next day. If there is a 30 minute delay on one day, the probability of a 30 minute delay the next day is 50 percent, and the probability of a 15 minute delay the next day is 20 percent. If there is a 45 minute delay on one day, the probability of a 45 minute delay the next day is 15 percent, and the probability of a 30 minute delay the next day is 30 percent.
a. Form your transition matrix (p). Using Excel calculate p3. Save your results.
. Suppose that given budget constraints, the city, as a matter of policy, will not want to undertake major road network expansions until there is eventually a 22 percent or more probability of a 45 minute or longer delay. What does this policy mean for the I 680, I 80 interchange?
Save your Excel file
3. Transportation problem (simple with no fixed costs). Use HW2.xlsx, “TransLP1” and “TransLP2” for this question. ZYX, Inc. manages three production plants, located in New Jersey, Chicago, and Denver. Its major markets are in Atlanta, Houston, Los Angeles, and Seattle. In each of those cities, it manages inventory and distribution centers. These centers need to be appropriately stocked with goods to meet expected demand. Table 1 below shows each plant’s productive capacity and Table 2 shows expected demand at each city. Table 3 gives the per unit cost of shipping from a given plant to a given distribution center. The company needs to meet demand but not exceed capacity levels at its plants nor its distribution centers.
a. Set this problem up in transLP1 as suggested in the spreadsheet and solve for the amount the firm will produce out of each plant and will ship to each distribution center in order to minimize transportation costs. Interpret your cost figures as being in $1,000s of dollars (so if you get an answer of say $2,000, consider that $2 million).
. ZYX Inc. is one of 5 firms that ship goods from New Jersey to Atlanta. The other 4 firms are interested in lo
ying the federal government to improve the road network between New Jersey and Atlanta. Such improvements are expected to reduce the per-unit cost of shipping from 15 to 10. Between legal fees, travel, and other lo
ying expenses, the firms have invited ZYX to participate in this lo
ying effort. However, it has been estimated that in order to be successful, each firm pay $20 thousand a piece to support lo
y effort. Should ZYZ Inc. participating lo
ying effort? Do your analysis in transLP2. Explain your answer.
Save your Excel file
4. Location Decision using Stepwise regression (follow class example). For this question, use HW 2.xlsx DD data, DD Results step fn, and DD Decision, Also use the HW 2 Stepwise.xlsx Excel file as well. In DD data, you will find data on 302 different Taco Bell restaurants in the mid-west. The company is looking to build more restaurants but wants to know where to build. It has determined that success is measured by average revenue per transaction over a given year (REV_TRANS) and has identified 13 additional variables that it thinks might impact REV_TRANS.
Open HW 2 Stepwis.xlsx. notice that in “Data” sheet I have copied your Taco Bell data there. Follow the directions on the “instructions” sheet (and my video where I used stepwise regression) to determine, in the “Regression” sheet, which variables to use to predict REV_TRANS. BEFORE you do this, make sure that in the “Settings” sheet you set the value in B4 to .15 (there is a lot of noise in this data so we are accepting some weaker significance cutoff level).
Once you have completed this process, take the data results in G10 through H## (## depends on which row contains the last variable listed and it’s coefficient). Paste this data into DD Results step fn A2 to B##.
Once you have done this, in P8 through P17 in the DD Decision sheet, calculate using your estimated coefficients and the data for each potential site a predicted value for REV_TRANS.
If the minimum acceptable level of REV_TRANS is $9.30 (put on B4), where should the firm locate its new restaurants?
Save your Excel file. Save your WORD file. Post both to Canvas (you do not need to post the stepwise Excel file).
table 3
AtlantaHoustonLos AngelesSeattle
New Jersey XXXXXXXXXX
Chicago9121815
Denver2081210
Unit cost
table 1capacity
New Jersey40
Chicago50
Denver40
table 2demand
Atlanta30
Houston20
Los Angeles40
Seattle25
Chapter 2
Unit 4: Supplement
Markov
Markov Model – long run
Will there be a point in the future where the transition probabilities don’t change from period to period?
For many transition matrices, the answer is “yes”
These are called the limiting steady-state, or long-run, transition matrix: S
Markov Model – long run
A key characteristic of these long-run transition matrices, S, is that the rows are the same
Markov Model – long run
Recall that in the long run, the elements of S do not change as we move forward in time with our P matrix
Mathematically, this means the following:
Markov Model – long run
Recall that the rows of our S matrix are the same. This means we can eliminate one of the rows so that, mathematically, our system of equations now looks like the following (again using our HyVee example):
Markov Model – long run
This gives us two equations
We also have a third equation: s1+s2=1 (why?)
Markov Model – long run
Often, this is better than doing it by hand
Why? Well, you could have a system of more than 2 equations (3, 4, 5 etc.) and this gets cumbersome to solve by hand.
So how do we solve for the long run s1 and s2 in Excel?
procedure
Take our two equations
.8s1 + .3s2 = s1
s1 + s2 = 1
Re-write them as follows (just group the s1 terms together in the first equation)
-.2s1 + .3s2 = 0
s1 + s2 = 1
procedure
procedure
Now you can’t really divide by a matrix.
But you can calculate a matrix’s inverse.
More details on this in Unit 6
This is a pain to do but super easy in Excel
So let’s go to Excel and do this.
12
12
ss
ss
éù
=
êú
ëû
S
1212
1212
orforourHyVeeexample:
ssss
0.80.2
ssss
0.30.7
éùéù
éù
=
êúêú
êú
ëû
ëûëû
S = SP
[
]
[
]
1212
0.80.2
ssss
0.30.7
éù
=
êú
ëû
112
212
12
s0.8s0.3s
s0.2s0.7s
1ss
=+
=+
=+
0.60.4
0.60.4
éù
=
êú
ëû
S
Chapter 2
Unit 4: Forecasting: Issues and Models
Regression and Causality
Markov Chains
Bass Diffusion Model
Exponential Smoothing
Regression Model Forecasting and Causality
Unit 4 – Causality gasoline and crude oil.xls
Regression Models and Forecasting
Causality
Granger-Sims
Granger-Sims
Granger-Sims
.
y
x
Granger-Sims
The F statistic tests whether the βis are jointly significantly different from zero….
Granger-Sims
Granger-Sims
Then calculate the co
esponding F statistic
Same logic applies
Forecasting with Markov Chains
Unit 4 – Markov.xls
*
Markov Model
Markov Model - Applications
Market Share dynamics
Labor Migration
Regulation and