Production planning using Excel's Solver add$-$in: You are the manager of a small bakery which only produces four types of goods. The amounts of the main ingredients needed for producing one cake, one pie, one loaf of bread, and one dozen cookies are listed in a table below. You are also given inventory levels, as well as costs per unit $($hour$,$ pound, item$).$ Your goal is to figure out how many of each product you should produce so as to minimize waste. Once you do this with the parameters provided in the worksheet, you will have to alter some of the parameters and repeat the Solver calculations. In the cells below, you can set how many of each item should be made. In the cell below, the total value of unused resources will be calculated. $4|$c$|$ HOW MANY SHOULD WE MAKE?

CAKES PIES BREAD COOKIES

$1111$

GRAND TOTAL WASTED: $ $1,338\mathrm{.}22$

THESE ARE TO BE THE VARIABLE CELLS IN THE SOLVER: THE THIS IS TO BE THE CELLS THE SOLVER WILL MANIPULATE, TRYING TO AFFECT THE "OBJECTIVE CELL" IN OBJECTIVE CELL'S VALUE. THE SOLVER. $2*1|$c$|$ CAKE $1$c$|$ PIE $1$c$|$ BREAD $1$c$|$ COOKIES

$2-5[$ HOW MUCH OF EACH INGREDIENT WILL WE USE FOR EACH PRODUCT IF WE; PRODUCE THE NUMBERS OF EACH AS LISTED ABOVE? $]$

TIME $0\mathrm{.}750\mathrm{.}550\mathrm{.}350\mathrm{.}45$

FLOUR $4\mathrm{.}051\mathrm{.}053\mathrm{.}001\mathrm{.}95$

SUGAR $1\mathrm{.}501\mathrm{.}050\mathrm{.}302\mathrm{.}05$

BUTTER $1\mathrm{.}000\mathrm{.}500\mathrm{.}001\mathrm{.}00$

EGGS $4\mathrm{.}001\mathrm{.}002\mathrm{.}002\mathrm{.}00$

$[$ HOW MUCH WILL WE; USE IN TOTAL? $][$ HOW MUCH WILL BE; LEFT OVER? $][$ WHAT DO THE LEFT$-$; OVERS COST? $]$

$2\mathrm{.}1085\mathrm{.}90$ $ $644\mathrm{.}25$

$10\mathrm{.}05479\mathrm{.}95$ $ $307\mathrm{.}17$

$4\mathrm{.}90228\mathrm{.}10$ $ $173\mathrm{.}36$

$2\mathrm{.}5082\mathrm{.}50$ $ $34\mathrm{.}65$

$9\mathrm{.}00447\mathrm{.}00$ $ $178\mathrm{.}80$

STEP #$1$: Fill in the cells above with a formula to calculate how much of each component will be used for the number of products made. You STEP #$2$: Fill in the cells above with formulas to calculate the values indicated should be able to do this with a SINGLE FORMULA that you copy in the heading. You should be able to do this with three formulas, each of which through the whole table. you copy down to complete the column. Step #$3$: Set up and run the Solver add$-$in to figure out how many of each item should be produced $($in the cells shaded blue$)$ in order to minimize the total cost of wasted ingredients $($in the cell shaded red$)$ based on the available resource parameters $($in the cells shaded green$).$ You must set up the Solver's parameters properly to get a proper solution. Check that the solutions the Solver returns to you make sense, and then answer the questions posed on the right side of this worksheet.Set each of the "how many should we make" values to zero. The "grad total wasted" cell should now tell you the total value of your inventory. What is the "GRAND TOTAL WASTED" value when you make nothing? $<$ YOU WILL HAVE TO MANUALLY ENTER YOUR ANSW Set your worksheet to show the results of producing exactly twelve $(12)$ of each product. How much $($quantity$,$ NOT COST$)$ will be left over of each ingredient: With the parameters given in the base file, run the Solver using the $"$GRG Nonlinear" solving method. What are your results:Change the Solver parameters to require at least ten $(10)$ of each item to be produced each week. Re$-$run the solver, again using the $"$GRG Nonlinear" solving method. What are your results: Change the parameters in your worksheet to show that you have $600$ pounds of flour and $50$ pounds of butter to use. Re$-$run the solver, again using the $"$GRG Nonlinear" solving method. $($Your cousin is still working part$-$time and you must still produce at least ten of each item.$)$ What are your results:You take a large order and must produce a minimum of SIXTY PIES $(60).$ Modify the solver parameters and re$-$run the solver. $($Keep the $10-$of$-$each$-$item minimum. Keep your cousin. Keep using the $"$GRG Nonlinear" solving method.$)$ What are your results: NOTE: Some people may find that their answers do not change from the previous step $($if they are already making $60$ pies$),$ but the new constraint must still be added into the model. That same week, you get another order for forty $(40)$ batches of cookies. So now you have to make at least ten cakes; at least sixty pies; at least ten loaves of bread, and at least $40$ batches of cookies. What are you going to run out of$?($PUT AN $"$X$"$ IN THE CELL THAT MATCHES YOUR SELECTION AND LEAVE THE OTHERS BLANK.$)$ In an attempt to make good on the cookie order, you decide to not make any cakes that week, so you can use those ingredients to make cookies and pies. While still producing the sixty pies that are needed, how many batches of cookies could you make with the ingredients you have? $($hint: You can use the "goal seek" tool under the "what$-$if analysis" menu or ribbon$-$item.$)$ With what's left after making sixty pies and all of the cookies you can, how many whole loaves of bread could you make? How much money would be wasted? $($The "goal seek" tool can help you here, too.$)$ $