TravelTime Bags manufactures a variety of bags. Travel Time has just received a special order for a large volume of bags 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 Travel Time’s job cost records from the most recent month from a worksheet named Job Cost Records. The list is sorted chronologically with the most recent orders first. This job cost records worksheet contains 408 job cost records.

Another worksheet contains descriptions for each of the model numbers. The following screenshot is from the Descriptions worksheet.

Answer the following questions to help TravelTime management decide if the special order should be accepted.

1. What Excel function would be useful for obtaining the description from the Descriptions worksheet for each of the models listed in the Special Order worksheet and 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 B2 in the Special Order worksheet to fill in the description for the model in Row 2? The cell references in the Table_array field should be absolute references.

a. Lookup_value
b. Table_array
c. Col_index_num
d. Range_lookup

3. What would you enter in each of the following fields in the dialog box for the formula needed in Cell F2 in the Special Order worksheet to obtain the most recent variable cost per bag for the model in Row 2? The cell references in the Table_array field should be absolute references.
a. Lookup_value
b. Table_array
c. Col_index_num
d. Range_lookup

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

Transcribed Image Text:

A 1 Model # 2 R9687 3 G05489 4 G7894 5 R7894 6 C4547 7 B4587 8 R4861 9 R98547 10 B9876 11 C98753 12 B Description с Quantity Price per bag 75 $ 75 $ 50 $ 150 $ 75 $ D 50 $ 100 $ 80 $ 50 $ 50 $ Total Revenue 105 $ 35 $ 55 $ 47 $ 25 $ 45 $ E 84 $ 100 $ 30 $ 40 $ 7,875 2,625 2,750 7,050 1,875 2,250 8,400 8,000 1,500 2,000 Variable Contribution cost per bag margin per bag H Total contribution margin