# 1.) Complete “Develop the Spreadsheet Model” on p. 797 of the Albright et al. te

1.) Complete “Develop the Spreadsheet Model” on p. 797 of the Albright et al. text using Excel. Interpret the results. Inputs. Enter all of the inputs from the statement of the problem in the shaded cellsas shown. Note that in later examples we often include a brief discussion on “Where Dothe Numbers Come From?” For this problem it is easy to get the numbers. The inputs inrows 5, 6, and 8 are printed on the packages of most foods, and the other inputs areMaggie’s preferences.Range names. Create the range names shown in columns F and G. Our convention isto enter “enough” range names but not to go overboard. Specifically, we enter enough978-1-111-00606-8, Data Analysis & Decision Making with Microsoft® Excel, Rev 3e, S. Christian Albright, Wayne L.Winston, Christopher J. Zappe – © Cengage Learningrange names so that the setup in the Solver dialog box, to be explained shortly, is entirelyin terms of range names. Of course, you can add more range names if you like.Excel Tip: Here is a Shortcut for Creating Range NamesSelect a range such as A20:B21 that includes nice labels in column A and the cells youwant to name in column B. Then from the Formulas ribbon, select Create from Selectionand accept the default. You automatically get the labels in cells A20 and A21 as the rangenames for cells B20 and B21. This shortcut illustrates the usefulness of adding concise butinformative labels next to ranges you want to name.Changing cells. Enter any two values for the changing cells in the Dessert_planrange. Any trial values can be used initially; Solver eventually finds the optimal values.Note that the two values shown in Figure 14.2 cannot be optimal because they are not feasible—they contain more calories and fat than are allowed. However, we do not need toworry about satisfying constraints at this point; Solver takes care of this later on.Ingredients consumed. To operationalize the calorie and fat constraints, we must calculate the amounts consumed by the dessert plan. To do this, enter the formula=SUMPRODUCT(B5:C5,Servings_per_day) in cell B20 for calories and copy it to cell B21 for fat. This formula is a shortcut for the “written out” formula=B5*B16+C5*C16The SUMPRODUCT function is very useful in spreadsheet models, especially LP models, and we see it often. Here, it multiplies the amount of calories per serving by the number of servings for each dessert and then sums such products over the two desserts. When there are only two products in the sum, as in this example, the SUMPRODUCT formula is not really simpler to enter than the “written out” formula. However, imagine that there are 50 desserts. Then the SUMPRODUCT formula becomes much simpler to enter (and read). For this reason, we use it whenever possible. Note that each range in this function, B5:C5 and Servings_per_day, is a one-row, two-column range. It is important in the SUMPRODUCT function that the two ranges be exactly the same size and shape.5 Grams consumed. Similarly, we must calculate the total number of grams of dessertconsumed daily. To do this, enter the formula =SUMPRODUCT(B8:C8,Servings_per_day) in cell B25. Each product in this SUMPRODUCT is grams per serving times number of servings; hence, its units are grams. 6 Total taste index. To calculate the total taste index, enter the formula =SUMPRODUCT(B12:C12,B8:C8,Servings_per_day)in cell B27. This formula shows that the SUMPRODUCT function can use three ranges (or more), provided that they are all exactly the same size and shape. Three are required here because we need to multiply taste points per gram times grams per serving times number of servings. Again, this formula is equivalent to the “written out” formula =B12*B8*B16+C12*C8*C162.) Which business functions are more likely to use optimization models than others and why?