Assignment Paper

Case 1. Sunblessed Orange Company
Issue: Production of our Two Products
The Operations VP asks you to recommend a production plan for this next week. There are two orange products this company markets: Bags of oranges and Cartons of orange juice. The primary inputs to production of these two products are oranges of two grades (obtained from suppliers). For production next week, we have available inventories of the two grades of oranges (listed below) that we will use. A production plan for a week specifies the lbs of oranges in bags and lbs of oranges in juice to produce. A good production plan is one that yields high profit contribution (revenue – variable cost) from the production output.
Data and Additional Information:
Present supplies that are available next week to be used in production of bags and juice are the following:
100,000 lbs of grade 6 oranges 120,000 lbs of grade 10 oranges
Available Labor.
This next week we have 5 production workers or 5 * 40 hrs = 200 labor hours available. In our production, we use 1 labor hour for each 1000 lbs of orange bags and 1 labor hour for each 1250 lbs of orange juice. Thus, we need 0.001 hrs per lb of orange bags and 0.0008 hrs per lb of orange juice.
Sunblessed Current Production Process:
1. We use 0.75 lb of grade 6 oranges and 0.25 lb of grade 10 for each lb of bags of oranges.
2. We use 0.25 lb of grade 6 oranges and 0.75 lb of grade 10 for each lb of orange juice.
Other information obtained from Production and Marketing:
The mktg VP indicates that we want at least 50,000 lbs of orange bags and 75,000 lbs of orange juice each week.
Our revenue per lb sold of bags of oranges is $1.50 and per lb sold of orange juice is $3.50.
The variable costs (labor, equipment use, oranges, variable overhead, etc):
—$0.80 per lb of orange bags —$1.15 per lb of orange juice
Use this Excel file to answer the following Case 1 questions. In the spaces indicated in this file, perform any necessary modeling and analysis required to support your answers. Once complete, please upload below.

Question 1: What is the Business Problem to be modeled? Define carefully.
Bus Question: _____________________
Decision (incl #): _____________________
Objective Measure: _____________________
Constraints (incl #): _____________________

Question 2: Analysis: Build a spreadsheet model to assist with this business problem and perform necessary manipulations of it to answer the the specific business questions that follow. Construct the model on the first worksheet of your Excel file in the space indicated in that file. Use our MBA 791 conventions for any spreadsheet model:
Cell Color: Data input—Blue, Decisions—Yellow, Output calculations—Gray, Objective measure calculation—Orange
Structure: One column for each decision value, for constraint calculations, and for constraint limits. One row for each constraint, for decision values, and for objective measure calculation.

Question 3: Use the model to recommend a Production Plan for this week.
Recommended Production Plan: __________________
Expected Revenue: __________________
Expected Variable cost: __________________
Expected Profit Contribution: __________________

Question 4: Insight One: There is uncertainty about revenue per lb of orange juice (we estimated $3.50 above). We believe that the actual market price/lb (i.e. our revenue) might vary between $3.00 and $3.50. Help the manager understand the impact of this uncertainty on the recommended production plan (production quantities and profit)? Examine this business question in revenue increments of $0.10. In your Excel file, produce a table, with labels, that you would present to the management team and then explain in business language the impact of this uncertainty about the revenue/lb of orange juice.

Question 5: Insight Two: The Marketing VP wants to know the “cost” of the market goal (minimums of each product). In other words, how much profit/week are we giving up (i.e., opportunity cost) because we have the two minimums? Provide this cost and include an explanation of how you calculated your answer.

Case 2. Inventory Planning—Demand Analysis

CASE 2 (A) Jessica Williams, manager of kitchen appliances for the Cincinnati Area XXX Department Stores, feels that her inventory levels of a popular TV models has been running high; she’s unsure if such levels are necessary. Each day she checks inventory and, if there are less than 30, she orders 20 (this policy was present when she started her job). However, before revising her inventory policy and actions, she decided to gather some data and conduct a first analysis. She reviewed a sample of 300 days over the last year or so and recorded the number of this product sold each day. She believes that these 300 days are typical of this TV model’s current demand for her stores.

Jessica says that these historical data results can be used to estimate the probability distribution of daily demand. For example, the estimated likelihood of customers buying 2 of this TV in a day would be 51/300 = 0.17.

The lead time for replenishing this TV model inventory is 5 working days (time between placing an order and the TVs arriving.) Thus, Jessica wants to develop a better understanding of “# of TVs demanded in a 5 day period.” Specifically, she would like to know the average, the 85th percentile, and the 95th percentile of # tvs sold in 5 days and also the likelihood of a stockout — if there are 24 in inventory to begin a 5 day period. (Stockout is the condition of no products in inventory when a customer requests one. If inventory is X, a stockout occurs if the demand is more than X)
Use this Excel file to answer the following Case 2 questions. In the spaces indicated in this file, perform any necessary modeling and analysis required to support your answers. Once complete, please upload below.

Question 1: Provide a Problem Definition for Jessica’s situation, i.e. the problem to be analyzed:
Business Question(s): _____________
Decisions: _____________
Obj Measure(s): _____________
Constraints: _____________

Question 2: Build An Excel simulation model for analysis of this situation.
Build the model in the Excel file provided for this case as indicated.

Question 3: Use the model in appropriate ways to generate a large sample of the key measure of this business process.

Question 4: Provide the following business answers for Jessica:
What is the estimated Average TV Model Sales for a 5 day period?
What is the estimated 85th %tile of TV Model Sales for a 5 day period?
What is the estimated 95th %tile of TV Model Sales for a 5 day period?
If Jessica just receives an shipment of 24 of this TV model for the in-store inventory (which is currently zero), what is the likelihood of a stockout over the next 5 days? (A stockout is when a customer wants to buy this TV model but none is available in-store.)

CASE 2 (B)
Jessica has another product (Product B) for which she believes the customer demand per day is approximately a rounded Normal distribution with mean = 8.5 and stdev = 2.1. To assist in inventory planning for this product (there is a 6 day lead time to replenish this inventory), Jessica would like to know (1) the chance of a stockout if the inventory level at the beginning of the 6 day period is 55 and (2) the inventory level at the beginning of a 6 day period to have a 5% chance of a stockout.
Copy your previous model to another place in your Excel file. Revise the model appropriately, and then work with this revised model to answer these two questions Jessica has.
Question 1. If the in-store inventory for Product B is 55, what is the chance of a stockout over the next 6 day lead time?
Question 2. If Jessica wants to allow only a 5% chance of a Product 2 stockout, what should be the inventory level at the beginning of a 6 day lead time period?