1. Merry, the Knight is a bookstore specializing in J. R. R. Tolkien and other fantasy literature. Recently, the bookstore has taken on a side business in New Zealand package tours of the places where the filming for the movie trilogy took place. The last three years results follow: Year Sales in units (tours) 2019 5,000 2020 3,000 2021 6,000 Sales Revenue ($ 11,000 a tour) $ 55,000,000 $ 33,000,000 $ 66,000,000 Expenses Travel costs $ 7,350,000 $ 7,000,000 $ 11,500,000 Meals and lodging 13,500,000 7,500,000 15,000,000 Guide salaries, and exp. 5,000,000 5,250,000 9,000,000 Insurance 7,500,000 7,500,000 7,500,000 Advertising 4,750,000 4,750,000 4,750,000 Net Operating Income $ 16,900,000 $ 1,000,000 $18,250,000 The budget is being written up for the 2022 season, in which demand is expected to stabilize, and the expected number of tours sold is 5800. In order to estimate net operating income for 2022, an analysis of the costs of doing business so far needs to be done. Assuming that the last three years costs can be used (they are typical and carefully compiled), identify each of the company's expenses as either a pure variable, a mixed, or a fixed expense. Using the hi-lo method, separate each of the mixed expenses into fixed and variable elements. Redo the company's expense totals (for all the expenses separately) at the 5800 units sales level, and use it to estimate net operating income. Finally, since the company expects to be able to sell 6,500 tours in 2023, what would you say they should estimate their operating income for that year to be, given the data? A 1 Year 2 Sales in Units (tours) 3 4 Sales Revenue ($11,000 a tour) Expenses 5 Travel costs B C D E F G H J 2019 2020 2021 Income Statement $5,000 $3,000 $6,000 2022 2023 $55,000,000 $33,000,000 $66,000,000 Tours $5,800 $6,500 Sales $63,800,000 $177,067,855 $7,350,000 $7,000,000 $11,500,000 Travel cost 8 Meals and lodging 7 Guide salaries, and exp. $13,500,000 $5,000,000 8 Insurance 9 Advertising $7,500,000 $15,000,000 $5,250,000 $9,000,000 $7,500,000 $7,500,000 $7,500,000 $4,750,000 $4,750,000 $4,750,000 Meals $11,200,000 $12,250,000 $14,500,000 $16,250,000 Guided Salaries Insurance Advertising $8,750,000 $9,625,000 $7,500,000 $7,500,000 $4,750,000 $4,750,000 10 Net Operating Income $17,100,000 $126,692,855 11 Net Operating Income $16,900,000 $1,000,000 $18,250,000 12 13 14 2019 2020 2021 15 Travel cost $1,470 $2,333.33 $1,916.67 Mixed 16 Meals and lodging $2,700 $2,500 $2,500 Pure Variable 17 Guide salaries, and exp. $1,000 $1,750 18 Insurance $7,500,000 $7,500,000 $1,500 $7,500,000 Mixed Fixed 19 Advertising $4,750,000 $4,750,000 $4,750,000 Fixed 20 21 Low 22 Hi $3,000 $6,000 23 24 Travel costs = change in variable costs/change in units 25 1500 per unit 26 Total cost = fixed cost + variable cost 27 $11,500,000 fixed cost + 1500 (6000) 28 Fixed costs = $2,500,000 Guide salaries, and exp. 1250 per tour Total cost = fixed cost + variable cost $9,000,000 fixed cost +$1,250 (6000) Fixed costs $1,500,000 B D E F G H J 1 Year 2019 2020 2021 Income Statement 2 Sales in Units (tours) $5,000 3 Sales Revenue ($11,000 a tour) $55,000,000 $3,000 $33,000,000 $6,000 2022 2023 $66,000,000 4 Expenses Tours Sales $5,800 $6,500 5 Travel costs $7,350,000 8 Meals and lodging $13,500,000 7 Guide salaries, and exp. 8 Insurance 9 Advertising $5,000,000 $7,500,000 $7,500,000 $4,750,000 $4,750,000 $7,000,000 $11,500,000 $7,500,000 $15,000,000 $5,250,000 $9,000,000 $7,500,000 $4,750,000 Travel cost |=(C3/C2)*13 =(C3/C1)*J3 =2500000+(A25=2500000+(A25 Meals =E16*13 =E16*J3 Guided Salaries =1500000+(E25=1500000+(E25 Insurance $7,500,000 $7,500,000 Advertising $4,750,000 $4,750,000 10 Net Operating Income -14-15-16-17-18-19-J4-J5-J6-J7-J8 11 Net Operating Income $16,900,000 $1,000,000 $18,250,000 12 13 14 2019 2020 2021 15 Travel cost =C5/C2 =D5/D2 |=E5/E2 Mixed 16 Meals and lodging |=C6/C2 =D6/D2 |=E6/E2 Pure Variable 17 Guide salaries, and exp. =C7/C2 =D7/D2 =E7/E2 Mixed 18 Insurance =C8 =D8 |=E8 Fixed 19 Advertising =C9 =D9 =E9 Fixed 20 21 Low =D2 22 Hi =E2 23 24 25 = (E5-D5)/(E2-D per unit 26 27 28 Fixed costs Travel costs = change in variable costs/change in units Total cost fixed cost + variable cost = $11,500,000 fixed cost + 1500 (6000) $2,500,000 Guide salaries, and exp. =(E7-D7)/(E2-D2 per tour Total cost fixed cost + variable cost $9,000,000 fixed cost +$1,250 (6000) Fixed costs $1,500,000