A major source of revenue in Texas is a state sales tax on certain tyes of goods
ID: 3182051 • Letter: A
Question
A major source of revenue in Texas is a state sales tax on certain tyes of goods and services. Data are compiled and the state controller uses them to project futre revenues for the state budget. One particular category of goods is classified as retail trade. Four years of quarterly data (in $ millions) for one particular area of southeast Texas follow:
A. compute seasonal indices for each quarter based on a CMA
B. Deseasonalize the data and develop a trend line on the deseasonalized data.
C. Use the trend line to forecast the sales for each quarter of year 5
D. Use the seasonal indices to adjust the forecasts found in part (c) to obtain the final forecasts.
Please Use Excel And show every step (take screenshot of excel if you can't include the actual file)!!!
quarter year 1 year 2 year 3 year4 1 268 288 283 294 2 295 297 310 325 3 297 311 321 335 4 341 347 371 381Explanation / Answer
Year Quarter Sales MA CMA Ratio Avg_Ratio SI DeS Time 1 1 268 0.910914 294.2098 1 1 2 295 0.970794 303.8751 2 1 3 297 300.25 302.75 0.981007 0.993885 298.8274 3 1 4 341 305.25 305.5 1.116203 1.124407 303.2709 4 2 1 288 305.75 307.5 0.936585 0.91004 0.910914 316.1658 5 2 2 297 309.25 310 0.958065 0.969862 0.970794 305.9353 6 2 3 311 310.75 310.125 1.002821 0.992931 0.993885 312.9136 7 2 4 347 309.5 311.125 1.115307 1.123328 1.124407 308.6071 8 3 1 283 312.75 314 0.901274 0.910914 310.6768 9 3 2 310 315.25 318.25 0.974077 0.970794 319.3264 10 3 3 321 321.25 322.625 0.994963 0.993885 322.9751 11 3 4 371 324 325.875 1.138473 1.124407 329.9516 12 4 1 294 327.75 329.5 0.892261 0.910914 322.7526 13 4 2 325 331.25 332.5 0.977444 0.970794 334.7777 14 4 3 335 333.75 0.993885 337.0612 15 4 4 381 1.124407 338.8452 16 a) S.I Q1 0.910914481 Q2 0.970793578 Q3 0.99388473 Q4 1.124407211 b) Deseasonalized data is as mentioned above in the table. Trend line based on time as dependent variable is 292.8264+2.75698(Time) c) Q1 292.8264+2.75698(17) 339.6951 Q2 292.8264+2.75698(18) 342.452 Q3 292.8264+2.75698(19) 345.209 Q4 292.8264+2.75698(20) 347.966 d) Q1 309.4331493 Q2 332.4502413 Q3 343.0979736 Q4 391.2554795
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.