Jacob Jones is the manager of theproduce section at Snell’s grocery store. Jacob must determine eachday how many pounds of bananas to order from the supplier. Demandvaries somewhat from day to day and if Jacob orders too manybananas, he will have to sell leftovers at a discount, if he orderstoo few bananas, customers will be dissatisfied and complain to hisboss.   Jacob wants to set up a profit model in Excel toexperiment with the number of pounds of bananas he should ordereach day.

Bananas are ordered (and assumedelivered the same day) from the supplier each day and cost Jacob15 cents per pound. They are sold for 69 cents a pound if sold thefirst day after delivery.  

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

a. Set up an EXCEL spreadsheet in orderto calculate profit for Jacob’s bananaproblem.

First day Demand and Order Quantity areseparate unknown variables.  

You must use the Excel IF function in this model.

b. Using Excel Data/What-If/Data Tablecreate a two way-table to show how profit changes with changes inFirst day demand and Quantity ordered. Use valuesof 100, 120, 140, 160, 180, and 200 for both variables in yourtable.