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...
-
Repeat Exercise 3.13 using the PDF. A certain random variable has a probability density function of the form f x (x) = ce 2xu(x). Find the following: (a) The constant c, (b) Pr (X >2), (c) Pr (X <...
-
Two concrete spans of a bridge of length L are placed end to end so that no room is allowed for expansion (Fig. P19.61a). If a temperature increase of T occurs, what is the height y to which the...
-
The temperature dependence of the activity coefficient can be expressed as (a) \(\left(\frac{\partial \ln \gamma_{i}}{\partial T} ight)_{P}=\frac{\bar{H}_{i}-H_{i}}{R T^{2}}\) (b)...
-
Record in T accounts the following stock transactions of Pigua Corporation, which represent all the companys treasury stock transactions during 2011: May 5Purchased 1,600 shares of its own $2 par...
-
Explain the mechanisms and challenges involved in implementing file systems in distributed operating environments, including issues related to data consistency, fault tolerance, and distributed file...
-
The results below are based on data collected as part of a student project for an introductory statistics class at UF. If there is more than one group/treatment, the first group mentioned is group 1....
-
Audits of financial statements are designed to obtain reasonable assurance of detecting misstatement due to: Fraudulent Financial Reporting : Misappropriation of Assets A. Yes : Yes B. Yes : No C. No...
-
What is two-stage cluster sampling? What are the benefits and disadvantages of this technique compared to other sampling techniques?
-
A marketing manager believes that advertising expenditures are effective in increasing sales only to a certain extent. He discovers that when the advertising expenditures exceed a certain level,...
-
What are the pitfalls of a good/better/best product and pricing model?
-
Explain how we can use the ratio method to improve the parameter estimates based on sample data.
-
Can you achieve excellence in CVM without cross-functional engagement?
-
Explain the Income Recognition Principle according to Generally Accepted Accounting Principles.
-
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...
-
Given a forecast of 1,405 and an actual outcome of 1,670, what is the error in the forecast? What is the percent error?
-
Given a forecast of 2,105 and an actual outcome of 1,980, what is the error in the forecast? What is the percent error?
-
Here are the errors associated with a particular forecast over the past 5 months, in chronological order: 5, 10, -15, 0, 8. In which month was the forecast perfectly accurate? In which month was the...
Study smarter with the SolutionInn App