Homework assignment #3 Part 1: Scheduling problem
Follow IP part 1 and IP part 2 videos, and solve the scheduling problem. Submit your spreadsheet solution.
Part 2: VBA Macros
Follow the video and submit your spreadsheet solution which includes: 1. The Macro for calculating the final scores.
2. The final scores in the designated column.
Part 2: Timeshare Exchange
Prepare the Timeshare Exchange case by filling in the tables on the Excel spreadsheet I shared with you TimeShare_spreadsheet.xlsx.
1) To fill in tables 1,2 and 3 refer to the customers’ submissions and choices original data (A4:G20). Hint: use the functions “If” and “And”.
2) To fill in tables 4 and 5 refer to the color code table (A23:E26). Hint: use the functions “Index” and “Match”.
Analyze the Timeshare Exchange case considering the following assumptions:
a) All exchanges are allowed – no matter what color was submitted by the customer.
b) There is no difference between top choice and second choice. Assume that both are
equally desirable to a customers who requested them. Hint: use the function “Max”.
c) Your objective is to maximize the number of exchanges.
d) Keep in mind that a customer can receive a particular resort/season combination only if
someone else gave the same combination up. For example, if customers 1 and 2 are both awarded Orlando in the winter, there have to be two other customers who (a) submitted Orlando in the winter, and (b) received something else in the exchange. Hint: use the function “Sumproduct” to calculate which resort combinations were given up.
e) It is recommended to use a separate sheet for your calculations and model setting.
f) Set your model in Risk Solver and optimize. How many matches have you received?
g) Prepare a table with your final solution (Owner, Awarded resort, Awarded Season). Hint:
use the functions “If”, “Index” and “Match”.
h) Submit your spreadsheet solution.
For reference, see in the box a valid final solution (there are multiple solutions to this problem).
Owner Resort Season
100
2 Horseshoe Summer 3 Orlando Winter 400
5 San Diego Fall
6 San Diego Winter
700
8
9
10
11
12
13
14
15
Orlando Winter
Orlando Summer Horseshoe Summer
San Diego Spring Horseshoe Winter
0 0 Horseshoe Summer
Orlando Fall