Question 1: Carolina Cotton Wonks (CC Wonks) is a small manufacturer of cotton textile in the US. The company's signature textiles are canvas, flannel, and chambray. Canvas is a heavy, durable fabric. It requires 9 pounds of raw cotton per yard. Flannel is a medium-weight cotton fabric and requires 7 pounds of raw cotton per yard. Chambray is light weight and requires 4 pounds of raw cotton per yard. At CC Wonks's facility, a yard of canvas requires 5 hours of processing, a yard of flannel requires 6 hours of processing, and a yard of chambray also requires 6 hours of processing. The maximum monthly demand for canvas, flannel, and chambray are 150, 500, and 1000 yards, respectively. CC Wonks has 6000 pounds of cotton in stock for next month's production. The monthly processing capacity at the facility is 8000 hours. CC Wonks makes a profit of $8 per yard of canvas, $10 per yard of flannel and $9 per yard of chambray.

You have been hired to determine the monthly production plan that maximizes CC Wonks' profits. You will formulate the production planning problem of CC Wonks as an optimization problem. Assume that the company does not necessarily have to meet the maximum demand (i.e., production quantities can be lower than the maximum demand).

a) What are the decision variables in CC Wonks'optimization problem? How many decision variables are there? Clearly explain the decision variables in English and provide the mathematical notation used in the algebraic formulation of the decision problem.

b) What is the objective function? Clearly explain the objective function in Englishand provide the mathematical formulation.

c) What are the constraints of the problem? Clearly explain each constraint in Englishand provide the mathematical formulation.

d) Are there any sign or type restrictions in your formulation? Why or why not? Clearly explain the sign and type restrictions (if any).

e) Construct a spreadsheet to determine the optimal production plan using Excel Solver.

i. Provide a screenshot of your spreadsheet model. The screenshot should show all the cells involved in formulating the optimization problem. NOTE: Do not copy and paste your spreadsheet to your answer report as an Excel object. Instead, capture a screenshot in Excel and paste this as an image.
ii. Provide a screenshot of your spreadsheet with the formula view. You can switch from normal view to formula view in Excel by clicking the CTRL and ~ keys on your keyboard simultaneously.
iii. Prepare the Solver model by logging all the information to the Solver Parameters window in Excel. Provide a screenshot of the Solver Parameters window in Excel.
iv. Finally, solve the optimization problem using the Simplex LP method in Excel Solver. What is the optimal solution, i.e. the optimal profit and the optimal production quantities?

f) Suppose the maximum demand, required amount of cotton and required processing hours remain unchanged. Would you advise CC Wonks to increase the amount of cotton available or the amount of processing hours available, or both, to increase its optimal profit? Explain clearly why (or why not).

g) Suppose the maximum demand for flannel goes down to 150 yards. Does this have an effect on the optimal production plan? What is the change in optimal profit and optimal production quantities? Is this change expected? Can you explain why the increase in the demand of this product has such an effect on the optimal production plan?

Question 2: OUTBOUND LOGISTICS NETWORK

One option in managing outbound logistics is to own and operate distribution centers. The outbound logistics network consists of distribution centers and retail stores. Assume that you work as an operations consultant for a textile company. Your client has already determined the location of its stores; the number and location of the distribution centers are yet to be determined.
The following information will guide you in analyzing the facility location decisions.

• The company will open 228 stores in 13 different states in the next 4 years. See the Excel file. The plan is finalized and will not change.

• The company has identified 5 possible locations for its distribution centers: Mabelvale AR, Valdosta GA, Salisbury NC, Ft. Worth TX and Salt Lake City, UT

• The company plans to operate 4 or 5, but not fewer, distribution centers. You will make recommendations to the company on the number and location of the distribution centers, considering the costs during the fifth year of operation.

• For each candidate location, estimated monthly
o cost of leasing,
o operating cost,
o capacity (in terms of number of truckloads of goods that can be handled) during the fifth year of operation are provided in the Excel file.
• The Excel file also has information on the distance between the candidate distribution center locations and store locations.
• Your client tells you that the transportation cost will be $1.2 per mile per truckload.
• Your client will make two deliveries to each store every week. Each delivery will involve only one truckload. That is, a total of two truckloads of goods will be shipped from the distribution centers to each retail store each week. In your analysis, assume that a month is only four weeks.

You will analyze the information provided, build spreadsheet models, and use optimization to make a recommendation to your client on which distribution centers to operate. As a part of your analysis, you will answer the following questions.

a) To minimize leasing and operating expenses, which locations should your client choose for its distribution centers? To answer this you have to prepare Table-1 as shown below and include it in your report. Clearly explain and justify your answer.

b) Which locations should your client choose for its distribution centers if the goal is to minimize the monthly total outbound transportation cost? You have to prepare Table-2 as shown below to answer this question.

As a part of your answer, you will build spreadsheet models. Each row of Table-2 shows you one possible logistics network configuration. You will use Excel Solver to determine the optimal shipment plan that minimizes the monthly transportation costs of each configuration during the fifth year of operations. (You have to formulate and solve six different optimization problems.) HINT: You can make this process more efficient. You can setup only one spreadsheet model that includes all distribution centers (the most general problem). Then, if a distribution center is not used in a configuration, you can simply equate its capacity to zero and then solve the problem with the Excel Solver.

In your Solver models, the decision variables should be the number of truckloads shipped from the distribution centers to the retail stores. You can ignore the type restrictions; i.e., do NOT include any integer (whole number) constraints in your Solver models. If you formulate the problem correctly, the optimal solution provided by the Solver will be in whole numbers even without the type restrictions.

Once you determine the optimal shipment plan for each configuration, compute the capacity utilization of the logistics network to complete Table-2. Consider the total distribution center capacity (available capacity) versus the total number of truckloads shipped (actual capacity) in your Solver model to determine the utilization.

c) You present the transportation cost in the main body of your report (see part b above). However, you must explain how these costs are computed. Your client is not familiar with optimization models. You must clearly explain your approach to computing the minimum transportation cost only for the network configuration which has 5 distribution centers. To do so:

First,

1) Define your decision variables, how many decision variables do you have?
2) Explain how you find the objective function (the objective function is very long, so you do not have to explicitly write it down.
3) Explain your constraints. What type of constraints do you have? How many of each type should you have?

Second, include screenshots of the spreadsheet model for this particular configuration (configuration with 5 distribution centers), show the formula used in your spreadsheet, and the Solver parameters window in your report. Do not copy and paste your Excel work into your Word document. Capture a screenshot and paste it into Word as an image. In Windows, screenshot can be taken by pressing on both Ctrl and PrtSc (Print Screen) together.

Finally, post the optimal shipment plan and the optimal cost by using the answer sheet that is generated through Excel Solver. Again capture a screen shot, do not copy and paste directly from Excel.

d) If the goal is to minimize the total cost per month (i.e., the monthly leasing, operating, and transportation costs combined), which locations should your client choose for its distribution centers? Clearly explain and justify your answer. As a part of your answer, prepare Table-3 as shown below.

e) Assume that the company runs in a market with high demand variability. Which locations would you pick now? Why? Explain.