Question

Jacob Jones is the manager of the produce section at Snell’s grocery store. Jacob must determine each day how many pounds of bananas to order from the supplier. Demand varies somewhat from day to day and if Jacob orders too many bananas, he will have to sell leftovers at a discount, if he orders too few bananas, customers will be dissatisfied and complain to his boss.   Jacob wants to set up a profit model in Excel to experiment with the number of pounds of bananas he should order each day.

Bananas are ordered (and assume delivered the same day) from the supplier each day and cost Jacob 15 cents per pound. They are sold for 69 cents a pound if sold the first day after delivery.  

Any bananas that are not sold the first day must be discounted to 39 cents per pound. Assume all bananas that are discounted will be sold at the lower price.

a. Set up an EXCEL spreadsheet in order to calculate profit for Jacob’s banana problem.

First day Demand and Order Quantity are separate unknown variables.  

You must use the Excel IF function in this model.

b. Using Excel Data/What-If/Data Table create a two way-table to show how profit changes with changes in First day demand and Quantity ordered. Use values of 100, 120, 140, 160, 180, and 200 for both variables in your table.

Answer 1

Let us assume that there be the supply variable be X and demand variable be Y.

Now there can be only three scenario

1. X>Y

2. .X=Y

3. X<Y

For an optimal solution with maximized profit ,

X=Y since then there wont be any closing inventory and hence the shop will not be forced to sell the banana at lower cost and also to spend on the excess leftover.

for a general situation we can use the general solution

The shop will incur a loss of 30 cents for each unsold pound of banana per day.

IF(X>Y, 69Y-15X-30(X-Y),IF(X=<Y,69Y-15X))  

In excel spreadsheet ,

=IF(C28>D28,69*D28-15*C28-30*(C28-D28),IF(C28<=D28,69*D28-15*C28))

Day 1	Order Quantity	Demand Quantity	closing inventory	Profit
1			0	0	=IF(C28>D28,69*D28-15*C28-30*(C28-D28),IF(C28<=D28,69*D28-15*C28))
2				0
3				0
4				0
5				0
6				0
7				0
8				0
9				0
10				0
11				0
12				0
13				0
14				0

b). Now let us test the model with the help of some data

Scenario #	Order Quantity	Demand Quantity	closing inventory	Profit
1	100	100	0	5400
2	120	120	0	6480
3	140	140	0	7560
4	160	160	0	8640
5	180	180	0	9720
6	200	200	0	10800
7	200	200	0	10800