Question

please provide formulas i need

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 56.60 per litre For any oil beyond 4,000 Litres, 55 80 per litre The Purchase Estimate spreadsheet calculates the total price of buying x litres of oil, wherexia 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: 1. The model can handle any/all 3 tiers of order scenarios 2. Format with differentiating colors as per example below. 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 scheduler Oy 1.000 lel First 1000 . t3000 Ltr. Tieri: Tier2 To Figure 6 - Scenario1: Tier 3 Ordered>4000) Purchase Estimates: Ordered: 2.900 Purchase Price schedule First 1000 Lt. Tert: Next 1000 Lt. Tera: Tier 1.000 el 1.100 lei Total Total Price Figure 7 - Scenario2: Tier 2 Ordered (0-1000) Purchase Estimates Ordered: 900 IT Next 1000 Lt. TP Figure 8 - Scenario: Tier 1 Ordered (0-1000

Answer 1

1) Determine the quantity of tier 1: fIF(D1<1000, D1,1000) сз в c D E Ordered: 6,500 1 Purcahse price Tiers Quantity Price Total schedule 2 3 First 1000 Ltrs 1000.00 $7.10 $6.60 $5.80 Tier 1 Tier 2 4 Next 3000 Ltrs. Above 4000 Ltrs Tier 3 5 6 Total 2) Determine the quantity of tier 2 IF(D8< 1000,0,1F(AND(D8>1000,D8 <4000), (D8-1000),3000) ) f C11 A В C D E F G Н Ordered: 6,500 Purcahse price Quantity Tiers Price Total schedule 9 10 First 1000 Ltrs $7.10 1000.00 3,000 Tier 1 11 Next 3000 Ltrs. Tier 2 $6.60 Tier 3 $5.80 12 Above 4000 Ltrs. 13 Total

3) Determine the quantity of tier 3: fIF($F$2 <= 4000,0, ($ F$2-4000)) Сб A В C E F 1 Purchase Estimates: Ordered: 2 6,500 Total Price Qty 1,000 @ 3,000@ 2,500 Purchase Price schedule Tier1: Tier2: Tier3: Total: 4 First 1000 Ltrs. 7.10 5 Next 3000 Ltrs. 6.60 6 Above 4000 Ltrs 5.80 Total Price: 7

4) Determine the total amount of tier 1 fC26 D26 E26 B c D A Ordered: 6,500 24 Purcahse price Tiers Quantity Price Total schedule 25 $7.10 $7,100 $6.60 $5.80 26 First 1000 Ltrs 27 Next 3000 Ltrs Tier 1 Tier 2 Tier 3 1000 3,000 2,500 28 Above 4000 Ltrs. 29 Total

5) Determine the total amount of tier 2: frC35*D35 Е35 В C E A Ordered: 6,500 32 Purcahse price schedule Quantity Price Tiers Total 33 34 First 1000 Ltrs 35 Next 3000 Ltrs 36 Above 400O Ltrs Tier 1 Tier 2 Tier 3 1000 3,000 2,500 $7.10 $7,100 $6.60 19,800 $5.80 14,500 37 Total 6) Determine the total quantity of three tiers: f SUM(C42:C44) C45 A в C D E Ordered: 6,500 40 Purcahse price Tiers Quantity Price Total schedule 41 1000 3,000 2,500 6,500 $7.10 S7,100 $6.60 19,800 $5.80 14,500 $41,400 42 First 1000 Ltrs Tier 1 43 Next 3000 Ltrs. Tier 2 Tier 3 44 Above 4000 Ltrs. 45 Total

7) Determine the ordered quantity of the tiers: A в C E Ordered: 2,900 48 Purcahse price schedule 50 First 1000 Ltrs 51 Next 3000 Ltrs 52 Above 400O Ltrs Tiers Quantity Price Total 49 $7.10 $7,100 $6.60 12,540 1,000 1,900 Tier 1 Tier 2 Tier 3 $5.80 0 $19,640 2,900 53 Total 8) Determine the ordered quantity of the tiers: foxOrdered: 900 D55 A В c D E Ordered: 900 55 Purcahse price Tiers Quantity Price Total schedule 56 57 First 1000 Ltrs 58 Next 3000 Ltrs 59 Above 4000 Ltrs 900 $7.10 $6,390 Tier 1 $6.60 Tier 2 Tier 3 0 0 $5.80 0 $6,390 60 Total 900