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:
- We use 0.75 lb of grade 6 oranges and 0.25 lb of grade 10 for each lb of bags of oranges.
- 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.
