Question;

picture of spreadsheet model below:

The spreadsheet model for Example 1.4 contains a table for profit versus order quantity and demand and a table for order quantity versus expected profits. Create two charts that show the information in an intuitive and visually appealing way C A ar E F G H Ordering decision with quantity discounts Range names used: 2 Cost =Modell$B$18 3 Inputs Quantity discount structure CostLookup Model $0$:$E$9 Sa At least Unit cost =Modell$$12 Demand 4 Unit cost-see table to right de Regular price $40 0 S4.00 Leftover price Modell$B$8 5 Leftover price $10 1000 $23.00 Order_quantity Modell$B$9 2000 7 $22.25 Probabilities ModellS$35:$1$35 Decision variable 3000 $21.75 Profit Modell$B$19 de 9 Order quantity 2500 4000 $21.30 Regular_price -Modell$$s th 10 Revenue Model $B$17 $2 11 Uncertain quantity Units sold at leftover price Model!$8516 $2 12 Demand 2000 Units sold at regular_price Modell$B$15 Sa 13 43 14 Profit model This example has a little of everything: VLOOKUP, two way data 15 Units sold at regular price 2000 table, probability and expected value, conditional formatting le 16 Units sold at leftover price 500 charting and SUMPRODUCT, But none of these are "frills." They $85,000 17 Revenue are all used to get the job done identify the best order quantity This is true in general: the more tools you have in your toolkit 18 Cost $55,625 the more business problems you can solve. 19 Profit $29,375 20 21 Data table of profit as a function of order quantity (along side) and demand (along top) 222 $29,375 500 1000 1500 2000 2500 3000 3500 4000 4500 The VLOOKUP funct 23 with values to use fe 500 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 24 1000 $2,000 $17,000 $17,000 $17,000 $17,000 $17,000 $17,000 $17,000 as many columns to $17.000 the value you want 25 1500 $4,500 $10,500 $25,500 $25,500 $25,500 $25,500 $25,500 $25,500 $25,500 the index of the colu 26 2000 -$9,500 $5,500 $20,500 $35,500 $35,500 $35,500 $35,500 $35,500 $35,500 the index of the next 27 2500 -$15,625 $625 $14,375 $29,375 $14,375 $14.375 $44,375 $44,375 $44,375 default) or FALSE (fo 28 3000 -$20,250 -$5,250 $9.750 $24.750 $39.750 $54,750 $54,750 $54,750 $54,750 29 3500 - $26,125-S11.125 $3,875 $18,875 $33,875 $48,875 $63,875 $63,875 $63,875 af you omit the lasta 30 4000 -$30,200 $15,200 -$200 $14,800 $29,800 $14,800 $59,800 $74,800 $74,800 if the last argument 31 4500 -$35,850 $20,850 -$5,850 $9,150 $24,150 $39,150 $54,150 $69,150 $84,150 Last entry less than 32 an HLOOKUP functic 33 Model of expected demands not columns 34 Demand 500 1000 1500 2000 2500 3000 3500 4000 4500 35 Probability 0.025 0.05 0.15 0.25 0.25 0.15 0.07 0.04 0.015 36 Sum of probabilities --> 1 A two-way data tabi 37 Order quantity Expected profit a one-way data tabl 38 500 58,000 to the output cell in 39 to the right of this 1000 $16,625 40 1500 $24,000 Analysis dropdown Order 2000 to vanables live. Model ALIENW A1 Ordering decision with quantity discounts $25,500 $35,500 $44,375 $54,750 $63,875 $74,800 $84,150 A C D E F G 25 1500 -$4,500 $10,500 $25,500 $25,500 $25,500 $25,500 $25,500 $25,500 26 2000 -$9,500 $5,500 $20,500 $35,500 $35,500 $35,500 $35,500 $35,500 27 2500 -$15,625 -$625 $14,375 $29,375 $44,375 $44,375 $14,375 $44,375 28 3000 -$20,250 $5,250 $9,750 $24,750 $39,750 $54,750 $54,750 $54,750 29 3500 -$26,125 $11,125 $3,875 $18,875 $33,875 $48,875 $63,875 $63,875 30 4000 -$30,200 $15,200 -$200 $14,800 $29,800 $44,800 $59,800 $74,800 31 4500 -$35,850 -$20,850 -$5,850 $9,150 $24,150 $39,150 $54,150 $69,150 32 33 Model of expected demands 34 Demand 500 1000 1500 2000 2500 3000 3500 4000 35 Probability 0.025 0.05 0.15 0.25 0.25 0.15 0.07 0.04 36 Sum of probabilities --> 37 Order quantity Expected profit 38 500 $8,000 39 1000 $16,625 40 1500 $24,000 Order 2000 to 41 2000 $30,625 maximize the 42 2500 $32,375 expected profit, 43 3000 $31,875 44 3500 $27,875 45 4000 $24,625 46 4500 $19,200 4500 0.015 1 48 49 Each SUMPRODUCT above is the sum of nine potential demands times their respective 50 probabilities. 51 The SUMPRODUCT function takes two range arguments, which must be exactly the same size and 52 shape, and it sums the products of the corresponding values in these two ranges. For example, the 53 formula -SUMPRODUCT(A10:011,E12:F13) is a shorcut for a formula involving the sum of four 54 products:=A10*12+A11E131B10 F124811F13. 55 56 57 58 59 60 Unit Expediting Cost 61 62 Expected Profit 63 $35.000 64 Model The spreadsheet model for Example 1.4 contains a table for profit versus order quantity and demand and a table for order quantity versus expected profits. Create two charts that show the information in an intuitive and visually appealing way C A ar E F G H Ordering decision with quantity discounts Range names used: 2 Cost =Modell$B$18 3 Inputs Quantity discount structure CostLookup Model $0$:$E$9 Sa At least Unit cost =Modell$$12 Demand 4 Unit cost-see table to right de Regular price $40 0 S4.00 Leftover price Modell$B$8 5 Leftover price $10 1000 $23.00 Order_quantity Modell$B$9 2000 7 $22.25 Probabilities ModellS$35:$1$35 Decision variable 3000 $21.75 Profit Modell$B$19 de 9 Order quantity 2500 4000 $21.30 Regular_price -Modell$$s th 10 Revenue Model $B$17 $2 11 Uncertain quantity Units sold at leftover price Model!$8516 $2 12 Demand 2000 Units sold at regular_price Modell$B$15 Sa 13 43 14 Profit model This example has a little of everything: VLOOKUP, two way data 15 Units sold at regular price 2000 table, probability and expected value, conditional formatting le 16 Units sold at leftover price 500 charting and SUMPRODUCT, But none of these are "frills." They $85,000 17 Revenue are all used to get the job done identify the best order quantity This is true in general: the more tools you have in your toolkit 18 Cost $55,625 the more business problems you can solve. 19 Profit $29,375 20 21 Data table of profit as a function of order quantity (along side) and demand (along top) 222 $29,375 500 1000 1500 2000 2500 3000 3500 4000 4500 The VLOOKUP funct 23 with values to use fe 500 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 24 1000 $2,000 $17,000 $17,000 $17,000 $17,000 $17,000 $17,000 $17,000 as many columns to $17.000 the value you want 25 1500 $4,500 $10,500 $25,500 $25,500 $25,500 $25,500 $25,500 $25,500 $25,500 the index of the colu 26 2000 -$9,500 $5,500 $20,500 $35,500 $35,500 $35,500 $35,500 $35,500 $35,500 the index of the next 27 2500 -$15,625 $625 $14,375 $29,375 $14,375 $14.375 $44,375 $44,375 $44,375 default) or FALSE (fo 28 3000 -$20,250 -$5,250 $9.750 $24.750 $39.750 $54,750 $54,750 $54,750 $54,750 29 3500 - $26,125-S11.125 $3,875 $18,875 $33,875 $48,875 $63,875 $63,875 $63,875 af you omit the lasta 30 4000 -$30,200 $15,200 -$200 $14,800 $29,800 $14,800 $59,800 $74,800 $74,800 if the last argument 31 4500 -$35,850 $20,850 -$5,850 $9,150 $24,150 $39,150 $54,150 $69,150 $84,150 Last entry less than 32 an HLOOKUP functic 33 Model of expected demands not columns 34 Demand 500 1000 1500 2000 2500 3000 3500 4000 4500 35 Probability 0.025 0.05 0.15 0.25 0.25 0.15 0.07 0.04 0.015 36 Sum of probabilities --> 1 A two-way data tabi 37 Order quantity Expected profit a one-way data tabl 38 500 58,000 to the output cell in 39 to the right of this 1000 $16,625 40 1500 $24,000 Analysis dropdown Order 2000 to vanables live. Model ALIENW A1 Ordering decision with quantity discounts $25,500 $35,500 $44,375 $54,750 $63,875 $74,800 $84,150 A C D E F G 25 1500 -$4,500 $10,500 $25,500 $25,500 $25,500 $25,500 $25,500 $25,500 26 2000 -$9,500 $5,500 $20,500 $35,500 $35,500 $35,500 $35,500 $35,500 27 2500 -$15,625 -$625 $14,375 $29,375 $44,375 $44,375 $14,375 $44,375 28 3000 -$20,250 $5,250 $9,750 $24,750 $39,750 $54,750 $54,750 $54,750 29 3500 -$26,125 $11,125 $3,875 $18,875 $33,875 $48,875 $63,875 $63,875 30 4000 -$30,200 $15,200 -$200 $14,800 $29,800 $44,800 $59,800 $74,800 31 4500 -$35,850 -$20,850 -$5,850 $9,150 $24,150 $39,150 $54,150 $69,150 32 33 Model of expected demands 34 Demand 500 1000 1500 2000 2500 3000 3500 4000 35 Probability 0.025 0.05 0.15 0.25 0.25 0.15 0.07 0.04 36 Sum of probabilities --> 37 Order quantity Expected profit 38 500 $8,000 39 1000 $16,625 40 1500 $24,000 Order 2000 to 41 2000 $30,625 maximize the 42 2500 $32,375 expected profit, 43 3000 $31,875 44 3500 $27,875 45 4000 $24,625 46 4500 $19,200 4500 0.015 1 48 49 Each SUMPRODUCT above is the sum of nine potential demands times their respective 50 probabilities. 51 The SUMPRODUCT function takes two range arguments, which must be exactly the same size and 52 shape, and it sums the products of the corresponding values in these two ranges. For example, the 53 formula -SUMPRODUCT(A10:011,E12:F13) is a shorcut for a formula involving the sum of four 54 products:=A10*12+A11E131B10 F124811F13. 55 56 57 58 59 60 Unit Expediting Cost 61 62 Expected Profit 63 $35.000 64 Model