Problem Set 1 – Solutions to Non-Starred Questions

Note: Solutions to the starred (*) questions will be available after HW1 due date

The T-Shirt Vendor Problem*

End of 2025 MLB Season is approaching, and the LA Dodgers are likely
to win the National League West Division. Jessica, a local T-shirt
vendor in LA, plans to order T-shirts celebrating the Team’s Division
Championship from a manufacturer, and then sell them to the fans. The
fixed cost of any order is $2000, the variable cost per T-shirt to Jessica
is $33, and Jessica’s selling price is $40.

However, this price will be charged only until a week after the beginning
of the playoff season. After that time, Jessica figures that interest in the T-shirts will be low, so
she plans to sell all remaining T-shirts, if any, at $23 each (assume all leftovers will be sold).

Her guess is that demand during the full-price period (i.e., the first week) will be 1500, but she is
concerned that this forecast is subject to error. Hence, she decided to order only 1400 T-shirts.

Question 1. Suppose you want to find an order quantity for Jessica that would maximize her
profit, using Excel Solver. Below is a screenshot of how you would do it, with the solve text
boxes left unfilled. What cell should be entered into the “Set Objective” box and into the “By
Changing Variable Cells” box? What is the optimal order quantity? You may try this using the
spreadsheet available on eTL: “Lecture02_0903_example_order_completed.xlsx.”

Note: The solution is obvious in hindsight, but only with the assumption that the demand will be exactly 1500.
Solution

Enter B22 into “Set Objective” and B13 to “By Changing Variable Cells.” This will generate an
optimal solution of 1500. Cleatly, if we firmly believe that the demand will be exactly 1500 and
all other parameters are certain, the order should match the demand to maximize profit.

Question 2. Despite your suggestion based on Question 1, Jessica remains conservative and
still wants to order 1400 T-shirts. However, she is interested in other ways to increase her profit
to $8,600. What options does she have to reach this profit goal? Please be specific with
numbers; e.g., she can reach this goal by either increasing selling price to $X, decreasing
variable cost to $Y, or decreasing fixed cost to $Z.
Note: You might want to increase the decimal place to be more precise. Assume that changing selling price,
variable cost, etc. does not affect other parameters.

Solution

For the X, Y, and Z values mentioned in the question, the goal can be reached by either

- increasing selling price to ~$40.571,

- decreasing variable cost to ~$32.429, or
- decreasing fixed cost to $1200.

These values can be comveniently found by using Goal Seek in Excel. Of course, there can be
a combination of changes in mutiple parameters to reach, e.g., decrease variable cost by $0.2
and decreasing fixed cost price by $520.

Question 3. Jessica realized that if she decreases (increases) her selling price by $1, demand
will increase (decrease) by 150 (from the current 1500). E.g., if she sets the price to $39, then
demand will be 1650; if $41, then 1350, etc. This time, with this possibility of influencing
demand, she changes her mind and wants to order 1800 T-shirts. How should she set her
selling price to maximize her profit? Provide your answer using Excel Solver or Data Table.

Solution

(1) Modify Cell B10 to “=1500+(40-B6)*150” to redefine demand as a function of price.

 (2) Change her current order quantity 1400 to 1800 in Cell B13.

(3) In Excel Solver, maximize B22, i.e., profit, by changing B6, i.e., selling price.

Click “Solve,” and you obtain an optimal solution: $38.

The Product Development Problem

iScreen Lab wants to decide whether to develop, produce, and sell

a new, handheld retinal imaging device for eye disease screening,
to be called the Intelligent Retinal Imaging System (IRIS).

The R&D department of the Lab estimates that it will cost

$300,000 to develop the product. The production department
estimates the unit cost of the device to be $200. The retail price is
to be set at $500 per unit. Based on our breakeven (BE) analysis
in class, we know that the BE point is to produce 1000 devices.

However, the production team realizes that their factory can only produce up to 800 devices
following the development phase (i.e., capacity constraint). We know that producing 800
devices will not “break even.” Luckly, the production team seems to be able to decrease
production cost per unit (currently $200), which might be helpful in justifying producing only 800.

Question 1. What is the maximum production cost (i.e., minimally adjusted from the original
cost) such that profit is non-negative while producing 800 devices (i.e., 800 becomes the new
BE point)?

Solution

We can directly use the existing spreadsheet and use Goal Seek: Set cell E15 (i.e., profit when
producing 800) to value 0 (to break even) by changing cell B4 (i.e., production cost).

Then it modifies the production cost to $125 to render the quantity 800 the new BE point as
follows (note that the production cost and profit values have changed).
Question 2. [Plot Twist] You later realize that decreasing production cost/unit by $1 (from the
original cost $200) will increase R&D cost (i.e., fixed cost; currently $300,000) by $10. This time,
what would be the production cost that would make 800 break even?

Solution

This time, instead of the hardcoded 300,000 in Cell B3, enter the following function:
=300000+(200-B4)*10. I.e., now the R&D cost is a function of the production cost. Then repeat
the process illustrated in Question 1. After increasing the decimal place, we can see that
production cost should be roughly $124.051 so that you break even at 800.
The Monopoly Pricing Problem

Suppose OPAQ controls the supply of crude oil in the world market. Market demand for oil
depends on the price set by OPAQ. Higher prices induce greater conservation effort by
consumers, leading to lower demand for oil. Thus, OPAQ faces a dilemma: setting higher prices
will generate higher revenues and profits per barrel but fewer barrels will be sold, which may
lead to lower total profits for the cartel.

Suppose that, based on the past consumption pattern, OPAQ estimates that any price above
$150 a barrel will choke off all demand, while every dollar drop in the price below $150 will
increase the market demand by 9 million barrels per month. The cost of producing oil is
estimated to be $10 per barrel.

Question 1. What price should OPAQ set to maximize its total profit?

Solution

This question has already been addressed in class.

Question 2. How would this price and OPAQ’s profit change as the demand choke-off price
varies over $100 to $200 range?

Solution

To answer this question efficiently, define the demand function as follows:

Next, everytime you assess a new demand choke price (i.e., the “maximum price” indicated in
Cell E4), simply change the value in Cell E4 and re-run the Solver.
For example, if the new demand choke price is $100, replace the existing $150 in Cell E4 with
$100. You will immediately notice that $80 is no longer the profit maximizer. The profit curve will
show that the maximizer price is somewhere between $50 and $60.

Then run the Excel solver again as follows:

The Solver will find $55 to be the new price that maximizes profit, given $100 as a new demand
choke price.
The Server Production Problem*

A firm that assembles computers and computer equipment plans to start production of two new
web server models: Type 1 and Type 2. Producing each type of model will require assembly
time, inspection time, and storage space.

The amount of each of these resources that can be devoted to the production is limited. The
manager would like to determine the quantity of each model to produce in order to maximize the
profit generated by sales of these servers.

To better understand the problem, the manager met with design and manufacturing personnel
and obtained the following information:

The manager also met with the firm’s marketing manager and learned that demand for the
servers was such that whatever combination of these two models of servers is produced, all of
them can be sold.

Question 1. Suppose that you—as the manager—decided to produce 7 units of Type 1 and 6
units of Type 2 servers. Would this plan violate any of the resource constraints (i.e., assembly
time, inspection time, or storage space availability)? Your answer can be in any form, but it
should be supported and demonstrated via Excel (you may use the above tables in your
spreadsheet).

Solution

With 7 units of Type 1 servers and 6 units of Type 2, the total used assembly hour is as follows:
7 units × 4 hours/unit + 6 units × 10 hours/unit = 88 hours, which does not violate the 100 hour
capacity. The total inspection hours and storage space can be calculated similarly: 20 and 39,
respectively, neither of which violates the corresponding capacity. In Excel, you can simply use
the SUMPRODUCT function as follows:
By comparing the used resources and the availability, we conclude that this decision is feasible
(i.e., implementable).

Question 2. Suppose you stick with the above plan to produce 7 units of Type 1 and 6 units of
Type 2 servers. How would your profit change if Profit($)/unit for Type 1 (which is currently $60)
varies over $50, $60, and $70 and Profit($)/unit for Type 2 (which is currently $50) varies over
$40, $50, and $60? That is, you want to reassess your profit across 3x3 = 9 different scenarios.
Use Data Table to provide your answer.

Solution

(1) First, set up a table as follows, where cell C26 is not a hard-coded 720 but a formula:
=SUMPRODUCT(C5:D5,C12:D12). Note that the orange-highlighted numbers in the
table represent the potential variations of the unit profit of Type 1 server, highlighted in
the same color; similarly for the green-highlighted numbers.
(2) Use Data Table: Select the range the covers the table (C26:F29), open Data Table, and
choose C5 as “Row Input Cell” and D5 as “Column Input Cell.”

Then it will generate the scenario-based total profit values under different combinations
of Type 2 and Type 2 unit profits (the orange and green numbers) as follows.