CITM 501 Spring/Summer 2014

Homework Assignment 5

Due Month #, 2014 @5 PM via Bb

Refer to the models at the end of this document to answer the following questions.

Solve model A adding answer and sensitivity analysis reports to your output. Answer the following questions based on the sensitivity analysis. Do not re?solve the model. Clearly indicate the part of the output each answer is based on.

1. If ABC found out that they underestimated their return on investment on the CRM offering, what would be the impact of this on their estimated profit, i.e. how much increase in the ROI from the current level of 0.4 would require you to re?solve the model and change your recommendation?

1. If the ROI for Data warehousing services went up to 0.5, how much of an increase in profit would that imply?

1. If the VP thinks that he can increase the computer capacity by acquiring 10 more computers for $72,000 (total, i.e. for all computers) annually, should he go ahead and spend the money on these 10 computers? Why? (Note: this money would not be part of the existing operating budget)

Solve model B adding answer and sensitivity analysis reports to your output. Answer the following questions based on the sensitivity analysis. Do not re?solve the model. Clearly indicate the part of the output each answer is based on.

1. If the profit requirement was reduced by 10% (from $500,000), how much of their spending can they decrease?

1. If they now decided that $500,000 is low for profit, how much should they increase their spending to make 5% more profit (than $500,000)?

Submit one spreadsheet file (with 7 tabs: the model, the answer report, and the sensitivity report for each part, and your analysis) beginning with your first initial ending with #5 as the file name (i.e. <first initial>_<lastname>#5.xlsx, e.g. j_smith#5.xlsx)

 

Model A

Decision Variables

CRM: annual amount ($) spent on CRM services

DWH: annual amount ($) spent on DWH services

DB: annual amount ($) spent on DB services

Objective Function

Maximize 0.4*CRM + 0.35*DWH + 0.4*DB

Constraints:
CRM + DWH + DB	<=	1.9M (annual budget in $)
1.8*CRM + 1.3*DWH + 1.9*DB	<=	5M (monthly bandwidth in MB)
3.6*CRM + 2.6*DWH + 3.8*DB	<=	4320000 (monthly M/C capacity in minutes)
CRM	<=	0.4*(CRM + DWH + DB) (diversification)
DWH	<=	0.4*( CRM + DWH + DB) (diversification)
DB	<=	0.4*( CRM + DWH + DB) (diversification)
CRM, DWH, DB	>=	0 (nonnegativity)
Model B
Decision Variables

CRM: annual amount ($) spent on CRM services

DWH: annual amount ($) spent on DWH services

DB: annual amount ($) spent on DB services

Objective Function
Minimize CRM + DWH + DB
Constraints:
0.4*CRM + 0.35*DWH + 0.4*DB	>=	500,000 (minimum ROI)
1.8*CRM + 1.3*DWH + 1.9*DB	<=	5M (monthly bandwidth in MB)
3.6*CRM + 2.6*DWH + 3.8*DB	<=	4320000 (monthly M/C capacity in minutes)
CRM	<=	0.4*(CRM + DWH + DB) (diversification)
DWH	<=	0.4*( CRM + DWH + DB) (diversification)
DB	<=	0.4*( CRM + DWH + DB) (diversification)
CRM, DWH, DB	>=	0 (nonnegativity)

CITM 501 Spring/Summer 2014

Homework Assignment 5

Due Month #, 2014 @5 PM via Bb

Refer to the models at the end of this document to answer the following questions.

Solve model A adding answer and sensitivity analysis reports to your output. Answer the following questions based on the sensitivity analysis. Do not re?solve the model. Clearly indicate the part of the output each answer is based on.

1. If ABC found out that they underestimated their return on investment on the CRM offering, what would be the impact of this on their estimated profit, i.e. how much increase in the ROI from the current level of 0.4 would require you to re?solve the model and change your recommendation?

1. If the ROI for Data warehousing services went up to 0.5, how much of an increase in profit would that imply?

1. If the VP thinks that he can increase the computer capacity by acquiring 10 more computers for $72,000 (total, i.e. for all computers) annually, should he go ahead and spend the money on these 10 computers? Why? (Note: this money would not be part of the existing operating budget)

Solve model B adding answer and sensitivity analysis reports to your output. Answer the following questions based on the sensitivity analysis. Do not re?solve the model. Clearly indicate the part of the output each answer is based on.

1. If the profit requirement was reduced by 10% (from $500,000), how much of their spending can they decrease?

1. If they now decided that $500,000 is low for profit, how much should they increase their spending to make 5% more profit (than $500,000)?

Submit one spreadsheet file (with 7 tabs: the model, the answer report, and the sensitivity report for each part, and your analysis) beginning with your first initial ending with #5 as the file name (i.e. <first initial>_<lastname>#5.xlsx, e.g. j_smith#5.xlsx)

 

Model A

Decision Variables

CRM: annual amount ($) spent on CRM services

DWH: annual amount ($) spent on DWH services

DB: annual amount ($) spent on DB services

Objective Function

Maximize 0.4*CRM + 0.35*DWH + 0.4*DB

Constraints:
CRM + DWH + DB	<=	1.9M (annual budget in $)
1.8*CRM + 1.3*DWH + 1.9*DB	<=	5M (monthly bandwidth in MB)
3.6*CRM + 2.6*DWH + 3.8*DB	<=	4320000 (monthly M/C capacity in minutes)
CRM	<=	0.4*(CRM + DWH + DB) (diversification)
DWH	<=	0.4*( CRM + DWH + DB) (diversification)
DB	<=	0.4*( CRM + DWH + DB) (diversification)
CRM, DWH, DB	>=	0 (nonnegativity)
Model B
Decision Variables

CRM: annual amount ($) spent on CRM services

DWH: annual amount ($) spent on DWH services

DB: annual amount ($) spent on DB services

Objective Function
Minimize CRM + DWH + DB
Constraints:
0.4*CRM + 0.35*DWH + 0.4*DB	>=	500,000 (minimum ROI)
1.8*CRM + 1.3*DWH + 1.9*DB	<=	5M (monthly bandwidth in MB)
3.6*CRM + 2.6*DWH + 3.8*DB	<=	4320000 (monthly M/C capacity in minutes)
CRM	<=	0.4*(CRM + DWH + DB) (diversification)
DWH	<=	0.4*( CRM + DWH + DB) (diversification)
DB	<=	0.4*( CRM + DWH + DB) (diversification)
CRM, DWH, DB	>=	0 (nonnegativity)