Reference no: EM132730072
Assessment - Simulation
PART I
In this part of the assessment task, you are required to solve TWO out of THREE problems:
1. The following data shows the quarterly profit (in thousands of dollars) made by ABC company in the past 3 years.
|
Year
|
Quarter
|
Profit ($1000s)
|
|
1
|
1
|
55
|
|
1
|
2
|
61
|
|
1
|
3
|
82
|
|
1
|
4
|
80
|
|
2
|
1
|
59
|
|
2
|
2
|
55
|
|
2
|
3
|
89
|
|
2
|
4
|
64
|
|
3
|
1
|
52
|
|
3
|
2
|
68
|
|
3
|
3
|
80
|
|
3
|
4
|
56
|
a. Use a = 0.2 to compute the exponential smoothing values for the time series. Compute Mean Squared Error (MSE) and the forecast of profit (in $1000s) for the next quarter.
b. Compare the three-period moving average forecast with the exponential smoothing forecast using a = 0.2.
c. Which appears to provide the better forecast based on MSE?
2. Consider the following time series data:
|
t
|
yr
|
|
1
|
0.445
|
|
2
|
0.466
|
|
3
|
0.498
|
|
4
|
0.456
|
|
5
|
0.556
|
|
6
|
0.578
|
|
7
|
0.643
|
|
8
|
0.696
|
|
9
|
0.734
|
|
10
|
0.798
|
a. Construct a time series plot. What type of pattern exists in the data?
b. Use simple linear regression analysis to find the parameters for the line that minimizes MSE for this time series.
c. What is the forecast for t = 11?
3. Consider the following quarterly time series:
|
Quarter
|
Year 1
|
Year 2
|
Year 3
|
|
1
|
1023
|
1212
|
1343
|
|
2
|
1156
|
1256
|
1401
|
|
3
|
1224
|
1224
|
1354
|
|
4
|
1092
|
1178
|
1398
|
a. Construct a time series plot. What type of pattern exists in the data?
b. Use a multiple regression model with dummy variables as follows to develop an equation to account for seasonal effects in the data. Qtr1 = 1 if quarter 1, 0 otherwise; Qtr2 = 1 if quarter 2, 0 otherwise; Qtr3 = 1 if quarter 3, 0 otherwise.
c. Compute the quarterly forecasts for next year based on the model developed in part b.
PART II
In this part of the assessment task, you choose THREE of the following FOUR problems and formulate the linear programming model for each problem and develop a spreadsheet model and find the optimal solution using Excel Solver. Your report must include the sensitivity analysis to show how sensitive the solutions are to (small) changes in the data. In this analysis, you are required to explain the following issues using proper examples:
1. If the objective function changes, how does the solution change?
2. If resources available change, how does the solution change?
3. If a constraint is added to the problem, how does the solution change?
1. Diet problem
Suppose your diet requires that all the food you eat come from one of the four "basic food groups" (chocolate cake, ice cream, soda, and cheesecake). At present, the following four foods are available for consumption: brownies, chocolate ice cream, cola, and pineapple cheesecake. Each brownie costs 50 cents, each scoop of chocolate ice cream costs 20 cents, each bottle of cola costs 30 cents, and each piece of pineapple cheesecake costs 80 cents. Each day, you must ingest at least 500 calories, 6 oz of chocolate, 10 oz of sugar, and 8 oz of fat. The nutritional content per unit of each food is shown in the following table.
a. What is the linear programming model for this problem?
b. Develop a spreadsheet model and find the optimal solution using Excel Solver that can be used to satisfy your daily nutritional requirements at minimum costs.
Table I: Diet problem
|
|
Calories
|
Chocolate
|
Sugar
|
Fat
|
|
Brownie
|
500
|
3 ounces
|
2 ounce
|
2 ounce
|
|
Chocolate ice cream (1 scoop)
|
300
|
2
|
2
|
4
|
|
Cola (1 bottle)
|
140
|
0
|
4
|
1
|
|
Pineapple cheesecake (1piece)
|
450
|
0
|
4
|
5
|
Your task is to identify the variables, write down the objective function and constraints using the information given in the table. Finally, find out the optimal solutions using the Solver in Ms Excel.
2. Portfolio Selection
APIC invests in short-term trade credits, corporate bonds, gold stocks, and construction loans. To encourage a diversified portfolio, the board of directors has placed limits on the amount that can be committed to any one type of investment. APIC has $5 million available for immediate investment and wishes to do two things:
a. Maximize the interest earned on the investments made over the next six months, and
b. Satisfy the diversification requirements as set by the board of directors. The specifics of the investment possibilities are:
|
Investment
|
Interest earned %
|
Maximum investment
($ Million)
|
|
Trade credit
|
8
|
1.5
|
|
Corporate bonds
|
10
|
3.5
|
|
Gold stocks
|
18
|
2.5
|
|
Construction loans
|
16
|
2.8
|
In addition, the board specifies that at least 55% of the funds invested must be in gold stocks and construction loans, and that no less than 15% be invested in trade credit.
a. Develop the linear programming model for this problem?
b. Develop a spreadsheet model and find the optimal solution using Excel Solver.
3. Work Scheduling Problem
Sony has a 24-hour-a-day, 7-days-a-week toll free hotline that is being set up to answer questions regarding a new product. The following table summarizes the number of full-time equivalent employees (FTEs) that must be on duty in each time block.
|
2
|
4-8
|
11
|
|
3
|
8-12
|
41
|
|
4
|
12-16
|
71
|
|
5
|
16-20
|
41
|
|
6
|
20-0
|
36
|
• Sony may hire both full-time and part-time employees. The former work 8-hour shifts and the latter work 4-hour shifts; their respective hourly wages are $17.20 and $14.95. Employees may start work only at the beginning of one of 6 shifts.
• At least two-thirds of the employees working at any one time must be full-time employees.
• Part-time employees can only answer 5 calls in the time a full-time employee can answer 6 calls. (i.e., a part-time employee is only 5/6 of a full-time employee.)
a. Define the variables, formulate an LP to determine how to staff the hotline at minimum cost and finally, find out the solution using Ms Excel.
b. Develop a spreadsheet model and find the optimal solution using Excel Solver.
4. Transportation Problem
The Top Speed Bicycle Co. manufactures and markets a line of 10-speed bicycles nationwide. The firm has final assembly plants in two cities in which labor costs are low, New Orleans and Omaha. Its three major warehouses are located near the larger market areas of New York, Chicago, and Los Angeles. The sales requirements for next year at the New York warehouse are 10000 bicycles, at the Chicago warehouse 8000 bicycles, and at the Los Angeles warehouse 15000 bicycles. The factory capacity at each location is limited. New Orleans can assemble and ship 20000 bicycles; the Omaha plant can produce 15000 bicycles per year. The cost of shipping one bicycle from each factory to each warehouse differs, and these unit shipping costs are:
Table V: Transportation Problem
|
|
New York
|
Chicago
|
Los Angeles
|
|
New Orleans
|
$2
|
3
|
5
|
|
Omaha
|
3
|
1
|
4
|
The company wishes to develop a shipping schedule that will minimize its total annual transportation cost.
a. Develop the linear programming model for this problem?
b. Develop a spreadsheet model and find the optimal solution using Excel Solver.