$3.$ Go bananas with optimisation

You are a fresh produce manager in the supermarket. Use historical data provided in the Excel$-$file $$Q$3\_$Bananas.xlsx$$ to choose optimal solutions for buying bananas for the supermarket for the two scenarios below:

$1.$ You are a greedy manager, and you want to earn as much money as possible.

$2.$ You are an environmentally minded manager, and you want to reduce the bananas wastage less than $1\%$ per year of your total amount of bought bananas.

The Excel file has three columns:

$1.$ Date of sales. It is not important for this problem.

$2.$ Demand for bananas. This is a random variable, and you don$$t know it when you make your decision about buying banana stock for your supermarket. Later you use it to calculate your profit or loss. For example, if demand is $400$ kg$,$ while your stock is $300$ kg only, then you are able to sell all bananas, and part of the demand is not satisfied; however, if stock is $500$ kg$,$ then you sell $400$ kg$,$ the full demand is satisfied, but $100$ kg of bananas are wasted. We assume, that bananas cannot be stored and should be sold on the next day. Unsold bananas go to waste.

$3.$ Price is a selling price of bananas in the supermarket on a given day. The supermarket does not set their own prices. The supermarket follows the wholesale market and sells bananas with a fixed $25\%$ margin. That, if the listed selling price for today is $$1\mathrm{.}25$ then the cost of buying bananas on that day is $$1,$ and the supermarket profit is $25$ cents $($$$0\mathrm{.}25)$ per one kilogram of bananas. Beware, cost is always $25\%$ less than selling price.

As you know, demand has a negative relationship with the price $$ if price goes up$,$ demand goes down, people don$$t buy that much if the price is too high. Hence, as a smart manager, you are interested in a $$flexible$$ solution where the quantity you buy varies from day to day and depends on the price.

use excel solver to solve this