STUDENT NAME:
_____________________________________________
Instructions. For all questions below, attach excel
spreadsheets with the problem number and your name on each sheet. The excel
spreadsheet will include data, output, graphs, and answers to the questions.
(You will find it easier to use text boxes for your answers when you use
excel.)
1. You have been asked to forecast Steel
Resources Inc.’s monthly Sales.
Its past monthly sales (starting
from the oldest data) over the past 16 months were 22, 23, 25, 24, 27, 28, 28,
29, 31, 32, 33, 34, 34, 35, 36, 38. (These sales data are in $ millions).
a. Enter the data on an excel spreadsheet.
(2 pts.)
b. Run
a scatter graph with time on the horizontal.
(2 pts.)
c. Label each axis. (2 pts.)
d. Add trendline, equation and R^2 to graph.
Rewrite the equation in terms of Sales and Time and show it as a 12-point font.
(4 pts.)
e. If you were asked to estimate sales 3
and 4 periods from the end of the data collected, what sales values would you
have estimated? (4 pts.)
2. Using the above data;
a. Run an excel regression that would
enable you to forecast company sales. (Assume no seasonality in the data and
include the regression output on the excel sheet.) 4 pts.
b. Write the equation that would enable you
to estimate Steel’s future sales (2 Pts.).
c. Overall does it appear that the
regression you ran was a good fit? (Yes/No). On what do you base your answer?)
2 pts.
d. What is your specific sales forecast ($
millions) for each of the next 4 months? (8 pts.).
3. You collected data from the same month on
7 Travel Agencies in order to determine if you could estimate Sales for each
agency based on its level of Advertising.
a. Copy the data below and paste
it in an excel spreadsheet. (2 pts/)
Travel Agency
Advertising
Sales
1
20000
150000
2
25000
180000
3
45000
220000
4
40000
210000
5
55000
300000
6
60000
350000
7
80000
400000
b. Run an excel regression that forecasts
Sales for each agency based on its level of Advertising. Show your regression
output. (4 pts.)
c. Write the estimating equation for
travel agency sales? (2 pts.)
d. Do you believe that Advertising helps
explain Sales for the 7 travel agencies? (Why?) (2 pts.)
e. Using your estimating equation in part a
(above), what would have been your specific estimates of $Sales for each travel
agency (1, 2, 3, ..7)? (Put them in the Table below.) (7 pts.)
f. Under the null hypothesis, the estimate
of $Sales for each travel agency (1, 2, 3,….7) is the simple average of past
sales for all travel agencies. (Put that estimate in the column of the Table
below.) (2 pts.)
Travel Agency
Advertising ($)
Actual Sales
Predicted Sales (w Advertising)
Predicted Sales (under Null)
1
20000
150000
2
25000
180000
3
45000
220000
4
40000
210000
5
55000
300000
6
60000
350000
7
80000
400000
g. Using the last 3 columns of the above
table, which model appears to provide better estimates: the null (without
advertising); or the regression with advertising? (Which model do you believe should be used to
make future predictions?) (WHY?) (2 pts.)
4. You work at Amazon in its worldwide music
division and have been tasked with providing quarterly sales estimates for the
future. You asked the accounting department to provide you with quarterly sales
data starting in the 1st quarter of 2014 and extending to the 3rd quarter of
2017. Those data ($ millions) showed a sale’s spike every 4th quarter, so you
decide to use excel to run a regression with a dummy variable to estimate
quarterly sales.
a. In an excel spreadsheet, copy the data
below, and create a scatter graph showing quarterly sales. (4 pts.)
Year
Quarter
Quarterly Sales ($ millions)
2014
1
700
2
750
3
800
4
1500
2015
1
800
2
1000
3
950
4
1650
2016
1
1000
2
1100
3
1300
4
1800
2017
1
1050
2
1150
3
1200
b. Does it appear that quarterly sales spike
every 4th quarter? (Why might this be?) 2 pts.
c. Show the complete data you would use to
run a quarterly sales regression with a 4th quarter dummy variable. (4 pts.)
d. Using your data, run a regression that
would provide an estimation of future quarterly sales. Show the regression
output. (4 pts.)
e. Using the regression output, what is the
general estimating equation for quarterly sales? (2 pts.)
f. Using that general estimating equation,
what is your specific estimate for 4th quarter sales in 2017 ($ millions)) AND
FOR EACH quarter in 2017? (5 pts.)
5. You have been asked to estimate your
company’s production function. You assembled the following data over the last
20 months.
Time
L
Q
1
10
22.61
2
11
26.79
3
12
31.2
4
13
35.83
5
14
40.63
6
15
45.59
7
16
50.66
8
17
55.83
9
18
61.07
10
19
66.35
11
20
71.64
12
21
76.91
13
22
82.13
14
23
87.29
15
24
92.33
16
25
97.25
17
26
102.00
18
27
106.60
19
28
110.90
20
29
115.10
a. Copy the above data into an excel
spreadsheet. (2 pts.)
b. Create the data table that you will use
in order to estimate the company’s production function if it takes the form of
Q = AL^3 + BL^2. (4 pts.)
c. Run the regression that will allow you
to estimate the company’s production function of the form Q = AL^3 + BL^2. Be
careful: intercept?) Show the regression output. (3 pts.)
d. What is the general estimating equation
for the company’s production function? (2 pts.)
6. You’re a financial analyst for Domino’s
Pizza. Your department has collected weekly data for an “average” Domino’s
store in the USA over the past 24 weeks. Since Domino’s considers Pizza Hut as
its main competitor, your department also keeps weekly information on Pizza
Hut. You were given weekly data for Domino’s Pepperoni Pizza sales (starting
with the oldest data first), and both Domino’s and Pizza Hut’s prices for their
pepperoni pizzas, along with the average income of people who normally buy
pepperoni pizza, and the average variable cost of Domino’s pepperoni pizza.
a. Following are weekly data that was
collected over the last 24 weeks.
Week
Q
P (Domino’s)
M
P (Pizza Hut)
AVC
1
2,750
$8.65
$25,500.00
$10.55
4.125
2
2,800
$8.65
$25,600.00
$10.45
4.640
3
2,875
$8.65
$25,700.00
$10.35
5.469
4
2,849
$8.65
$25,970.00
$10.30
5.174
5
2,842
$8.65
$25,970.00
$10.30
5.096
6
2,816
$8.65
$25,750.00
$10.25
4.811
7
3,039
$7.50
$25,750.00
$10.25
7.516
8
3,059
$7.50
$25,950.00
$10.15
7.788
9
3,100
$7.50
$25,950.00
$10.00
8.360
10
3,090
$7.50
$26,120.00
$10.00
8.219
11
2,934
$8.50
$26,120.00
$10.25
6.168
12
2,942
$8.50
$26,120.00
$10.25
6.266
13
2,834
$8.50
$26,200.00
$9.75
5.007
14
2,517
$9.99
$26,350.00
$9.75
2.121
15
2,503
$9.99
$26,450.00
$9.65
2.021
16
2,502
$9.99
$26,350.00
$9.60
2.014
17
2,557
$9.99
$26,850.00
$10.00
2.418
18
2,500
$10.25
$27,350.00
$10.25
2.000
19
2,623
$10.25
$27,350.00
$10.20
2.952
20
2,633
$10.25
$27,950.00
$10.00
3.037
21
2,700
$9.75
$28,159.00
$10.10
3.640
22
2,729
$9.75
$28,264.00
$10.20
3.918
23
2,790
$9.75
$28,444.00
$10.25
4.535
24
2,800
$9.75
$28,500.00
$10.50
4.640
Below are a series of questions
that lead you into finding optimal output and prices for Dom’s pizza (profit
maximizing). Note: carry regression output (coefficients) decimals to 6 places
in the equations.
a. Copy from the above data table the information
you need in order to estimate Domino’s direct demand equation (weekly demand
for Dom’s pepperoni pizza) and show it on your excel spreadsheet. (2 pts.)
b. Run a regression that would enable you to
estimate weekly demand of Dom’s pepperoni pizza. Show regression output. (2
pts.)
c. Write your estimated weekly demand
equation for Domino’s peperoni pizza. (2 pts.)
d. Suppose for the next 4 weeks it is
assumed that M=$30,000 and PHUT = $10. Write the direct demand equation for
Domino’s pepperoni pizza that you’d use for those weeks. (2 pts.)
e. Using the direct demand equation, write
the inverse demand equation (solve for P). (2 pts.)
