Question
A supermarket chain (FRESHM) funds the purchase of merchandise monthly by bank loans. Monthly loan data were collected over the last 25 months. See the
A supermarket chain (FRESHM) funds the purchase of merchandise monthly by bank loans. Monthly loan data were collected over the last 25 months. See the data at the end of this assignment. Answer the following questions:
a. Use the linear regression approach
1.A test for the presence of linear trend was run. From the computer output answer. Which statement is correct?
a.R sq = 0.78. This is clearly more than 0.05. There is a linear trend
b.P value for the intercept = 7.81E-18. This is clearly small, so the intercept is not zero. There is linear trend.
c.P value for 'Loans' = 4.42E-09 < 0.05. Beta1 is not zero, so there is a linear trend.
d.None of the above
2.For the regression equation you have created in question 1, what does the estimated slope of this equation tell you in this particular problem? Answer in the Answers sheet.
3.Use the regression equation of part '2' (not the template results) to make a prediction of expected loans for each of the next three months (months 26, 27, and 28). Answer in the Answers sheet. Show your calculations.
4.FRESHM wants to be 95% confident that it does not borrow less than the amount of funds actually needed, in any of the next three months (26, 27, 28). What loan is the minimum amount FRESHM have to borrow monthly, to meet the confidence requirement in each of the next three months? (Use the template!).
a.Already calculated above in a-3.
b.1424.83 in each month
c.1401.82, 1424.83, and 1447.70, in the three months respectively
d.None of the above
b.Use the Holt's approach.
5.Determine the best value of alpha and gamma, for the MAE criterion. Show your work on a spreadsheet.
6.For the model you obtained in question '5' using the optimal alpha and gamma, you should get L25 =1766.89, and T25 =66.88.Which statement is correct?
a.The local slope is L25/T25 = 1766.89/66.88 = 26.42
b.The intercept (namely, the time series at t=0) is estimated to be 1766.89 as of t=25
c.The average slope at t=25 is 66.88/25.
d.At t=25 the monthly loan is estimated to rise by 66880 dollars per month.
7.Use L25 =1766.89 andT25 = 66.88 from the template of the model you obtained in question '5' to calculate the forecast of loans for months 26, 27, and 28.
Show your calculations.
8.As a matter of policy, management will approve borrowing up to a total of $1,900,000 for next month (month 26). How confident can management be that this loan amount will be sufficient to purchase the merchandise required that month? You can use trial and error, or goal seek with the template.
The time series data
Month
Loan
in 1000$
Month
Loan
in 1000$
1
1156
14
1264
2
1101
15
1235
3
1195
16
1357
4
1072
17
1373
5
1237
18
1358
6
1106
19
1489
7
1148
20
1428
8
1005
21
1516
9
1350
22
1681
10
1115
23
1598
11
1196
24
1699
12
1298
25
1781
13
1260
Comment: Select this table then paste into an excel sheet as is, then organize the data in one column so that you can use the template or run regression.
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