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 12point 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 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.)