question archive Algoma University school of Business and Economics Dr
Algoma University school of Business and Economics Dr
Subject:MS ExcelPrice: Bought3
Algoma University school of Business and Economics
Dr. Gerry Mahar
ADMN 2607: Management Science — Test Three
There are 50 points in the test that is worth 25% of your term grade. Select any combination of questions for the 50-point total. Show Excel models for full credit.
Q1. Operations manager, Margaret O’Meagher, in Miramichi, N.B. is interested in determining an optimal inventory policy at her Miramichi Valley Restaurant Ltd. (MVRL) for orders of frozen french fries from McCain Foods in Florenceville, N.B. The number of pounds of french fries used by MVRL over the past 15 weeks is as follows:
Week
Pounds
Week
Pounds
Week
Pounds
1
16,400
6
14,800
11
17,300
2
15,500
7
16,100
12
16,800
3
18,200
8
15,600
13
17,200
4
17,400
9
16,900
14
15,700
5
16,100
10
17,700
15
16,200
a. Graph this time series.
b. Verify statistically that the time series is stationary.
c. Using a three-week moving average, calculate the forecasted weekly demand for frozen McCain french fries at MVRL for weeks 16 through 20.
d. Using a four-week moving average, calculate the forecasted demand for McCain frozen french fries at the MVRL for weeks16 through 20.
e. Which moving average method gives the lower mean squared forecast error?
Suppose exponential smoothing is used to forecast weekly sales demand for McCain french fries.
f. What is the forecast of weekly french fry sales demand for weeks 16 through 20 using a smoothing constant at α =.10?
g. What is the forecast of the weekly french fry demand for weeks 16 through 20 using a smoothing constant of α =.20?
h. Which of the two smoothing constants gives the lower mean squared forecast error?
I. Which of the two smoothing constants gives the lower mean absolute value of the forecasting error?
j. Which of the two smoothing constants gives the lower mean absolute percent of the forecast error?
k. Which of the two smoothing constants gives the lower largest deviation of the forecast error?
OPTIONAL BONUS PART OF QUESTION 1. (5 points)
Suppose exponential smoothing is used to forecast weekly sales demand for McCain french fries for weeks 16 through 20. Use Solver to:
l. Determine the value of a that minimizes the mean squared forecast error.
m. Determine the value of a that minimizes the mean absolute value of the forecast error.
n. Determine the value of a that minimizes the mean.
Q2. Grand River Construction Company (GRCC) located in Brantford, Ontario has a construction budget to build a new clubhouse for the Brantford Harlequins Rugby Club. GRCC uses both union and non-union workers. Levelling and grading of land, electrical wiring and plumbing work is contracted out to union workers. The actual construction of the project, though supervised by union carpenters, is performed by mainly non-union laborers (including volunteers) to reduce costs. Project planning has produced the following chart.
Immediate Predecessors
Expected Completion Time (Days)
A
Grade land
-
3
B
Order building supplies
-
4
C
Hire non-union labor
-
7
D
Pour concrete slab
A
8
E
Receive/organize supplies
B
8
F
Build clubhouse frame
C, D, E
30
G
Electrical installation
F
15
H
Plumbing installation
F
11
I
Stucco/paint exterior
H
20
J
Dry wall/paint interior
G, H
18
K
Finish/cleanup
I, J
6
L
Landscaping
K
8
a. Draw a PERT/CPM network for this problem.
b. Prepare a chart showing the earliest/latest start and finish times and the slack for each activity. What is the expected completion time for the rugby club project?
c. What is the impact of a delay of 1 day in the pouring of the concrete slab?
d. On Day 42, what is the priority of jobs? If there were some cross -trained electrician- plumbers, what might be a good decision?
e. What is the impact of building the clubhouse 3 days early?
f. What is the priority of job at the start of the project? Why?
g. On Day 58, what is the priority of jobs? Why?
Q3. A HOME LANDSCAPTING OPTION IN CAPE BRETON, NOVA SCOTIA
When buyers purchase new homes, they usually choose to do their own landscaping on their new property. The PERT/CPM network shown in the figure below represents a landscaping project for a new home in Cape Breton, Nova Scotia. The time is measured in days.
A C G
10 4 10
B D F
7 9 3
E H
11 8
a. What are the expected completion time and the critical path for the landscaping project in Cape Breton, N.S.?
b. What are the earliest and latest start and finish times for activity C?
c. How long can activity A be delayed without delaying the minimum completion time of the project?
d. If activities A, C, and F are each delayed three days, how long will the landscaping project be delayed?
Q4. The Ringtones, a gospel rock group that had many record hits in the 1970s, has evolved and changed many musicians over the past decades. A group however is still singing and performing at music concerts under that name and is now touring Canada. The Ringtones would like to stop in Montreal, Quebec on its current tour schedule. A local music station, PQSR, is in charge of making the arrangements to bring the group to Montreal.
The station has listed the following activities that must be completed prior to the music concert in the table that follows. Time is measured in weeks.
Activity
Immediate Predecessors
Optimistic
Most Likely
Pessimistic
A.
Negotiate lease/contract terms
--
3
4
5
B.
Hire opening music acts
A
2
4
12
C.
Hire concert security
B
1
2
3
D.
Hire sound technicians
B
1
2
10
E.
Ticket sales outlets
B
2
3
4
F
Initial sales promotion
E
2
3
4
G.
Hotel/travel arrangements
A
.5
1
1.5
H.
Final sales promotion
F
4
5
12
I.
Concert rehearsals
C, D, G
2
5
8
J.
Concession sales
A
5
13
15
a. Give an estimated completion time for this project. Show calculations.
b. Which activities have the most slack?
c. What is the probability that the project will be completed in 12 weeks?
d. Give a time in which the station can be 99% sure of completing the project.
OPTIONAL BONUS PART OF QUESTION 4. (5 points)
A more experienced promotion manager promises completion of the project in about 16 days with the following task timings:
Activity
Immediate Predecessors
Optimistic
Most Likely
Pessimistic
A.
Negotiate lease/contract terms
--
1
2
3
B.
Hire opening music acts
A
2
4
6
C.
Hire concert security
B
1
2
3
D.
Hire sound technicians
B
2
3
6
E.
Ticket sales outlets
B
1
2
3
F
Initial sales promotion
E
2
3
4
G.
Hotel/travel arrangements
A
0.5
1
1.5
H.
Final sales promotion
F
4
5
7
I.
Concert rehearsals
C, D, G
2
5
8
J.
Concession sales
A
5
10
12
e. On another model, show whether this is possible or not.
Q5. West End Honda Acura Motors offers their customers in good standing a choice of several lease financing plans. The plan that will be the least expensive for a customer is contingent on the anticipated number of kilometers driven over the first twelve (12) months of the lease. Meghan O’Connor is trying to decide which lease plan to choose, and she therefore wishes to forecast the number of miles she will drive the car over the lease term. She has reviewed her mileage records over the past two years and found that her monthly kilometers is as follows (year 2 in the most recent year.)
Year
1
2
January
1051
1398
February
1145
1366
March
1076
1425
April
1344
1286
May
1276
1342
June
1286
1451
July
1157
1486
August
1398
1385
September
1339
1468
October
1291
1536
November
1365
1422
December
1528
1449
a. Graph this time series.
b. Verify statistically that this time series exhibits linear trend.
c. Using a linear regression approach, forecast Meghan’s kilometers over a one-year lease period.
Q6. (15 points) Below is a record of the number of individuals signed up by the Canadian Navy recruiting office in the Bloor West Village area of Toronto, Ontario.
January
8
February
12
March
15
April
11
May
17
June
16
July
21
August
7
September
13
October
16
November
12
December
11
a. Using Excel's linear regression, forecast the expected total recruits for the next two months and generate Excel's summary output.
b. Briefly explain each of the regression statistics from the summary output.
c. Demonstrate whether rounding forecast (up or down) will improve the regression forecast.
OPTIONAL BONUS PART OF QUESTION 6. (5 points)
d. Demonstrate a better forecasting method for the recruiting office.
Q7. North West Miramichi Contractors Inc. has hired you as a summer student who is majoring in management sciences during her BBA degree program. Going into your third year of study in September, you are excited to be getting relevant work experience in 2021. The following table provides the information necessary for you to construct a project network and project crash data.
Activity
Activity
Time (weeks)
Cost ($)
Activity
Activity Predecessor
Normal
Crash
Normal
Crash
1
-
20
8
1000
1480
2
-
24
20
1200
1400
3
-
14
7
700
1190
4
1
10
6
500
820
5
3
11
5
550
730
Your initial assignment is to begin a project management with the following tasks.
a. Construct a project model.
b. What is the normal project completion time and cost?
c. Construct a linear programming model to determine which activities should be crashed to achieve the following deadlines and determine the total project cost including crash costs.
Project completion time (day)
Crash A?
Crash B?
Crash C?
Crash D?
Crash E?
Total project cost
20
24
25
26
Q8. Allan Gallant of Moncton Muffins has noticed a pattern in his sales (thousands of dollars) and wishes to use seasonal forecasting to improve his stock forecasts and reduce extra stock. A summary of his monthly sales is given below:
Season
2017
2018
2019
2020
Spring
48.6
49.5
54.7
57.0
Summer
54.2
56.0
59.9
63.9
Fall
59.8
63.5
65.0
68.6
Winter
79.8
85.5
89.0
94.2
a. He graphs the data to find the pattern. He sees a definite repeating pattern in his sales.
b. Calculate the period factors.
c. When you are using seasonal factors, how do you de-seasonalize the sales data, and how do you seasonalize the forecast?
Q9. Using Moncton Muffins’ data from question 8. Which exponential smoothing factor (0.1,0.3, 0.5, 0.7 or 0.9) with an initial forecast of 48 provides the best sales forecast according to its MAP and MSE?
Q10. Using Moncton Muffins’ data from question 8, use multivariate regression to forecast sales. Give the MAP and MSE for your forecast.
Algoma University school of Business and Economics
Dr. Gerry Mahar
ADMN 2607: Management Science — Test Three
There are 50 points in the test that is worth 25% of your term grade. Select any combination of questions for the 50-point total. Show Excel models for full credit.
Q1. Operations manager, Margaret O’Meagher, in Miramichi, N.B. is interested in determining an optimal inventory policy at her Miramichi Valley Restaurant Ltd. (MVRL) for orders of frozen french fries from McCain Foods in Florenceville, N.B. The number of pounds of french fries used by MVRL over the past 15 weeks is as follows:
|
Week |
Pounds |
Week |
Pounds |
Week |
Pounds |
|
1 |
16,400 |
6 |
14,800 |
11 |
17,300 |
|
2 |
15,500 |
7 |
16,100 |
12 |
16,800 |
|
3 |
18,200 |
8 |
15,600 |
13 |
17,200 |
|
4 |
17,400 |
9 |
16,900 |
14 |
15,700 |
|
5 |
16,100 |
10 |
17,700 |
15 |
16,200 |
a. Graph this time series.
b. Verify statistically that the time series is stationary.
c. Using a three-week moving average, calculate the forecasted weekly demand for frozen McCain french fries at MVRL for weeks 16 through 20.
d. Using a four-week moving average, calculate the forecasted demand for McCain frozen french fries at the MVRL for weeks16 through 20.
e. Which moving average method gives the lower mean squared forecast error?
Suppose exponential smoothing is used to forecast weekly sales demand for McCain french fries.
f. What is the forecast of weekly french fry sales demand for weeks 16 through 20 using a smoothing constant at α =.10?
g. What is the forecast of the weekly french fry demand for weeks 16 through 20 using a smoothing constant of α =.20?
h. Which of the two smoothing constants gives the lower mean squared forecast error?
I. Which of the two smoothing constants gives the lower mean absolute value of the forecasting error?
j. Which of the two smoothing constants gives the lower mean absolute percent of the forecast error?
k. Which of the two smoothing constants gives the lower largest deviation of the forecast error?
OPTIONAL BONUS PART OF QUESTION 1. (5 points)
Suppose exponential smoothing is used to forecast weekly sales demand for McCain french fries for weeks 16 through 20. Use Solver to:
l. Determine the value of a that minimizes the mean squared forecast error.
m. Determine the value of a that minimizes the mean absolute value of the forecast error.
n. Determine the value of a that minimizes the mean.
Q2. Grand River Construction Company (GRCC) located in Brantford, Ontario has a construction budget to build a new clubhouse for the Brantford Harlequins Rugby Club. GRCC uses both union and non-union workers. Levelling and grading of land, electrical wiring and plumbing work is contracted out to union workers. The actual construction of the project, though supervised by union carpenters, is performed by mainly non-union laborers (including volunteers) to reduce costs. Project planning has produced the following chart.
|
|
|
Immediate Predecessors |
Expected Completion Time (Days) |
|
A |
Grade land |
- |
3 |
|
B |
Order building supplies |
- |
4 |
|
C |
Hire non-union labor |
- |
7 |
|
D |
Pour concrete slab |
A |
8 |
|
E |
Receive/organize supplies |
B |
8 |
|
F |
Build clubhouse frame |
C, D, E |
30 |
|
G |
Electrical installation |
F |
15 |
|
H |
Plumbing installation |
F |
11 |
|
I |
Stucco/paint exterior |
H |
20 |
|
J |
Dry wall/paint interior |
G, H |
18 |
|
K |
Finish/cleanup |
I, J |
6 |
|
L |
Landscaping |
K |
8 |
a. Draw a PERT/CPM network for this problem.
b. Prepare a chart showing the earliest/latest start and finish times and the slack for each activity. What is the expected completion time for the rugby club project?
c. What is the impact of a delay of 1 day in the pouring of the concrete slab?
d. On Day 42, what is the priority of jobs? If there were some cross -trained electrician- plumbers, what might be a good decision?
e. What is the impact of building the clubhouse 3 days early?
f. What is the priority of job at the start of the project? Why?
g. On Day 58, what is the priority of jobs? Why?
Q3. A HOME LANDSCAPTING OPTION IN CAPE BRETON, NOVA SCOTIA
When buyers purchase new homes, they usually choose to do their own landscaping on their new property. The PERT/CPM network shown in the figure below represents a landscaping project for a new home in Cape Breton, Nova Scotia. The time is measured in days.
A C G
10 4 10
B D F
7 9 3
E H
11 8
a. What are the expected completion time and the critical path for the landscaping project in Cape Breton, N.S.?
b. What are the earliest and latest start and finish times for activity C?
c. How long can activity A be delayed without delaying the minimum completion time of the project?
d. If activities A, C, and F are each delayed three days, how long will the landscaping project be delayed?
Q4. The Ringtones, a gospel rock group that had many record hits in the 1970s, has evolved and changed many musicians over the past decades. A group however is still singing and performing at music concerts under that name and is now touring Canada. The Ringtones would like to stop in Montreal, Quebec on its current tour schedule. A local music station, PQSR, is in charge of making the arrangements to bring the group to Montreal.
The station has listed the following activities that must be completed prior to the music concert in the table that follows. Time is measured in weeks.
|
|
Activity |
Immediate Predecessors |
Optimistic |
Most Likely |
Pessimistic |
|
A. |
Negotiate lease/contract terms |
-- |
3 |
4 |
5 |
|
B. |
Hire opening music acts |
A |
2 |
4 |
12 |
|
C. |
Hire concert security |
B |
1 |
2 |
3 |
|
D. |
Hire sound technicians |
B |
1 |
2 |
10 |
|
E. |
Ticket sales outlets |
B |
2 |
3 |
4 |
|
F |
Initial sales promotion |
E |
2 |
3 |
4 |
|
G. |
Hotel/travel arrangements |
A |
.5 |
1 |
1.5 |
|
H. |
Final sales promotion |
F |
4 |
5 |
12 |
|
I. |
Concert rehearsals |
C, D, G |
2 |
5 |
8 |
|
J. |
Concession sales |
A |
5 |
13 |
15 |
a. Give an estimated completion time for this project. Show calculations.
b. Which activities have the most slack?
c. What is the probability that the project will be completed in 12 weeks?
d. Give a time in which the station can be 99% sure of completing the project.
OPTIONAL BONUS PART OF QUESTION 4. (5 points)
A more experienced promotion manager promises completion of the project in about 16 days with the following task timings:
|
|
Activity |
Immediate Predecessors |
Optimistic |
Most Likely |
Pessimistic |
|
A. |
Negotiate lease/contract terms |
-- |
1 |
2 |
3 |
|
B. |
Hire opening music acts |
A |
2 |
4 |
6 |
|
C. |
Hire concert security |
B |
1 |
2 |
3 |
|
D. |
Hire sound technicians |
B |
2 |
3 |
6 |
|
E. |
Ticket sales outlets |
B |
1 |
2 |
3 |
|
F |
Initial sales promotion |
E |
2 |
3 |
4 |
|
G. |
Hotel/travel arrangements |
A |
0.5 |
1 |
1.5 |
|
H. |
Final sales promotion |
F |
4 |
5 |
7 |
|
I. |
Concert rehearsals |
C, D, G |
2 |
5 |
8 |
|
J. |
Concession sales |
A |
5 |
10 |
12 |
e. On another model, show whether this is possible or not.
Q5. West End Honda Acura Motors offers their customers in good standing a choice of several lease financing plans. The plan that will be the least expensive for a customer is contingent on the anticipated number of kilometers driven over the first twelve (12) months of the lease. Meghan O’Connor is trying to decide which lease plan to choose, and she therefore wishes to forecast the number of miles she will drive the car over the lease term. She has reviewed her mileage records over the past two years and found that her monthly kilometers is as follows (year 2 in the most recent year.)
|
|
Year |
|
|
1 |
2 |
|
|
January |
1051 |
1398 |
|
February |
1145 |
1366 |
|
March |
1076 |
1425 |
|
April |
1344 |
1286 |
|
May |
1276 |
1342 |
|
June |
1286 |
1451 |
|
July |
1157 |
1486 |
|
August |
1398 |
1385 |
|
September |
1339 |
1468 |
|
October |
1291 |
1536 |
|
November |
1365 |
1422 |
|
December |
1528 |
1449 |
a. Graph this time series.
b. Verify statistically that this time series exhibits linear trend.
c. Using a linear regression approach, forecast Meghan’s kilometers over a one-year lease period.
Q6. (15 points) Below is a record of the number of individuals signed up by the Canadian Navy recruiting office in the Bloor West Village area of Toronto, Ontario.
|
January |
8 |
|
February |
12 |
|
March |
15 |
|
April |
11 |
|
May |
17 |
|
June |
16 |
|
July |
21 |
|
August |
7 |
|
September |
13 |
|
October |
16 |
|
November |
12 |
|
December |
11 |
a. Using Excel's linear regression, forecast the expected total recruits for the next two months and generate Excel's summary output.
b. Briefly explain each of the regression statistics from the summary output.
c. Demonstrate whether rounding forecast (up or down) will improve the regression forecast.
OPTIONAL BONUS PART OF QUESTION 6. (5 points)
d. Demonstrate a better forecasting method for the recruiting office.
Q7. North West Miramichi Contractors Inc. has hired you as a summer student who is majoring in management sciences during her BBA degree program. Going into your third year of study in September, you are excited to be getting relevant work experience in 2021. The following table provides the information necessary for you to construct a project network and project crash data.
|
|
|
Activity |
Activity |
||
|
|
|
Time (weeks) |
Cost ($) |
||
|
Activity |
Activity Predecessor |
Normal |
Crash |
Normal |
Crash |
|
1 |
- |
20 |
8 |
1000 |
1480 |
|
2 |
- |
24 |
20 |
1200 |
1400 |
|
3 |
- |
14 |
7 |
700 |
1190 |
|
4 |
1 |
10 |
6 |
500 |
820 |
|
5 |
3 |
11 |
5 |
550 |
730 |
Your initial assignment is to begin a project management with the following tasks.
a. Construct a project model.
b. What is the normal project completion time and cost?
c. Construct a linear programming model to determine which activities should be crashed to achieve the following deadlines and determine the total project cost including crash costs.
|
Project completion time (day) |
Crash A? |
Crash B? |
Crash C? |
Crash D? |
Crash E? |
Total project cost |
|
20 |
|
|
|
|
|
|
|
24 |
|
|
|
|
|
|
|
25 |
|
|
|
|
|
|
|
26 |
|
|
|
|
|
|
Q8. Allan Gallant of Moncton Muffins has noticed a pattern in his sales (thousands of dollars) and wishes to use seasonal forecasting to improve his stock forecasts and reduce extra stock. A summary of his monthly sales is given below:
|
Season |
2017 |
2018 |
2019 |
2020 |
|
Spring |
48.6 |
49.5 |
54.7 |
57.0 |
|
Summer |
54.2 |
56.0 |
59.9 |
63.9 |
|
Fall |
59.8 |
63.5 |
65.0 |
68.6 |
|
Winter |
79.8 |
85.5 |
89.0 |
94.2 |
a. He graphs the data to find the pattern. He sees a definite repeating pattern in his sales.
b. Calculate the period factors.
c. When you are using seasonal factors, how do you de-seasonalize the sales data, and how do you seasonalize the forecast?
Q9. Using Moncton Muffins’ data from question 8. Which exponential smoothing factor (0.1,0.3, 0.5, 0.7 or 0.9) with an initial forecast of 48 provides the best sales forecast according to its MAP and MSE?
Q10. Using Moncton Muffins’ data from question 8, use multivariate regression to forecast sales. Give the MAP and MSE for your forecast.
