Price-Freight Reduction Analysis In purchasing, we are often confronted with discounts if we buy at a higher volume. However, we know in supply chain management that buying more than we need results in higher holding costs, because we have to pay for and store the inventory. Buying a higher volume reduces the purchase price and freight cost/unit, however, they have to be analyzed against the higher holding costs. Open the corresponding excel spreadsheet Price-Freight Reduction Analysis. The model shows the Monthly holding cost, freight reduction, and price reduction, in percentages, in yellow. It shows the Product purchase price and freight cost in blue and net savings or loss) in green. Louie's Loops is a major manufacturer and has been presented with cost reduction of 10% and a freight reduction of 5%, if they buy 3 months inventory instead of 1 month. Monthly holding cost is 3%, the purchase price is $52 and the Freight cost is $5. The cost of Ownership includes the monthly holding costs, 1 month, for month to month purchase and 3 months, for the discount price and freight purchase. All questions are from the base data. Make sure to reset it. 1. Save the excel spreadsheet with the initial numbers as your 'base'. Turn in a copy of the base spreadsheet 2. Using the initial data, should Louie's Loops take the discounts? 3. If the cost reduction is 2% instead of 10%, should Louie's loops take the discounts? 4. If other data remains at base, at what cost reduction (in whole percents) is the model approximately break even? 5. If the holding cost percentage goes down, which alternative becomes more favorable? B C D E F H K L Monthly Holding Cost 0.00% N 2 1 Freight reduction 0.00% 3 Cost Reduction 0.00% 4 5 6 7 2 Original Conditions: Purchasing month to month sub-total 8 Purchase Price $ Freight Cost $ cost of ownership $ $ 9 10 3 11 12 Price & Freight Reduced: 10% Cost Reduction N Purchase Price New Freight Cost $ month 1 month 2 month 3 sub-total ownership ownership ownership cost cost cost $ $ $ $ Net Savings 13 14 $ $ Price-Freight Reduction Analysis In purchasing, we are often confronted with discounts if we buy at a higher volume. However, we know in supply chain management that buying more than we need results in higher holding costs, because we have to pay for and store the inventory. Buying a higher volume reduces the purchase price and freight cost/unit, however, they have to be analyzed against the higher holding costs. Open the corresponding excel spreadsheet Price-Freight Reduction Analysis. The model shows the Monthly holding cost, freight reduction, and price reduction, in percentages, in yellow. It shows the Product purchase price and freight cost in blue and net savings or loss) in green. Louie's Loops is a major manufacturer and has been presented with cost reduction of 10% and a freight reduction of 5%, if they buy 3 months inventory instead of 1 month. Monthly holding cost is 3%, the purchase price is $52 and the Freight cost is $5. The cost of Ownership includes the monthly holding costs, 1 month, for month to month purchase and 3 months, for the discount price and freight purchase. All questions are from the base data. Make sure to reset it. 1. Save the excel spreadsheet with the initial numbers as your 'base'. Turn in a copy of the base spreadsheet 2. Using the initial data, should Louie's Loops take the discounts? 3. If the cost reduction is 2% instead of 10%, should Louie's loops take the discounts? 4. If other data remains at base, at what cost reduction (in whole percents) is the model approximately break even? 5. If the holding cost percentage goes down, which alternative becomes more favorable? B C D E F H K L Monthly Holding Cost 0.00% N 2 1 Freight reduction 0.00% 3 Cost Reduction 0.00% 4 5 6 7 2 Original Conditions: Purchasing month to month sub-total 8 Purchase Price $ Freight Cost $ cost of ownership $ $ 9 10 3 11 12 Price & Freight Reduced: 10% Cost Reduction N Purchase Price New Freight Cost $ month 1 month 2 month 3 sub-total ownership ownership ownership cost cost cost $ $ $ $ Net Savings 13 14 $ $