Please help - I'm in my last semester and my teacher is not helping me whatsoever.
In some ordering problems, such as Example 1-4, whenever demand exceeds existing inventory, the excess demand is not lost but is filled by expedited orders - at a premium cost to the company. Change Sam's model (the one in example 1-4) to reflect this behavior. Assume that the unit cost of expediting is $40.
Hint: Add a new cell to your spreadsheet called Unit Expediting Cost and use an IF statement to help you calculate the total expediting costs in your profit model.
Spreadsheet below:
A1 X Ordering decision with quantity discounts V W YA A B C D E F G H 1 J M N 0 0 R S T U 1 Ordering decision with quantity discounts Range names used: 2 Cost =Model!$B$18 Example1-4 3 Inputs Quantity discount structure CostLookup =Model!$D$5:$E$9 Sam's Bookstore, with many locations across the United States, places orders for all the latest books and then 4 Unit cost - see table to right At least Unit cost Demand =Model!$B$12 distributes them to its individual bookstores. Sam's needs a model to help it order the appropriate number of 5 Regular price $40 0 $24.00 Leftover_price =Model!$B$6 any title. 6 Leftover price $10 1000 $23.00 Order_quantity =Model!$B$9 7 2000 $22.25 Probabilities =Model!$B$35:$J$35 For example, Sam's plans to order a popular new hardback novel, which it will sell for $30. It can purchase any 8 Decision variable number of this book from the publisher, but due to quantity discounts, the unit cost for all books it orders 3000 $21.75 Profit =Model!$B$19 9 Order quantity 2500 depends on the number ordered. If the number ordered is less than 1000, the unit cost is $24. After each 1000, 4000 $21.30 Regular_price =Model!$B$5 the unit cost drops: to $23 for at least 1000 copies; to $22.25 for at least 2000; $21.75 for at least 3000; and to 10 Revenue =Model!$B$17 $21.30(the lowest possible unit cost) for at least 4000. For example, if Sam's orders 2500 books, its total cost is 11 Uncertain quantity Units_sold_at_leftover_price =Model!$B$16 $22.25(2500) = $55,625. 12 Demand 2000 Units_sold_at_regular_price =Model!$B$15 13 Sam's is uncertain about the demand for this book - it estimates that demand could be anywhere from 500 to 14 Profit model This example has a little of everything: VLOOKUP, two-way data 4500. Also, as with most hardback novels, this one will eventually come out inpaperback. Therefore, if Sam's has 15 Units sold at regular price 2000 table, probability and expected value, conditional formatting, any hardbacks left when the paperback comes out, it will put them on sale for $10, at which price it believes all 16 Units sold at leftover price 500 charting, and SUMPRODUCT. But none of these are "frills." They leftovers will be sold. How many copies of this hardback novel should Sam's order from the publisher? 17 Revenue $85,000 are all used to get the job done: identify the best order quantity. Objective: To learn about IF formulas, VLOOKUP, Two-way data tables, and SUMPRODUCT 18 Cost This is true in general: the more tools you have in your toolkit, $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) 22 $29,375 500 1000 1500 2000 2500 3000 3500 4000 4500 The VLOOKUP function is one of Excel's most useful functions. To use it, first create a vertical lookup table, 23 500 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 with values to use for comparison listed in the left column of the table and corresponding output values in 24 1000 $2,000 $17,000 $17,000 $17,000 as many columns to the right as you like. Then the VLOOKUP functions takes three or four arguments: (1) $17,000 $17,000 $17,000 $17,000 $17,000 25 1500 -$4,500 $10,500 $25,500 $25,500 the value you want to compare to the values in the left column of the table; (2) the lookup table range; (3) $25,500 $25,500 $25,500 $25,500 $25,500 the index of the column you want the returned value to come from, where the index of the left column is 1, 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 column is 2, and so on; and optionally (4) TRUE (for an approximate match, the 27 2500 -$15,625 -$625 $14,375 $29,375 $44,375 $44,375 $44,375 $44,375 $44,375 default) or FALSE (for an exact match). 28 3000 -$20,250 -$5,250 $9,750 $24,750 $39,750 $54,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 $63,875 If you omit the last argument, the values in the left column of the table must be entered in ascending order. 30 4000 -$30,200 $15,200 -$200 $14,800 $29,800 $44,800 $59,800 $74,800 $74,800 If the last argument is TRUE or omitted, Excel scans down the leftmost column of the table and finds the 31 4500 -$35,850 $20,850 -$5,850 $9,150 $24,150 $39,150 $54,150 $69,150 $84,150 last entry less than or equal to the first argument (in this sense it finds an approximate match). There is also 32 an HLOOKUP function that works exactly the same way, except tha tthe lookup table is arranged in rows, 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 table allows you to see how a single output cell varies as you vary two input cells. Unlike 37 Order quantity Expected profit a one-way data table, only a single output cell can be used. To create this type of table, enter a refence 38 500 $8,000 to the output cell in the top-left corner of the table, enter possible values of the two inputs below and 39 1000 $16,625 to the right of this corner cell, and select the entire table. Then select Data Table from the What if 40 1500 Analysis dropdown on the Data ribbon, and enter references to the cells where the original two input $24,000 Order 2000 to variables live Model + > u + 100% A1 f Ordering decision with quantity discounts T V W YA J $25,500 $35,500 $44,375 $54,750 $63,875 $74,800 $84,150 L M N O P 0 R S me value you want to compare the values in the Ten Corum VI LTE Cave, 2) WE TOUKUp Louie Tange, PI the index of the column you want the returned value to come from, where the index of the left column is 1, the index of the next column is 2, and so on; and optionally (4) TRUE (for an approximate match, the default) or FALSE (for an exact match). If you omit the last argument, the values in the left column of the table must be entered in ascending order. If the last argument is TRUE or omitted, Excel scans down the leftmost column of the table and finds the last entry less than or equal to the first argument in this sense it finds an approximate match). There is also an HLOOKUP function that works exactly the same way, except tha tthe lookup table is arranged in rows, not columns. 4500 0.015 1 A two-way data table allows you to see how a single output cell varies as you vary two input cells. Unlike a one-way data table, only a single output cell can be used. To create this type of table, enter a refence to the output cell in the top-left corner of the table, enter possible values of the two inputs below and to the right of this corner cell, and select the entire table. Then select Data Table from the What-if Analysis dropdown on the Data ribbon, and enter references to the cells where the original two input variables live. A B C D E F G . I 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 $44,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 47 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:B11,E12:F13) is a shorcut for a formula involving the sum of four 54 products: =A10*E12+A11*E13+B10*F12+B11*F13. 55 56 57 58 59 60 Unit Expediting Cost 61 62 Expected Profit 63 $35,000 64 420.000 Model + The Row Input cell corresponds to the values along the top row of the table, and the Column Input cell corresponds to the values along the left-most column of the table. When you click OK, Excel substitutes each pair of input values into these two input cells, recalculates the spreadsheet, and enters the corresponding output value in the table. Remember that a two-way data table has one input (the column input) along the side and another input (the row input) along the top, and it has a single output specified in the top left cell. 100% A1 X Ordering decision with quantity discounts V W YA A B C D E F G H 1 J M N 0 0 R S T U 1 Ordering decision with quantity discounts Range names used: 2 Cost =Model!$B$18 Example1-4 3 Inputs Quantity discount structure CostLookup =Model!$D$5:$E$9 Sam's Bookstore, with many locations across the United States, places orders for all the latest books and then 4 Unit cost - see table to right At least Unit cost Demand =Model!$B$12 distributes them to its individual bookstores. Sam's needs a model to help it order the appropriate number of 5 Regular price $40 0 $24.00 Leftover_price =Model!$B$6 any title. 6 Leftover price $10 1000 $23.00 Order_quantity =Model!$B$9 7 2000 $22.25 Probabilities =Model!$B$35:$J$35 For example, Sam's plans to order a popular new hardback novel, which it will sell for $30. It can purchase any 8 Decision variable number of this book from the publisher, but due to quantity discounts, the unit cost for all books it orders 3000 $21.75 Profit =Model!$B$19 9 Order quantity 2500 depends on the number ordered. If the number ordered is less than 1000, the unit cost is $24. After each 1000, 4000 $21.30 Regular_price =Model!$B$5 the unit cost drops: to $23 for at least 1000 copies; to $22.25 for at least 2000; $21.75 for at least 3000; and to 10 Revenue =Model!$B$17 $21.30(the lowest possible unit cost) for at least 4000. For example, if Sam's orders 2500 books, its total cost is 11 Uncertain quantity Units_sold_at_leftover_price =Model!$B$16 $22.25(2500) = $55,625. 12 Demand 2000 Units_sold_at_regular_price =Model!$B$15 13 Sam's is uncertain about the demand for this book - it estimates that demand could be anywhere from 500 to 14 Profit model This example has a little of everything: VLOOKUP, two-way data 4500. Also, as with most hardback novels, this one will eventually come out inpaperback. Therefore, if Sam's has 15 Units sold at regular price 2000 table, probability and expected value, conditional formatting, any hardbacks left when the paperback comes out, it will put them on sale for $10, at which price it believes all 16 Units sold at leftover price 500 charting, and SUMPRODUCT. But none of these are "frills." They leftovers will be sold. How many copies of this hardback novel should Sam's order from the publisher? 17 Revenue $85,000 are all used to get the job done: identify the best order quantity. Objective: To learn about IF formulas, VLOOKUP, Two-way data tables, and SUMPRODUCT 18 Cost This is true in general: the more tools you have in your toolkit, $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) 22 $29,375 500 1000 1500 2000 2500 3000 3500 4000 4500 The VLOOKUP function is one of Excel's most useful functions. To use it, first create a vertical lookup table, 23 500 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 $8,000 with values to use for comparison listed in the left column of the table and corresponding output values in 24 1000 $2,000 $17,000 $17,000 $17,000 as many columns to the right as you like. Then the VLOOKUP functions takes three or four arguments: (1) $17,000 $17,000 $17,000 $17,000 $17,000 25 1500 -$4,500 $10,500 $25,500 $25,500 the value you want to compare to the values in the left column of the table; (2) the lookup table range; (3) $25,500 $25,500 $25,500 $25,500 $25,500 the index of the column you want the returned value to come from, where the index of the left column is 1, 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 column is 2, and so on; and optionally (4) TRUE (for an approximate match, the 27 2500 -$15,625 -$625 $14,375 $29,375 $44,375 $44,375 $44,375 $44,375 $44,375 default) or FALSE (for an exact match). 28 3000 -$20,250 -$5,250 $9,750 $24,750 $39,750 $54,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 $63,875 If you omit the last argument, the values in the left column of the table must be entered in ascending order. 30 4000 -$30,200 $15,200 -$200 $14,800 $29,800 $44,800 $59,800 $74,800 $74,800 If the last argument is TRUE or omitted, Excel scans down the leftmost column of the table and finds the 31 4500 -$35,850 $20,850 -$5,850 $9,150 $24,150 $39,150 $54,150 $69,150 $84,150 last entry less than or equal to the first argument (in this sense it finds an approximate match). There is also 32 an HLOOKUP function that works exactly the same way, except tha tthe lookup table is arranged in rows, 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 table allows you to see how a single output cell varies as you vary two input cells. Unlike 37 Order quantity Expected profit a one-way data table, only a single output cell can be used. To create this type of table, enter a refence 38 500 $8,000 to the output cell in the top-left corner of the table, enter possible values of the two inputs below and 39 1000 $16,625 to the right of this corner cell, and select the entire table. Then select Data Table from the What if 40 1500 Analysis dropdown on the Data ribbon, and enter references to the cells where the original two input $24,000 Order 2000 to variables live Model + > u + 100% A1 f Ordering decision with quantity discounts T V W YA J $25,500 $35,500 $44,375 $54,750 $63,875 $74,800 $84,150 L M N O P 0 R S me value you want to compare the values in the Ten Corum VI LTE Cave, 2) WE TOUKUp Louie Tange, PI the index of the column you want the returned value to come from, where the index of the left column is 1, the index of the next column is 2, and so on; and optionally (4) TRUE (for an approximate match, the default) or FALSE (for an exact match). If you omit the last argument, the values in the left column of the table must be entered in ascending order. If the last argument is TRUE or omitted, Excel scans down the leftmost column of the table and finds the last entry less than or equal to the first argument in this sense it finds an approximate match). There is also an HLOOKUP function that works exactly the same way, except tha tthe lookup table is arranged in rows, not columns. 4500 0.015 1 A two-way data table allows you to see how a single output cell varies as you vary two input cells. Unlike a one-way data table, only a single output cell can be used. To create this type of table, enter a refence to the output cell in the top-left corner of the table, enter possible values of the two inputs below and to the right of this corner cell, and select the entire table. Then select Data Table from the What-if Analysis dropdown on the Data ribbon, and enter references to the cells where the original two input variables live. A B C D E F G . I 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 $44,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 47 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:B11,E12:F13) is a shorcut for a formula involving the sum of four 54 products: =A10*E12+A11*E13+B10*F12+B11*F13. 55 56 57 58 59 60 Unit Expediting Cost 61 62 Expected Profit 63 $35,000 64 420.000 Model + The Row Input cell corresponds to the values along the top row of the table, and the Column Input cell corresponds to the values along the left-most column of the table. When you click OK, Excel substitutes each pair of input values into these two input cells, recalculates the spreadsheet, and enters the corresponding output value in the table. Remember that a two-way data table has one input (the column input) along the side and another input (the row input) along the top, and it has a single output specified in the top left cell. 100%
