Use the HLOOKUP function in cell H11 to calculate freight cost based on the shipping class and
Fantastic news! We've Found the answer you've been seeking!
Question:
Use the HLOOKUP function in cell H11 to calculate freight cost based on the shipping class and state to which the shipment is going. Freight Cost can be referenced on the "Freight Cost" lookup table (range E5:N8). Be sure to use appropriate relative and absolute cell references.
Hint: You will need to use a function within the HLOOKUP function to determine the appropriate row. Please provide formula .
Transcribed Image Text:
A B D E G H K. L 1 When shipping products two major costs are incurred: the cost of insuring the item and the actual freight cost. Insurance is dependent on the value of 4 the shipment 5 Quickstove has an agreement with shippers for a fixed rate that is 6 determined by destination state and service level (regular, express or 7 overnite). Freight Cost AZ WA NV UT CA MT OR CO HI 3.00 $ 5.00 $ 4.70 $ 7.60 $ 8.40 S Regular 5.00 $ 6.00 $ 5.00 $ 6.00 $ 5.00 ($ 8.00 8.40 $ 7.60o ($ 13.50 13.70 $ 13.70 I$ 11.60) $ 17.10 6.00 $ Express 7.60 $ 8.40 $ 7.60 $ Overnite 11.60 $ 13.70 $ 11.60 $ 7.80 $ 11.60 $ Shipping Insurance Shipping Insurance Cost 10 OrderiD Ship To ProductID Product Name Value Class Cost Freight Cost Total Shipment Insurance $ 68.80 Express $ 86.00 Express $ 120.39 Regular 11 1003 AZ 100 QuickStove 2.50 Value Cost 1004 1007 12 WA 100 QuickStove 2.50 $ 0.50 $ $ 120.39 Express $ 17.55 Overnite $ 4.00 Overnite $ 69.98 Overnite $ 274.32 Overnite $ 274.32 Express $ 15.68 Regular $ 69.98 Overnite $ 60.80 Regular $ 34.99 Overnite $ 19.60 Overnite $ 4.00 Overnite 13 NV 100 QuickStove 3.00 24 10 $ 1.00 UT 100 QuickStove 30 | $ 2.00 50 s 14 1011| 3.00 15 1027 CA 100 QuickStove 1.00 2.50 1041 102 Replacement Fuel Cells 104 Quick Stove Can 104 Quick Stove Can 104 Quick Stove Can 102 Replacement Fuel Cells 104 Quick Stove Can 16 MT 0.50 $ 100 $ 3.00 17 1042 CA 2.50 $ 200 $ 4.00 18 1050 OR 4.00 400 $ 5.00 19 1058 UT 4.00 20 1059 AZ 1.00 21 1060 WA 2.50 22 1062 UT 102 Replacement Fuel Cells 2.50 23 1073 CO 104 Quick Stove Can 2.00 1074 102 Replacement Fuel Cells 102 Replacement Fuel Cells 24 CO 1.00 25 1088 WA 0.50 A B D E G H K. L 1 When shipping products two major costs are incurred: the cost of insuring the item and the actual freight cost. Insurance is dependent on the value of 4 the shipment 5 Quickstove has an agreement with shippers for a fixed rate that is 6 determined by destination state and service level (regular, express or 7 overnite). Freight Cost AZ WA NV UT CA MT OR CO HI 3.00 $ 5.00 $ 4.70 $ 7.60 $ 8.40 S Regular 5.00 $ 6.00 $ 5.00 $ 6.00 $ 5.00 ($ 8.00 8.40 $ 7.60o ($ 13.50 13.70 $ 13.70 I$ 11.60) $ 17.10 6.00 $ Express 7.60 $ 8.40 $ 7.60 $ Overnite 11.60 $ 13.70 $ 11.60 $ 7.80 $ 11.60 $ Shipping Insurance Shipping Insurance Cost 10 OrderiD Ship To ProductID Product Name Value Class Cost Freight Cost Total Shipment Insurance $ 68.80 Express $ 86.00 Express $ 120.39 Regular 11 1003 AZ 100 QuickStove 2.50 Value Cost 1004 1007 12 WA 100 QuickStove 2.50 $ 0.50 $ $ 120.39 Express $ 17.55 Overnite $ 4.00 Overnite $ 69.98 Overnite $ 274.32 Overnite $ 274.32 Express $ 15.68 Regular $ 69.98 Overnite $ 60.80 Regular $ 34.99 Overnite $ 19.60 Overnite $ 4.00 Overnite 13 NV 100 QuickStove 3.00 24 10 $ 1.00 UT 100 QuickStove 30 | $ 2.00 50 s 14 1011| 3.00 15 1027 CA 100 QuickStove 1.00 2.50 1041 102 Replacement Fuel Cells 104 Quick Stove Can 104 Quick Stove Can 104 Quick Stove Can 102 Replacement Fuel Cells 104 Quick Stove Can 16 MT 0.50 $ 100 $ 3.00 17 1042 CA 2.50 $ 200 $ 4.00 18 1050 OR 4.00 400 $ 5.00 19 1058 UT 4.00 20 1059 AZ 1.00 21 1060 WA 2.50 22 1062 UT 102 Replacement Fuel Cells 2.50 23 1073 CO 104 Quick Stove Can 2.00 1074 102 Replacement Fuel Cells 102 Replacement Fuel Cells 24 CO 1.00 25 1088 WA 0.50
Expert Answer:
Answer rating: 100% (QA)
The formuale to be used is the hlookup and inside th... View the full answer
Related Book For
Posted Date:
Students also viewed these programming questions
-
Jack Turner is your client. He recently graduated from College and is considering purchasing a business. While in College, Jack drove a cab to pay his way through school. Jack learned a great deal...
-
Ksp for PbCrO4 = 2.8 x 10-13 at 25 C. Which of the following statements about the concentration of the ions in PbCrO4 solutions is always true whatever its molarity? [Pb+ (aq)]5.2 x 10-7 mol L-1 and...
-
Give the line and angle formulas for the following compounds: a) (CH3)2CHCH(C2H5)CH2CH2CH3 b) CH2=C(C2H5)CH2C(CH3)2CH3 c) (CH3)2C=CHC(C2H5)=CH2 d) CH3(CH2)30COCH3CH2NH2 e) (CH3)3CC=CCH2CH=C(CH3)2...
-
Sparkle is one firm of many in the market for toothpaste, which is in long-run equilibrium. a. Draw a diagram showing Sparkles demand curve, marginal-revenue curve, average total- cost curve, and...
-
Using the Heisenberg uncertainty principle, calculate x for each of the following. a. An electron with v = 0.100 m/s b. A baseball (mass = 145 g) with v = 0.100 m/ s How does the answer in part a...
-
Distinguish between sampling for attributes and sampling for variables.
-
It has been estimated that the new product just presented by your Research and Development (R&D) department will bring in net cash flows of $80,000 per year for four years. Marketing will begin...
-
Pickens Corporation declares and issues a 10% stock dividend. On the date of declaration, the stock is selling for $30 per share. Assuming that there were 50,000 shares previously outstanding, with a...
-
The following direct materials data pertain to the operations of Monty Co. for the month of December. Standard materials price $5.00 per pound Actual quantity of materials purchased and used 16,790...
-
City Youth Services (CYS) is a not-for-profit organization established to provide counseling and other services to children under the age of 18. It concentrates on troubled teenagers who are...
-
What measures could the Big Four firms take to mitigate the risk their partners will improperly share confidential client information with third parties? Would these measures be cost-effective? Why...
-
You have been engaged by the trustees of Roger Trust to audit the trust as of December 31, 19XX. The audit is for the purpose of assuring the trustees and beneficiaries that trust assets,...
-
Jordan \& Stone, CPAs, audited the financial statements of Tech Co., a nonpublic entity, for the year ended December 31, 19X1, and expressed an unqualified opinion. For the year ended December 31,...
-
John Wilguess, CPA, has been engaged to review the financial statements of Lucky Oil Company, a nonpublic company, for the year ended June 30, 19X4. The previous year's financial statements of Lucky...
-
a. In what forms may a CPA firm be organized? b. In what four groups are CPA firms often classified?
-
Your client, Byrd and Byrd, Inc., a regional trucking company, has prepared a financial forecast for the coming year and has asked you to examine it and issue a report to accompany it. Management's...
-
During Year 3, Anywhere, Inc. (Al) incurred the following product costs. Raw materials Labor Overhead $ 82,000 104,214 68,000 The Year 2 ending balance in the Work in Process (WIP) account was...
-
In Exercises, find the equation of the tangent line at the given point on each curve. 2y 2 - x = 4; (16, 2)
-
The marginal costs, average variable costs (AVC), and average total costs (ATC) for a firm are shown in Figure 13P-2. In the figure, mark the quantity the firm will choose to produce in the short run...
-
Describe an economic model you know. What assumptions does the model make? Are the assumptions reasonable?
-
Suppose a city has a chain of fitness centers (gyms) all owned by the same company, Fit Fun. A new company is considering opening a gym in the city. Give an example of an aggressive tactic Fit Fun...
-
What are Porters generic strategies?
-
What is the link between the balanced scorecard and strategy?
-
How can the lifecycle approach be used to maximise return from a product?
Study smarter with the SolutionInn App