Question
Please provide formulas i need Problem 4: Estimates A company purchases Olive oil according to this...
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
Estimates - Excel Data Review View Page Layout Formulas H AutoSave Off File Home Insert X Cut ЦЬ Copy Paste Format Painter Clipboard Calibri 11. AA== BIU - BA Font SE Alignme F22 A 1 Purchase Estimates: B C D E Ordered: Price Qty Total 3 Purchase Price Schedule: 14 First 1000 Ltrs. Tier1: 5 Next 3000 Ltrs. Tier2: 6 Above 4000 Ltrs. Tier3: @ @ @ Total: Total Price:
5/9 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: 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 Preschedule aty Price Tier 1.000 lel 7.10 Tier 1.000 The P 5 .10 14.300 Total Price Figure 6 - Scenario: Tier 3 Ordered >4000) Purchase Estimates Ordered: 2.900 1,000 Perchase Price schedule: Furst 1000 LET. Ter Next 1000 Lt. Above 4000 LOL Teri Total 2.900 Figure 7 - Scenario2: Tier 2 Ordered (0-4000) Parchase times Ordered: 900 harthane Nie wchedule
Answers
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 Total3) 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: 74) 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 Total5) 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 Total7) 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
Similar Solved Questions
1 answers
What is the inverse function of #f(x)= (3x-2)/(x+7)?
What is the inverse function of #f(x)= (3x-2)/(x+7)?...
1 answers
Read Nurse Staffing and Inpatient Hospital Mortality. Write a Memorandum (no more than 2 pages) directed to your faculty...
Read Nurse Staffing and Inpatient Hospital Mortality. Write a Memorandum (no more than 2 pages) directed to your faculty answering the questions: What questions the did researchers try to answer...
1 answers
Suppose that all existing firms in a long-run competitive market equilibrium are identical and have the...
Suppose that all existing firms in a long-run competitive market equilibrium are identical and have the following cost function C(Q)= 1002 with MC(Q)=2Q. Suppose also that market demand is given by P(Q)=A-0.04Q, where A-40.0. What is the equilibrium market quantity? No units, no rounding. Your Answe...
2 answers
In biochemistry, we use a 'Bradford Assay' to calculate the concentration of protein in a sample....
In biochemistry, we use a 'Bradford Assay' to calculate the concentration of protein in a sample. This method uses the same principles of the standard curve from this experiment. In a Bradford Assay, a standard curve is prepared using a known protein, typically Bovine Serum Albumin (BSA), bu...
1 answers
How do I check numbers that are valid for each bases ? Please show the method...
How do I check numbers that are valid for each bases ? Please show the method and specify steps 4. Which of the following would be valid numbers for bases 3, 7, 8, 13, 15 and 16? a. DEADBEAF b. DC4A c. 122 d. 1689 e. 1337 f. 888...
1 answers
Required information [The following information applies to the questions displayed below.] The following information is available...
Required information [The following information applies to the questions displayed below.] The following information is available for Lock-Tite Company, which produces special-order security products and uses a job order costing system. April 30 May 31 $ $49,000 9,800 60,000 58,000 20,900 33,200 Inv...
1 answers
9. (10 points) Use the principle of induction to prove the following formula. For all nen...
9. (10 points) Use the principle of induction to prove the following formula. For all nen Ü(21 – 1) =n? n 10. (10 points) Prove the following inequality using the modified principle of induction. For all k e N, if k > 4, then 2 <k!...
1 answers
High Country, Inc., produces and sells many recreational products. The company has just opened a new...
High Country, Inc., produces and sells many recreational products. The company has just opened a new plant to produce a folding camp cot that will be marketed throughout the United States. The following cost and revenue data relate to May, the first month of the plant’s operation: Beginning in...
1 answers
Retail price data for n = 60 hard disk drives were recently reported in a computer magazine....
Retail price data for n = 60 hard disk drives were recently reported in a computer magazine. Three variables were recorded for each hard disk drive: y = Retail PRICE (measured in dollars) x 1 = Microprocessor SPEED (measured in megahertz) (Values in sample range f...
1 answers
11. Thurman Comfort Specialists, Inc. reported the following stockholders' equity on its balance sheet at June...
11. Thurman Comfort Specialists, Inc. reported the following stockholders' equity on its balance sheet at June 30, 2018: (Click the icon to view the partial balance sheet.) Read the requirements Requirement 1. Identify the different classes of stock that Thurman Comfort Specialists has outstandi...
1 answers
Presented below is the condensed financial statements of Raleigh Airlines Ltd. for the year ended December...
Presented below is the condensed financial statements of Raleigh Airlines Ltd. for the year ended December 31, 2018. Consolidated Statement of Financial Position At December 31 (Stated in thousands of Canadian dollars) 2018 2017 Assets Currents Assets: Cash and Cash Equivalents 1185806...
1 answers
I need sub-questions E,F,G,H answered. thank you E) Name a practical reason for LTM to use...
I need sub-questions E,F,G,H answered. thank you E) Name a practical reason for LTM to use LIFO. F) If LTM were considering a switch from FIFO to LIFO, it would have to be concerned with the LIFO conformity rule. Explain. G) Assume LTM uses LIFO and the same number of units were sold. Would the comp...
1 answers
4.6 (20 pts) Solve the initial value problems for the Bernoulli equation. (a) xy+y=x*y; y(1) =...
4.6 (20 pts) Solve the initial value problems for the Bernoulli equation. (a) xy+y=x*y; y(1) = 1/4; (b) xy + 3y = rºy?, y(1) = 1/2....
1 answers
Discuss the advantages and disadvantages of Monte Carlo analysis in project risk management. Should Monte Carlo...
Discuss the advantages and disadvantages of Monte Carlo analysis in project risk management. Should Monte Carlo analysis be used in every project? Why or why not?...
1 answers
Question 3 (PLO-2,CLO-2,C3) a) Solve the orthogonal trajectories of the following curves. r = cy2 b)...
Question 3 (PLO-2,CLO-2,C3) a) Solve the orthogonal trajectories of the following curves. r = cy2 b) Solve the following differential equation. (x2 +3xy + y2)dx – xdy =...
1 answers
Identify concepts of the nursing metaparadigm ( remember the selected theory may not include all four concepts)
Identify concepts of the nursing metaparadigm ( remember the selected theory may not include all four concepts)...
1 answers
V-0.0 1. An electron is accelerated from rest to a final speed of 1.50 x 106....
V-0.0 1. An electron is accelerated from rest to a final speed of 1.50 x 106. It begins at a location where the electric potential is zero. a) What is the final kinetic energy of the electron? (4) v = 1.50 x 10m's - b) What is the value of the electric potential (V) at the electron's final p...