Question
Hello, I need help with this excel assignment for my math class I'll list the data and the questions will follow. I'll appreciate any kind
Hello, I need help with this excel assignment for my math class I'll list the data and the questions will follow. I'll appreciate any kind of help!
The following items are a guide for responses to be addressed in project one. Note that WalMarts fiscal year starts the first week of February. This means that when analyzing the data, week 26 is actually week 30 (26+4 weeks for January) in 2002 or the end of July 2002. Also, week 52 is actually week 4 (52+4 weeks for January 2002 minus 52 weeks for 2002) in 2003 or the end of January 2003. Outliers (extreme values) are present in the data and can distort modeling results. As an example, spikes in sales (revenue) at weeks 28-30 occurs in weeks 32-34 (28+4 and 30+4) which represent mid to late August 2002. Another spike at week 58 week is actually week 10 in 2003 (58+4 weeks for January 2002 minus 52 weeks for 2002). This corresponds to sales for early March 2003. The question is whether these spikes are due to special events or holiday periods, or are perhaps due to restocking and stock availability.
Boxed Foods 2002-2003 Data
Week I Sales in $
26 2400
27 2000
28 1800
29 1750
30 1700
31 2500
32 3100
33 2400
34 2350
35 3100
36 3150
37 2300
38 2600
39 2025
40 2225
41 2200
42 1975
43 2025
44 2025
45 2400
46 2200
47 2600
48 1975
49 2700
50 2800
51 3600
52 3200
53 3025
54 3000
55 3400
56 3400
57 4050
58 4500
59 3850
60 3500
61 3475
62 4000
63 3900
64 3250
65 3600
66 4500
67 3600
68 4100
69 4300
70 4600
71 3950
72 4300
73 4300
74 4225
75 3975
76 4600
77 4300
1. Identify spikes (outliers) in the data where extreme sales values occur and correlate these spikes with actual calendar dates in 2002 or 2003 and with events that may occur during these periods.
2. Modeling the data linearly -
a. Generate a linear model for this data by choosing two points.
b. Generate a least-squares linear regression model for this data.
c. How good is this regression model? Output and discuss the R2 value.
d. What are the marginal sales (derivative, i.e. rate of change) for this department using the linear model with two data points and the regression model?
e. Compare the two models. Which do you feel is better?
f. Remove appropriate outliers as you deem necessary and rerun the linear regression model. What is the marginal sales and discuss improvements?
3. Modeling the data quadratically -
a. Generate a quadratic model for this data. Also output and discuss the R2 value.
b. What are the marginal sales for this department using this model?
c. Calculate the model generated a relative max/min value. Show backup analytical work.
d. Compare actual and model generated relative max/min value.
e. Remove outliers and rerun the quadratic least squares model. What is the marginal sales and discuss improvements?
4. Comparing models
a. Based on all models run, which model do you feel best predicts future trends? Explain your rationale.
b. Based on the model selected, what type of seasonal adjustments, if any, would be required to meet customer needs?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started