You have been asked to forecast Steel Resources Inc.’s monthly Sales.

 

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 1^st quarter of 2014 and extending to the 3^rd quarter of 2017. Those data ($ millions) showed a sale's spike every 4^th 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 4^th quarter? (Why might this be?) 2 pts.

c. Show the complete data you would use to run a quarterly sales regression with a 4^th 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 4^th 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 P_HUT = $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.)