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. ForQuestions3and4usetheinformationsummarizedintheSolverssensitivityreportasmuchasyoucantoanswerthesensitivityanalysisquestions.

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.

• Identify verbally the decisions to be made, the constraints on these decisions, and the overall measure of performance for the decisions.
• 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.
• 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.
• Hint:DefineyourdecisionvariablesasX_ij=amountofspaceleasedinmonthiforaperiodofjmonthsfori=1,,5andj=1,,6i;forexample,X₂₄=amountofspaceleasedinmonth2foraperiodof4months.Thisproblemhas15variables.

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 #4

Round Tree Manor is a hotel that provides two types of rooms with three rental classes: Super Saver, Deluxe, and Business. The profit per night for each type of room and rental class is as follows:

Room	Super Saver	Deluxe	Business
Type I	$30	$35	--
Type II	$20	$30	$40

Type I rooms do not have high-speed Internet access and are not available for the Business rental class.

Round Trees management makes a forecast of the demand by rental class for each night in the future. A linear programming model developed to maximize profit is used to determine how many reservations to accept for each rental class. The demand forecast for a particular night is 130 rentals in the Super Saver class, 60 rentals in the Deluxe class, and 50 rentals in the Business class. Round Tree has 100 Type I rooms and 120 Type II rooms.

1. Uselinearprogrammingtodeterminehowmanyreservationstoacceptineachrentalclassandhowthereservationsshouldbeallocatedtoroomtypes.Isthedemandbyanyrentalclassnotsatisfied?Explain.
2. Howmanyreservationscanbeaccommodatedineachrentalclass?
3. ManagementisconsideringofferingafreebreakfasttoanyoneupgradingfromaSuperSaverreservationtoDeluxeclass.IfthecostofthebreakfasttoRoundTreeis$5,shouldthisincentivebeoffered?
4. Withalittlework,anunusedofficeareacouldbeconvertedtoarentalroom.Iftheconversioncostisthesameforbothtypesofrooms,wouldyourecommendconvertingtheofficetoaTypeIoraTypeIIroom?Why?
5. Couldthelinearprogrammingmodelbemodifiedtoplanfortheallocationofrentaldemandforthenextnight?Whatinformationwouldbeneededandhowwouldthemodelchange?

Question #3

Case Problem 1 PRODUCT MIX

TJ's, Inc., makes three nut mixes for sale to grocery chains located in the Southeast. The three mixes, referred to as the Regular Mix, the Deluxe Mix, and the Holiday Mix, are made by mixing different percentages of five types of nuts.

In preparation for the fall season, TJ's has just purchased the following shipments of nuts at the prices shown:

Type of Nut	Shipment Amount (pounds)	Cost per Shipment ($)
Almond	6000	7500
Brazil	7500	7125
Filbert	7500	6750
Pecan	6000	7200
Walnut	7500	7875

The Regular Mix consists of 15% almonds, 25% Brazil nuts, 25% filberts, 10% pecans, and 25% walnuts. The Deluxe Mix consists of 20% of each type of nut, and the Holiday Mix consists of 25% almonds, 15% Brazil nuts, 15% filberts, 25% pecans, and 20% walnuts.

An accountant at TJ, Inc. analyzed the cost of packaging materials, sales price per pound, and so forth, and determined that the profit contribution per pound is $1.65 for the Regular Mix, $2.00 for the Deluxe Mix, and $2.25 for the Holiday Mix. These figures do not include the cost of specific types of nuts in the different mixes because that cost can vary greatly in the commodity markets.

Customer orders already received are summarized here:

Type of Mix	Orders (pounds)
Regular	10000
Deluxe	3000
Holiday	5000

Because demand is running high, it is expected that TJ, Inc. will receive many more orders than can be satisfied.

TJ, Inc. is committed to using the available nuts to maximize profit over the fall season; nuts not used will be given to a local charity. Even if it is not profitable to do so, TJ, Inc.'s president indicated that the orders already received must be satisfied.

Managerial Report

Perform an analysis of TJ, Inc.'s product-mix problem, and prepare a report for TJ's president that summarizes your findings. Be sure to include information and analysis on the following:

1. The cost per pound of the nuts included in the Regular, Deluxe, and Holiday mixes

2. The optimal product mix and the total profit contribution

3. Recommendations regarding how the total profit contribution can be increased if additional quantities of nuts can be purchased

4. A recommendation as to whether TJ, Inc. should purchase an additional 1000 pounds of almonds for $1000 from a supplier who overbought

5. Recommendations on how profit contribution could be increased (if at all) if TJ, Inc. does not satisty all existing orders