Naya makes wonderful Banana Bread. The table below shows the quantity of each ingredient required to make a loaf $($Recipe column$),$ the minimum quantity Naya can purchase at a time $($Order Quantity column$),$ and the corresponding price Naya must pay $($Price column$).$ For example: a loaf of bread requires $3-$bananas; Naya has to purchase bananas in bunches of $4$ at$-$a$-$time; and each bunch costs her $$1\mathrm{.}20.$

Naya sells her loafs for $$7\mathrm{.}50$ a piece.

Naya doesn$$t want any ingredients to go to waste, therefore she wants to purchase enough ingredients and bake enough loaves of bread so that when she$$s done baking she has no excess inventory of ingredients. Said another way, she wants to run out of all ingredients upon baking her last loaf.

Item Recipe Order Quantity Price

Banana $3$ bananas $4$ bananas$/$bunch $$1\mathrm{.}20$ per bunch

Eggs $2$ eggs $12$ eggs$/$carton $$4\mathrm{.}99$ per carton

Flour $1\mathrm{.}5$ cups $3$ cups per sack $$5\mathrm{.}30$ per sack

Sugar $2$ cups $3$ cups per sack $$3\mathrm{.}99$ per sack

Baking Soda $1\mathrm{.}5$ tsp $48$ tsp$/$container $$0\mathrm{.}99$ per container

Assignment

Step $1.$ Design a spreadsheet that includes at least all of the information in the table above.

Step $2.$ Create a cell that will track the number of batches Naya has made. Start by setting the value in this cell to $1($e$.$g$.,$ Naya has made $1-$batch$).$

Step $3.$ Create a cell for each ingredient that will track how much Naya has purchased $($e$.$g$.,$ the number of bunches of bananas, cartons of eggs, sacks of flour, sugar and baking soda she has purchased$).$ Begin with the value $1$ for each ingredient $($e$.$g$.,$ Naya has purchase $1-$bunch of bananas, $1-$carton of eggs, $1-$sack of flour, $1-$sack of sugar, and $1-$container of baking soda$).$

Step $4.$ You$$ll likely want to track the total amount of ingredients Naya has purchased based on Step $3,$ as well as the amount of each ingredient left over based on the number of loaves she$$s baked per step $2.$ For example, after purchasing $1-$bunch of bananas Naya has purchased $4-$bananas; and after baking $1-$loaf of bread, Naya has used $3-$bananas and has $1-$banana left over.

Step $5.$ Configure Solver to achieve an appropriate Objective given Naya$$s desire to have zero$-$inventory at the end of baking. Also configure appropriate Variable Cells and appropriate Constraints in order to determine how many batches of bread Naya should bake, and the amount of each ingredient she must purchase. When asked to $$Select a Solving Method$$ select $$Simplex LP$$; then click $$Options$$ and make sure $$Ignore Integer Constraints$$ is unchecked.

Hint: Naya can$$t make half$-$a$-$loaf, or purchase half$-$a$-$bunch of bananas, half$-$a$-$carton of eggs, half$-$a$-$sack of flour, half$-$a$-$sack of sugar, or half$-$a$-$sack of baking soda. She also needs enough ingredients for each loaf of bread she bakes.

Step $6.$ Once you$$ve solved the problem and you know how many loaves Naya must bake and how much of each ingredient she must purchase, please calculate her revenue $($recall she makes $$7\mathrm{.}50/$loaf$),$ her expense $($refer to the table on page $1$ for the cost of each ingredient$)$ and profit $($i$.$e$.,$ total revenue less total expense$).$