Equation that would estimate steel future sales

Assignment Help Microeconomics

Reference no: EM131044679

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. Run a scatter graph with time on the horizontal. Add trendline, equation and R^2 to graph.

b. Run an excel regression, assuming no seasonality in the data. Write the equation that would enable you to estimate Steel's future sales.

c. Overall does it appear that the regression you ran was a good fit? (Yes/No). On what do you base your answer?)

d. What is your specific sales forecast ($ millions)for each of the next 4 months?

e. Attach your excel spreadsheet with your answers, data used, scatter and regression output.

2. 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. (You can copy the data below and paste it in an excel spreadsheet.)

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

a. Running a simple excel regression, what is your estimate of sales for each travel agency for next month?

b. Do you believe that Advertising helps explain Sales for the 7 travel agencies? (Why?)

c. Using your estimating equation in part a (above), what would have been your specific estimate of $Sales for each travel agency (1, 2, 3, ..7) for the previous month? (Put them in the Table below.)

d. 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.)

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

e. 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?)

f. Attach your excel spreadsheet showing data used and regression output that would enable you to estimate Sales for each travel agency based on its level or advertising.

3. 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 2013 and extending to the 3rd quarter of 2016. 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. (You can copy the accounting data below and paste it on an excel spreadsheet.)

a. Create a scatter graph using quarterly sales (see data table below).

b. Does it appear that quarterly sales spike every 4th quarter? (Why might this be?)

c. Run a 4th quarter dummy variable regression equation. What is your general estimating equation for quarterly sales revenues?

d. What is your specific estimate of 4th quarter sales in 2016 ($ millions)) AND FOR EACH quarter in 2017?

Year	Quarter	Quarterly Sales ($ millions)
2012	1	700
	2	750
	3	800
	4	1500
2013	1	800
	2	1000
	3	950
	4	1650
2014	1	1000
	2	1100
	3	1300
	4	1800
2015	1	1050
	2	1150
	3	1200

e. Attach your excel spreadsheet showing data used and regression output that enabled you to estimate future quarterly sales with 4th quarter spikes.

Reference no: EM131044679

Questions Cloud

Risk-free rate for all maturities : The spot price of an investment asset that provides no income is $30 and the risk-free rate for all maturities (with continuous compounding) is 10%. Show all work. What, to the nearest cent, is the three-year forward price? Assume that the asset prov..

A price setter usually has a large market share : A price setter usually has a large market share. Price setters must watch cost more closely than price takers. A health provider in a competitive market is usually a price taker. Health care providers are: A price setter or price taker, depending on ..

Contracts are necessary for hedging the portfolio : A portfolio is worth $24,000,000. The futures price for a Treasury note futures contract is 110 and each contract is for the delivery of bonds with a face value of $100,000. On the delivery date the duration of the bond that is expected to be cheapes..

Experiencing rapid growth-dividends are expected to grow : Momsen Corp. is experiencing rapid growth. Dividends are expected to grow at 25 percent per year during the next three years, 15 percent over the following year, and then 6 percent per year indefinitely. The required return on this stock is 12 percen..

Equation that would estimate steel future sales : Run an excel regression, assuming no seasonality in the data. Write the equation that would enable you to estimate Steel's future sales

Odd dividend policy-how much will you pay for share today : Maloney, Inc., has an odd dividend policy. The company has just paid a dividend of $2 per share and has announced that it will increase the dividend by $6 per share for each of the next five years, and then never pay another dividend. If you require ..

Discounted payback period if discount rate is zero percent : An investment project costs $10,000 and has annual cash flows of $3,000 for six years. What is the discounted payback period if the discount rate is zero percent? (Enter 0 if the project never pays back. What is the discounted payback period if the d..

Calculate marginal revenue and marginal cost : Calculate marginal revenue and marginal cost for each quantity. Graph them. At what quantity do these curves cross? How does this relate to your answer to part (a)

What is the present value of her inheritance of the ship : Mac has bequeathed his spaceship Serenity to his second in command, Zoe, upon his death. Zoe knows that Mac's weekly hairbreadth escapes from mortal danger cannot continue forever, and she estimates he has six years to live. Given that Serenity is no..

User Account

All Pages