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 |
|
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 EACHquarter 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.)
e. What level of laborers produce the
maximum average production? (2 pts.) (Calculate a specific number.
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.)
f. Write the estimated equation for
marginal revenue. (2 pts.)
g. From the above data, create an AVC
data table in excel that allows us to estimate Domino’s AVC regression equation
(AVC = a + bQ +c Q^2). (3 pts.)
h. Using the data from your table,
run a regression that will enable you to estimate the company’s AVC. (3 pts.)
i.
Write Domino’s estimated AVC equation. (2 pts.)
j.
From your AVC equation, write Domino’s estimated TVC equation. (2 pts.)
k. Write Domino’s estimated MC
equation. (2 pts.)
l.
Using your estimated equations for MR (part f) and MC (part k), what is
Domino’s optimal output of pizza per week that results in maximum profits for
Dominos? (Q* is found by setting MR = MC, then using Math is Fun’s Quadratic
Problem Solver to solve the simplified quadratic equation. (2 pts.)
m. If Domino’s want to maximize its
profits, what PRICE should it set for their pepperoni pizzas for an average
store? (2 pts.)
