Instructions:

1. For each question, your mathematical model and answers to each individual part should be typed in Microsoft Word. Also, for each question, state your model by clearly defining your decision variables (with appropriate units), the objective function (min or max), and all the relevant (including the nonnegativity) constraints.
2. Excel spreadsheet models are required for each question. You can submit a workbook containing all the separate spreadsheets. Make sure that each spreadsheet carries the name of the question you are answering.
3. For how to use the Solver for solving linear programming models and produce the Solver’s sensitivity report, refer to Appendix 2.2 page 89 and Appendix 3.1 page 149 in your textbook 14e, respectively.
4. For Questions 3 and 4 use the information summarized in the Solver’s sensitivity report as much as you can to answer the sensitivity analysis questions.

Question #1:

Web Mercantile sells many household products through an on-line catalog. The company needs substantial warehouse space for storing its goods. Plans now are being made for leasing warehouse storage space over the next five months. Just how much space will be required in each of these months is known. However, since these space requirements are quite different, it may be most economical to lease only the amount needed each month on a month-by-month basis. On the other hand, the additional cost for leasing space for additional months is much less than for the first month, so it may be less expensive to lease the maximum amount needed for the entire five months. Another option is the intermediate approach of changing the total amount of space leased (by adding a new lease and/or having an old lease expire) at least once but not every month.
The space requirement and the leasing costs for the various leasing periods are as follows:

Month
Required Space Leasing Period
(months) Cost per Sq. Ft.
Leased

1 30,000 sq. ft. 1 $65
2 20,000 sq. ft. 2 $100
3 40,000 sq. ft. 3 $135
4 10,000 sq. ft. 4 $160
5 50,000 sq. ft. 5 $190
The objective is to minimize the total leasing cost for meeting the space requirements.

a) Identify verbally the decisions to be made, the constraints on these decisions, and the overall measure of performance for the decisions.

b) Convert these verbal descriptions of the constraints and measure of performance into quantitative expressions in terms of the data and decisions. Summarize the model in algebraic form by stating the decision variables, the objective function and constraints.

c) Formulate a spreadsheet model for this problem. Identify the data cells, the changing cells, the target cell, and the other output cells. Please use the SUMPRODUCT function if possible. Use the Excel Solver to solve the model.

d) Hint: Define your decision variables as Xij = amount of space leased in month i for a period of j months for i = 1, …, 5 and j = 1, …, 6 – i; for example, X24 = amount of space leased in month 2 for a period of 4 months. This problem has 15 variables.
Question #2:

Larry Edison is the Director of the Computer Center for Buckly College. He now needs to schedule the staffing of the center. It is open from 8 AM until midnight. Larry has monitored the usage of the center at various times of the day and determined that the following number of computer consultants are required:
Time of Day Minimum Number of Consultants Required to be on Duty

8 AM -noon 4
Noon – 4 PM 8
4 PM – 8 PM 10
8 PM – Midnight 6

Two types of computer consultants can be hired: full-time and part-time. The full-time consultants work for eight consecutive hours in any of the following shifts: morning (8 AM – 4 PM), and evening (4 PM – Midnight). Full-time consultants are paid $14.00 per hour.
Part-time consultants can be hired to work any of the four shifts listed in the table. Part-time consultants are paid $12.00 per hour.
An additional requirement is that during every time period, there must be at least two full-time consultants on duty for every part-time consultant on duty.
Larry would like to determine how many full-time and part-time consultants should work each shift to meet the above requirements at the minimum possible cost.
a) Formulate a linear programming model for this problem on a spreadsheet. Use the Solver to solve this model.

b) Summarize the model in algebraic form by defining the decision variables, the objective function and all the constraints.
Question #3:

Answer questions 1 to 5 under managerial report for Case Problem: PRODUCT MIX given on page 146-147

Question #4: Problem 21 page 139

Instructions
Web Application Development
The purpose of this project is to demonstrate your ability to apply some of the skills that you have learned by creating a real-world web design project using various tools and methodologies, including Visual Basic .NET controls.
Website Design
Create an ASP.NET web application to be used as a loan calculator. The textboxes used to input data (loan amount, repayment period in months, and interest rate) should be checked for valid entries. A button should be used to call a payment function that calculates the monthly payment and displays the result in a label. An additional button should be used to reset or clear all the textboxes and the label. Create a link for “Help” that displays instructions for using this loan calculator. The data in each box must be appropriately formatted (e.g., $50,000 or $2,345). The layout should be clear, easy to read, and logical in sequence. Use colors to enhance the presentation. This application must be accessed and function through a web browser. WRITER MUST DOCUMENT THE DESIGN. WRITER MUST UPLOAD THE COMPLETED ORDER IN MICROSOFT WORD AND ZIP FILE.

WRITER MUST USE THE RUBRIC LEVEL 5 BELOW TO MAKE SURE THIS PROJECT HAS ALL THE REQUIREMENTS.
Rubric Name:

Criteria    Level 5
5 points    Level 4
4 points    Level 3
3 points    Level 2
2 points    Level 1
1 point
Program meets requirements outlined in the assignment and runs without errors. It doesn’t crash or produce unexpected results.    Requirements were followed and all expectations were met. The program complies and funs and contains all of the requested features.    Most of the requirements were met but the program fell short of meeting all requirements and expectations. Some parts of the program needed additional functionality to completely meet expectations of the assignment description.    Significant amount of the requiremetns were missing from the implementation.    The program did not compile but it could have if some of the errors were corrected.    The program did not compile or run. The source code was very incomplete and non-functioning.
User interface is visually appealing, designed well for the user and follows current standards.    The user interface is useable and is very easy to follow and understand.    The user interface worked and included all of the necessary components but it was difficult to navigate and it was difficult to find certain components.    The user interface was difficult to navigate or use. Some options were not clear or easy to find.    The user interface was missing significant options and features.    The user interface was not complete or working properly. It was difficult to tell what was implemented and what was missing.
Best use of Visual Basic Language. The code is straightforward, properly indented, use of white space and proper naming conventions are used for variables and objects.    The source code is well designed and does not contain any bugs or issues.    The source code was written in Visual Basic and ASP.NET but some of the functionality was hard to follow. The implementation did not make efficient use of the language constructs or features.    The program was not straight forward. That is, it was hard to find implemented details and the source code logic was confusing.    The source code was incomplete and did not function per requirements.    The code was not created to work with the user interface. Most of the source code implementation was missing.
Code is extensively commented and variables are explicitly defined.    The source code contains a sufficient level of comments and all variables were explicity defined.    There were some areas were comments wree missing. Some variables were not clearly defined or used correctly.    Little to no comments were provided. Not all variables were used correctly.    No comments were provided. Some variables were defined but not used correctly.    No comments were written in the source code. No variables were explicitly defined or used in the program.
Overall Score    Level 5
14 or more    Level 4
11 or more    Level 3
8 or more    Level 2
5 or more    Level 1
0 or more

Instructions
Web Application Development
The purpose of this project is to demonstrate your ability to apply some of the skills that you have learned by creating a real-world web design project using various tools and methodologies, including Visual Basic .NET controls.
Website Design
Create an ASP.NET web application to be used as a loan calculator. The textboxes used to input data (loan amount, repayment period in months, and interest rate) should be checked for valid entries. A button should be used to call a payment function that calculates the monthly payment and displays the result in a label. An additional button should be used to reset or clear all the textboxes and the label. Create a link for “Help” that displays instructions for using this loan calculator. The data in each box must be appropriately formatted (e.g., $50,000 or $2,345). The layout should be clear, easy to read, and logical in sequence. Use colors to enhance the presentation. This application must be accessed and function through a web browser. WRITER MUST DOCUMENT THE DESIGN. WRITER MUST UPLOAD THE COMPLETED ORDER IN MICROSOFT WORD AND ZIP FILE.

WRITER MUST USE THE RUBRIC LEVEL 5 BELOW TO MAKE SURE THIS PROJECT HAS ALL THE REQUIREMENTS.
Rubric Name:

Criteria    Level 5
5 points    Level 4
4 points    Level 3
3 points    Level 2
2 points    Level 1
1 point
Program meets requirements outlined in the assignment and runs without errors. It doesn’t crash or produce unexpected results.    Requirements were followed and all expectations were met. The program complies and funs and contains all of the requested features.    Most of the requirements were met but the program fell short of meeting all requirements and expectations. Some parts of the program needed additional functionality to completely meet expectations of the assignment description.    Significant amount of the requiremetns were missing from the implementation.    The program did not compile but it could have if some of the errors were corrected.    The program did not compile or run. The source code was very incomplete and non-functioning.
User interface is visually appealing, designed well for the user and follows current standards.    The user interface is useable and is very easy to follow and understand.    The user interface worked and included all of the necessary components but it was difficult to navigate and it was difficult to find certain components.    The user interface was difficult to navigate or use. Some options were not clear or easy to find.    The user interface was missing significant options and features.    The user interface was not complete or working properly. It was difficult to tell what was implemented and what was missing.
Best use of Visual Basic Language. The code is straightforward, properly indented, use of white space and proper naming conventions are used for variables and objects.    The source code is well designed and does not contain any bugs or issues.    The source code was written in Visual Basic and ASP.NET but some of the functionality was hard to follow. The implementation did not make efficient use of the language constructs or features.    The program was not straight forward. That is, it was hard to find implemented details and the source code logic was confusing.    The source code was incomplete and did not function per requirements.    The code was not created to work with the user interface. Most of the source code implementation was missing.
Code is extensively commented and variables are explicitly defined.    The source code contains a sufficient level of comments and all variables were explicity defined.    There were some areas were comments wree missing. Some variables were not clearly defined or used correctly.    Little to no comments were provided. Not all variables were used correctly.    No comments were provided. Some variables were defined but not used correctly.    No comments were written in the source code. No variables were explicitly defined or used in the program.
Overall Score    Level 5
14 or more    Level 4
11 or more    Level 3
8 or more    Level 2
5 or more    Level 1
0 or more