Question

Problem 4: Estimates A company purchases Olive oil according to this price schedule For the first 1000 Litres, $7.10 per Litre For any of the next 3000 Litres $6.60 per Litre For any oil beyond 4,000 Litres, $5.80 per Litre The Purchase Estimate spreadsheet calculates the total price of buying x Litres of oil, where x is a number input to a cell (Ordered) on the worksheet with values that may fall into any or all of the 3 Tiers of pricing. Re-create the model as per below The model can handle any/all 3 tiers of order scenarios 2. Format with differentiating colors as per example below. 1. Hint: Key functions to use: IF Here are 3 possible output examples you can use to test your solutions Purchase Estimates Ordered: 6,500 Purchase Price schedule Tiert: Tier2 Tiers gty 1000 Price Total 7,300 15,800 First 1000 trs 7.30 3,000e 2.300 Le Next 3000 Ltrs 600 Above 4000 Ltrs S.80 14.500 Total 41400 6.500 Total Price Figure 6-Scenario1: Tier 3 Ordered (4000 Purchase Estimates Ordered: 2,900 Total 7.100 Purchase Price schedule Tiert Tier2 Ther Qty Price First 1000 Ltrs 1000 1300 7.30 12.540 6.00 Next 3000 Ltrs le 5.80 Above 4000 Ltrs Total Price 18.640 Total: 2900 Figure 7-Scenario2: Tier 2 Ordered (0-4000) Purchase Estimates Ordered 900 Purchase Price schedule: First 1000 Ltrs Next 3000 Ltrs ove 4000 Ltrs Total gty Price Tert Tier2 Tiers 2.30 600 S80 Total Price 6390 s00 Total Figure 8-Scenorio: Tier 1 Ordered (0-1000 CCC

Answer 1

Use provided formula, as is, on Qty cellagainst the Tiers (See the selected cell and function area in screen shots)

Tier1 - Qty cell

Tier1 - Qty cell	=IF($F2>1000,1000,$F2) fox IF($F2>1000,1000,$F2) C4 A C D E F 1 Purchase Esitmates Ordered: 2 8500 Purchase Price Schedule Qty 4 First 1000 Ltrs Price Total Tier1 1000 7100 7.1 LL
Tier2 - Qty Cell	=IF($F2 > 4000,3000,IF($F2<1000,0,$F2-1000)) IF($F2 4000,3000,IF($F2<1000, 0, $F2-1000)) C5 A В C D E G Н 1 Purchase Esitmates Ordered: 2 8500 Purchase Price Schedule Qty Total Price 3 Tier1 4 First 1000 Ltrs 1000 7.1 7100 5 Next 3000 Ltrs 3000 Tier2 6.6 19800
Tier3` - Qty Cell	=IF($F2>4000,($F2-4000),0) IF($F2>4000,($F2-4000), 0) fox X Сб A В C D E F 1 Purchase Esitmates Ordered: 2 8500 Purchase Price Schedule Qty Tier1 Total Price 3 4 First 1000 Ltrs 1000 7.1 7100 Tier2 3000 6.6 5 Next 3000 Ltrs 19800 26100 6 Above 4000 Ltrs Tier3 4500 5.8 Total 8500 Total Price 7 53000 LL

Once done, you can use following formula for cells under total column

=$C4*$E4 , C is QTY column and E is PRICE column, Make sure that you change it according to your excel sheet.

put the equation in total cell of Tier 1 and, click and drag, formula will be automatically copied and updated.

sC4 SE4 X SUM А В C D F 1 Purchase Esitmates Ordered: 2 8500 |Total 7.11-$C4 $E4 Purchase Price Schedule Qty Tier1 1000 Tier2 Tier3 Total 8500 |Price 3 4 First 1000 Ltrs 3000 5 Next 3000 Ltrs 6.6 19800 26100 Above 4000 Ltrs 6 4500 5.8 Total Price 7 53000

Purchase Esitmates Ordered: 1001 Purchase Price Schedule Qty Tier1 Tier2 Tier3 Total 1001 Price Total 1000 7100 First 1000 Ltrs Next 3000 Ltrs Above 4000 Ltrs 7.1 6.6 6.6 0 5.8 0 Total Price 7106.6

Purchase Esitmates Ordered: 10 Purchase Price Schedule Qty First 1000 Ltrs Next 3000 Ltrs Above 4000 Ltrs Price Total Tier1 Tier2 Tier3 10 7.1 71 6.6 0 5.8 0 Total Total Price 10 71

Purchase Esitmates Ordered: 4000 urchase Price Schedule Qty Tier1 Tier2 Tier3 Total 4000 Total Price 1000 First 1000 Ltrs Next 3000 Ltrs Above 4000 Ltrs 7.1 7100 3000 6.6 19800 0 5.8 0 Total Price 26900

Purchase Esitmates Ordered: 8500 Purchase Price Schedule Qty Total Price First 1000 Ltrs Tier1 1000 7.1 7100 Next 3000 Ltrs Above 4000 Ltrs 3000 Tier2 19800 6.6 Tier3 Total 8500 4500 5.8 26100 Total Price 53000