Assignment2 Fall 2013
ITM 501
Fall 2013
Assignment 2
Notes regarding submission:
1. The due date is October 30, Wednesday (23:59).
2. The assignment must be on a MS Excel file which must be submitted through Blackboard.
Please click the assignment title (not the assignment file) to upload your file.
3. You must create three worksheets for each problem:
a. LP model as a mathematical statement (see example below),
b. Spreadsheet model (that is, an Excel Solver model),
c. Answer report (Solver solution).
4. There are five problems in total (A.1, A.2, A.3, B, and C). Therefore, your MS Excel file must contain fifteen worksheets.
5. The file should be named according to the following convention:
<first name>_<last name>#2.xlsx, e.g. selcuk_savas#2.xlsx)
1
Part A.1
ABC is now considering adding three more lines (in addition to their already existing CRM line) to their offering: data warehousing (DWH), data backup solutions (DBU), and decision support systems (DSS). The VP of operations that will be overseeing these three lines has a maximum annual budget of $ 2,000,000 for these three new lines. They have not decided on the pricing for these services yet, but experience and some small-scale market research suggests that ABC will make roughly $0.9 profit per year for each dollar they spend (which includes personnel, bandwidth, hardware and software and other overhead costs) on DWH services. These figures are $0.9 for DBU and $0.95 for DSS.
The company has bandwidth capacity of 5 GBps (5,000,000 KBps) that they can dedicate to these new lines. The expectation is that every dollar spent on the DWH services will lead to transactions that require a dedicated bandwidth of 2 KBps. These figures are 2.2 KBps for DBU and 1.8 KBps for DSS services.
In addition to bandwidth, every $ spent on DWH will require 22 minutes of the machine hours available (per year). These figures are 20 minutes for both DBU and DSS services. ABC has a total of 50 dedicated computers that run around the clock to provide
Fall 2013
Assignment 2
Notes regarding submission:
1. The due date is October 30, Wednesday (23:59).
2. The assignment must be on a MS Excel file which must be submitted through Blackboard.
Please click the assignment title (not the assignment file) to upload your file.
3. You must create three worksheets for each problem:
a. LP model as a mathematical statement (see example below),
b. Spreadsheet model (that is, an Excel Solver model),
c. Answer report (Solver solution).
4. There are five problems in total (A.1, A.2, A.3, B, and C). Therefore, your MS Excel file must contain fifteen worksheets.
5. The file should be named according to the following convention:
<first name>_<last name>#2.xlsx, e.g. selcuk_savas#2.xlsx)
1
Part A.1
ABC is now considering adding three more lines (in addition to their already existing CRM line) to their offering: data warehousing (DWH), data backup solutions (DBU), and decision support systems (DSS). The VP of operations that will be overseeing these three lines has a maximum annual budget of $ 2,000,000 for these three new lines. They have not decided on the pricing for these services yet, but experience and some small-scale market research suggests that ABC will make roughly $0.9 profit per year for each dollar they spend (which includes personnel, bandwidth, hardware and software and other overhead costs) on DWH services. These figures are $0.9 for DBU and $0.95 for DSS.
The company has bandwidth capacity of 5 GBps (5,000,000 KBps) that they can dedicate to these new lines. The expectation is that every dollar spent on the DWH services will lead to transactions that require a dedicated bandwidth of 2 KBps. These figures are 2.2 KBps for DBU and 1.8 KBps for DSS services.
In addition to bandwidth, every $ spent on DWH will require 22 minutes of the machine hours available (per year). These figures are 20 minutes for both DBU and DSS services. ABC has a total of 50 dedicated computers that run around the clock to provide