Waverly Pool Toys Corporation manufactures a variety of pool toys. Waverly has just received a special order for a large volume of pool toys at reduced special order prices. The following is a screenshot of the special order in an Excel file in a worksheet named SpecialOrder.

To follow is a sample from Waverly’s job cost records from the most recent month from a worksheet named JobCostRecords. The list is sorted chronologically with the most recent orders first. This JobCostRecords worksheet contains 445 job cost records.

Answer the following questions to help Waverly management decide if the special order should be accepted.
1. What Excel function would be useful for obtaining the most recent variable cost from the job cost records for each of the models in the special order?
2. What would you enter in each of the following fields in the dialog box for the formula needed in Cell F2 in the SpecialOrder worksheet to obtain the most recent contribution margin per pack for the model in Row 2? Make the cell references in the Table_array field absolute references.

a. Lookup_value

b. Table_array

c. Col_index_num

d. Range_lookup 3. What formula would you enter in Cell G2 in the SpecialOrder worksheet to calculate the contribution margin per pack for the model in Row 2?
4. What formula would you enter in Cell H2 in the SpecialOrder worksheet to calculate the total contribution margin for the model in Row 2?
5. How could you copy the formulas in Cells G2 and H2 down to the remaining rows in the SpecialOrder worksheet?
6. What formula would you enter in Cell H12 in the SpecialOrder worksheet to calculate the total contribution margin for the special order?
7. Should the company accept this special order?

Transcribed Image Text:

B D E G H Price per Total Variable cost Contribution Total 1 Model # Quantity pack Revenue per pack margin per pack contribution margin 2 FM-991 Foam noodle, variety, 50-pack 50 $ 40 $ 2,000 3 FL-030 Inflatable small floats, 20-pack 50 $ 72 $ 3,600 4 FL-060 Inflatable medium floats, 10-pack 50 $ 45 $ 2,250 5 FL-120 Inflatable large floats, 5-pack 50 $ 45 $ 2,250 6 DR-580 7 BB-329 Diving ring set, 10-pack Beach balls, 50-pack 100 $ 25 $ 2,500 100 $ 50 $ 5,000 8 FR-250 Foam rafts, regular, 20-pack 50 $ 59 $ 2,950 9 FR-760 Foam rafts, premium, 10-pack 50 $ 60 $ 3,000 10 BP-100 Baby pool toys, variety 6-pack 50 $ 40 $ 2,000 11 FC-900 Floating cup holders, 50-pack 100 $ 50 $ 5,000 12 $ 23,550