Homework #3 Quantitative Business Analysis (MHR381) 3 Homework number Due date Additional files needed None Excel functionality addressed: Learn how to use Excel to evaluate the profitability of different products. Business topic addressed: Evaluate the profitability of three products sold by a company. Realize that multiple profitability measures can be used for different reasons. Also understand that the profitability of a product is affected by decisions such as how to account for costs. This case illustrates how profit is affected by whether you designate costs as fixed or variable. Tish Kanui helps manage guest services at Royal Kona Resort, a resort overlooking Kailua Bay on the main island of Hawaii. Part of Tish's job is to operate Royal Kona's three boat tours. Tish has asked you to help her evaluate the boat tours in terms of profitability. Royal Kona has invested in these tours as a means of offering more services to their guests. Royal Kona management would now like to financially assess the tours. This assessment will help determine whether to continue operating all three tours, because unprofitable tours could be discontinued. Also, Royal Kona guests frequently ask the resort concierge to recommend a tour, and so the Royal Kona can influence which tour is taken. If one tour generates more profit per passenger than the other tours, then that tour should be recommended. WHAT TO DO: Step 1: Open a blank Excel spreadsheet and enter the following information: B D 1 2018 Royal Kona Tour Information 2 Tour 3 Price per passenger Lanai Villager $95 $85 Sunset $110 The three tours are named for the three boats: the Lanai, the Villager, and the Sunset. Each tour lasts for almost one full day, and some food is provided for passengers. The Lanai tour runs near the coastline and stops twice. During these stops guests receive a guided tour through some of Hawaii's scenery. The Villager tour makes three 91-minute stops at small villages during the day and passengers eat most of their food off of the boat while they shop. Finally, the Sunset journeys a bit further into the Pacific and does not stop during the day. The price-per-passenger information in row-3 reflects the price each guest pays for the one-day tour, and this price includes food charges. Thus, a couple touring on the Lanai would pay $170, plus any tips or gratuities paid directly to the tour-guide. HW3-1 Homework #3 Step-2: Royal Kona pays a variety of costs to operate the boats. At this point you will enter the fixed cost is a variable cost. When an additional passenger pays to take the Lanai tour, the Royal fixed costs. A fixed cost is a cost that remains fairly stable regardless of sales. The opposite of a Kona receives an extra $85 in sales revenue. The Royal Kona will also pay for the passenger's food and for fuel and other costs associated with the tour, and these costs are not fixed because they rise and fall, or vary. In cell A4 enter Fixed Costs (annual)", and then in the next five rows, cells A5-A9, enter five types of costs, one cost per row, and indent the names of these costs. The costs include: boat depreciation, boat maintenance, insurance, share of hotel costs, and advertising. The three boats are depreciated, meaning the purchase costs are spread out over multiple years. captain or tour-guide. Royal Kona expects to use the Lanai for 10 years and then sell the boat for S600,000. Thus, over the ten years, the accounting cost of the boat is $1,100,000 ($1,700,000 $600,000), and the depreciation is expensed evenly over these years, so that the cost per year is $110,000. The Villager and Sunset each seat up to 32 passengers and cost $1.00 million a piece which is also depreciated evenly over the 10 years. No salvage value is expected for these two boats, so each has an annual depreciation of $100,000. Enter the annual depreciation amounts for all three boats in cells B5-D5. Royal Kona pays to maintain and repair each boat, and in 2018 these costs totaled $37,000 for the Lanai, $22,000 for the Villager, and $29,000 for the Sunset. Enter these amounts in the cell range B6-D6. Royal Kona pays insurance for each boat, and in 2018 these insurance costs were $5,000 for each boat. So enter $5,000 in the cells B7-07. Next, costs needed to support the boat tours include a portion of Tish's salary, occasional support from other resort employees, and use of resort phones and computers. Management's best guess is that, collectively, these costs total about $69,000. For convenience, this cost is divided equally between the three boats, so each boat incurs an annual fixed cost of $23,000 that you should enter in cells B8-DS. Finally, the tours are advertised with separate brochures and mailings. Total costs are $40,500 per year, again divided equally among the three boats. So enter $13,500 to denote these costs in cells B9-D9. And before moving further, format your cells. Make sure text is aligned above numbers, and use comma separators in cells B5-D9. To insert commas in these cells, highlight the cell range and then in the Number group on the Home ribbon, click on the ","icon. Next, click on the arrow in the right corner to open up the dialog box. On the Number tab, select Number, zero decimal places, and check the box that indicates whether to use commas. Then click OK. Also use dollar signs in row 5 by selecting just cells BS-D5 and from the menu bar again select the "" icon under the Number group. Again, click on the arrow in the right corner of the Number group. This TINA 2 Homework #3 time on the Number tab select Currency, zero decimal places, and put a dollar sign in the Symbol field. Step 3: Now enter the variable cost information. The boat tours entail added costs each time the tour is operated, so costs generally correspond to the number of tours conducted, not with each extra passenger. In cell A10 enter "Variable Costs (per trip)". Four variable costs are involved, and enter one cost in each of cells All-A14, indenting each heading as you did with the fixed costs. The four variable costs include: fuel, tour guide cost, passenger food, and docking fees. So enter the following information to account for these costs in cells BIT-D14: Fuel: the mean fuel cost per trip is $200 for the Lanai, $175 for the Villager, and $325 for the Sunset. Tour guide cost: tour guides for the Lanai cost more because they must lead on-shore tours, and the Royal Kona pays $165 per trip for a tour-guide on this boat. The tour guide cost per trip for the Villager is $100 and for the Sunset is $130. Passenger food: the mean amount spent on food per trip during 2018 was $225 for the Lanai, $150 for the Villager (because most food is purchased on shore), and $400 for the Sunset. Docking fees: the Lanai is charged $75 per trip and the Villager is charged $95 per trip in docking fees. The Sunset does not dock during the day and so Royal Kona does not pay any docking fees for the Sunset. . . Step-4: The Royal Kona is licensed to operate tours during 240 days per year. In cell A15 enter "Number of trips per year." and in cell A16 enter "Mean passengers per trip". The Lanai made 250 trips with an average of 29.8 passengers per trip. Enter these numbers in cells B15 and B16. The Villager was not operated as frequently because the Royal Kona frequently uses the Villager for employee and other business functions. In total, during 2018 the Villager made 155 trips with an average of 23.1 passengers per trip, and the Sunset made 212 trips with an average of 32.2 passengers per trip. Enter these pieces of information in cells C15-D16. Step-5: Now calculate the 2018 profit eamed by each boat tour. In cell A17 enter "Annual Profit". Apply a bottom border format to row 16 to visually draw attention to the profit in Row 17. Now you will enter the profit formula in the cells in Row 17. The formula contains three parts and illustrates the SUM function. The first part of the formula expresses the total revenue, which is the price per passenger found in cell B3, times the number of trips from cell B15 and times the number of passengers per trip from cell B16. The next portion of the formula subtracts the fixed costs contained in cells B5 through B9. Finally, the variable costs are subtracted as well, and these variable costs are the costs per trip contained in cells B11 through B14 multiplied by the number of trips from B15. In cell B17, enter the formula -B3*B15*B16-SUM(B5:39)- SUM(B11:B14)*B15. Apply this formula to the other boats, and you should have the total 2018 profit from all three boats. Finally, apply the same dollar sign and comma separator format used in cells B5-D5 to cells B17-017. What was the Royal Kona's most profitable boat tour in 2018 and how much was that Step-6: The boats do not all make the same number of trips per year. Therefore, the profit information calculated above will not necessarily tell you which boat would earn the most marginal profit if one trip was added. If you had enough demand to warrant an extra trip, then to 01: profit? Homework #3 extra trip (marginal profit). So move down to cell A18 and enter "Added trip profit" Then in cells B18-D18 enter the profit per added trip. You can review the formula used in row 17 and think about how to modify this formula. In other words, figure out this function on your own 02: Whar boat four makes the most profit for Royal Kona in terms of an incremental trip, and how much is this marginal profit? Step 7: In cell A19 enter "Profit margin per added trip". Profit margin also provides an important measure of profit. The profit margin answers the question of how much money are you making on each dollar of revenue. In other words, for every dollar that a customer pays you, how much do you keep? (Note: in some industries the same concept is used but in reverse, so the meaning becomes, for each dollar you receive, how much do you spend, and this is sometimes referred to as the operating ratio.) To see the importance of the profit margin concept, suppose 60 resort guests are traveling as a group. They tell you that their group has earmarked $3500 so that most of their members could take a day cruise. They want you to recommend a tour for them. In this case, you obtain greatest profit by determining which tour provides you with the most profit per dollar, so that you can keep the most possible out of this $3500. To calculate profit margin, divide the profit per added trip (marginal profit) by the total revenue per added trip. Use actual revenues in your spreadsheet, so not the example $3500 from the prior paragraph. Thus, calculate the profit margin for each boat tour, and enter these formulas in cells B19-D19. You should get a number that is at most 1.00, because you cannot keep more than one dollar for every dollar you receive. If you have results that are not valid, then look to see if the formula is clear in specifying which numbers belong in the numerator versus denominator. If you have a problem, you might be able to fix it easily by inserting parentheses to clarify your formula. Q3: What boat tour offers the greatest profit margin per added trip, and what is this profit margin? Step-8: Now look at a financial profitability measure that would be used if Royal Kona managers wanted to make future investments into similar types of boat tours. For this evaluation you need to assess how much profit is generated by each dollar invested. These invested dollars are really the fixed costs. Enter "Return on investment" in cell A20. In cell B20 divide the profit in B17 by the sum of the fixed costs. Next apply this same formula to the other two boats. Since this is a rate of return, express it as a percentage. Homework #3 04: Whar boar four offers the greatest return on investment, and what is this return on investment? Some added comments about this type of Excel use, and a final question. This assignment really draws from a number of areas including accounting, finance, and marketing. Operations management is also reflected in this assignment because an operations manager needs to evaluate the cost effectiveness of production operations. Rather than using revenue as we did here, the production manager would typically measure the number of units produced, for example how many units were produced on each of three assembly lines. After measuring production units, a production manager would then divide these units by the appropriate costs, fixed or variable, to determine which production line is producing at a higher profitability. And, as you saw here, a production manager might compute profitability in several ways, because each profitability measure provides unique information helpful in making decisions. A final benefit of this analysis is that break-even amounts can be calculated very quickly. For example, for any Royal Kona boat tour, you can take any cell containing cost information and change the number in this cell to see how high the cost can go before the boat tour profit margin becomes zero. Or you could look at the cells containing revenue information. If Royal Kona faced increased competition, you might want to see how low the price of a boat tour could become before the tour became profitless. For this question focus on the mean number of passengers per trip on the Sunset tour. Q5: As an added complication, the food cost for each trip is being changed so that it is not fixed per trip, but will be dependent upon the number of passengers on each trip. In other words, if food for one passenger is x. food for 2 passengers would be 2x and so on. With this change (and given that the current price and other fixed costs remain the same). what is the minimum mean number of passengers needed to be profitable in terms of having a positive return on investment? Alternatively, is there a tool you could use for determining the number of passengers needed to make the trip profitable? If so, what tool is that? Homework #3 Quantitative Business Analysis (MHR381) 3 Homework number Due date Additional files needed None Excel functionality addressed: Learn how to use Excel to evaluate the profitability of different products. Business topic addressed: Evaluate the profitability of three products sold by a company. Realize that multiple profitability measures can be used for different reasons. Also understand that the profitability of a product is affected by decisions such as how to account for costs. This case illustrates how profit is affected by whether you designate costs as fixed or variable. Tish Kanui helps manage guest services at Royal Kona Resort, a resort overlooking Kailua Bay on the main island of Hawaii. Part of Tish's job is to operate Royal Kona's three boat tours. Tish has asked you to help her evaluate the boat tours in terms of profitability. Royal Kona has invested in these tours as a means of offering more services to their guests. Royal Kona management would now like to financially assess the tours. This assessment will help determine whether to continue operating all three tours, because unprofitable tours could be discontinued. Also, Royal Kona guests frequently ask the resort concierge to recommend a tour, and so the Royal Kona can influence which tour is taken. If one tour generates more profit per passenger than the other tours, then that tour should be recommended. WHAT TO DO: Step 1: Open a blank Excel spreadsheet and enter the following information: B D 1 2018 Royal Kona Tour Information 2 Tour 3 Price per passenger Lanai Villager $95 $85 Sunset $110 The three tours are named for the three boats: the Lanai, the Villager, and the Sunset. Each tour lasts for almost one full day, and some food is provided for passengers. The Lanai tour runs near the coastline and stops twice. During these stops guests receive a guided tour through some of Hawaii's scenery. The Villager tour makes three 91-minute stops at small villages during the day and passengers eat most of their food off of the boat while they shop. Finally, the Sunset journeys a bit further into the Pacific and does not stop during the day. The price-per-passenger information in row-3 reflects the price each guest pays for the one-day tour, and this price includes food charges. Thus, a couple touring on the Lanai would pay $170, plus any tips or gratuities paid directly to the tour-guide. HW3-1 Homework #3 Step-2: Royal Kona pays a variety of costs to operate the boats. At this point you will enter the fixed cost is a variable cost. When an additional passenger pays to take the Lanai tour, the Royal fixed costs. A fixed cost is a cost that remains fairly stable regardless of sales. The opposite of a Kona receives an extra $85 in sales revenue. The Royal Kona will also pay for the passenger's food and for fuel and other costs associated with the tour, and these costs are not fixed because they rise and fall, or vary. In cell A4 enter Fixed Costs (annual)", and then in the next five rows, cells A5-A9, enter five types of costs, one cost per row, and indent the names of these costs. The costs include: boat depreciation, boat maintenance, insurance, share of hotel costs, and advertising. The three boats are depreciated, meaning the purchase costs are spread out over multiple years. captain or tour-guide. Royal Kona expects to use the Lanai for 10 years and then sell the boat for S600,000. Thus, over the ten years, the accounting cost of the boat is $1,100,000 ($1,700,000 $600,000), and the depreciation is expensed evenly over these years, so that the cost per year is $110,000. The Villager and Sunset each seat up to 32 passengers and cost $1.00 million a piece which is also depreciated evenly over the 10 years. No salvage value is expected for these two boats, so each has an annual depreciation of $100,000. Enter the annual depreciation amounts for all three boats in cells B5-D5. Royal Kona pays to maintain and repair each boat, and in 2018 these costs totaled $37,000 for the Lanai, $22,000 for the Villager, and $29,000 for the Sunset. Enter these amounts in the cell range B6-D6. Royal Kona pays insurance for each boat, and in 2018 these insurance costs were $5,000 for each boat. So enter $5,000 in the cells B7-07. Next, costs needed to support the boat tours include a portion of Tish's salary, occasional support from other resort employees, and use of resort phones and computers. Management's best guess is that, collectively, these costs total about $69,000. For convenience, this cost is divided equally between the three boats, so each boat incurs an annual fixed cost of $23,000 that you should enter in cells B8-DS. Finally, the tours are advertised with separate brochures and mailings. Total costs are $40,500 per year, again divided equally among the three boats. So enter $13,500 to denote these costs in cells B9-D9. And before moving further, format your cells. Make sure text is aligned above numbers, and use comma separators in cells B5-D9. To insert commas in these cells, highlight the cell range and then in the Number group on the Home ribbon, click on the ","icon. Next, click on the arrow in the right corner to open up the dialog box. On the Number tab, select Number, zero decimal places, and check the box that indicates whether to use commas. Then click OK. Also use dollar signs in row 5 by selecting just cells BS-D5 and from the menu bar again select the "" icon under the Number group. Again, click on the arrow in the right corner of the Number group. This TINA 2 Homework #3 time on the Number tab select Currency, zero decimal places, and put a dollar sign in the Symbol field. Step 3: Now enter the variable cost information. The boat tours entail added costs each time the tour is operated, so costs generally correspond to the number of tours conducted, not with each extra passenger. In cell A10 enter "Variable Costs (per trip)". Four variable costs are involved, and enter one cost in each of cells All-A14, indenting each heading as you did with the fixed costs. The four variable costs include: fuel, tour guide cost, passenger food, and docking fees. So enter the following information to account for these costs in cells BIT-D14: Fuel: the mean fuel cost per trip is $200 for the Lanai, $175 for the Villager, and $325 for the Sunset. Tour guide cost: tour guides for the Lanai cost more because they must lead on-shore tours, and the Royal Kona pays $165 per trip for a tour-guide on this boat. The tour guide cost per trip for the Villager is $100 and for the Sunset is $130. Passenger food: the mean amount spent on food per trip during 2018 was $225 for the Lanai, $150 for the Villager (because most food is purchased on shore), and $400 for the Sunset. Docking fees: the Lanai is charged $75 per trip and the Villager is charged $95 per trip in docking fees. The Sunset does not dock during the day and so Royal Kona does not pay any docking fees for the Sunset. . . Step-4: The Royal Kona is licensed to operate tours during 240 days per year. In cell A15 enter "Number of trips per year." and in cell A16 enter "Mean passengers per trip". The Lanai made 250 trips with an average of 29.8 passengers per trip. Enter these numbers in cells B15 and B16. The Villager was not operated as frequently because the Royal Kona frequently uses the Villager for employee and other business functions. In total, during 2018 the Villager made 155 trips with an average of 23.1 passengers per trip, and the Sunset made 212 trips with an average of 32.2 passengers per trip. Enter these pieces of information in cells C15-D16. Step-5: Now calculate the 2018 profit eamed by each boat tour. In cell A17 enter "Annual Profit". Apply a bottom border format to row 16 to visually draw attention to the profit in Row 17. Now you will enter the profit formula in the cells in Row 17. The formula contains three parts and illustrates the SUM function. The first part of the formula expresses the total revenue, which is the price per passenger found in cell B3, times the number of trips from cell B15 and times the number of passengers per trip from cell B16. The next portion of the formula subtracts the fixed costs contained in cells B5 through B9. Finally, the variable costs are subtracted as well, and these variable costs are the costs per trip contained in cells B11 through B14 multiplied by the number of trips from B15. In cell B17, enter the formula -B3*B15*B16-SUM(B5:39)- SUM(B11:B14)*B15. Apply this formula to the other boats, and you should have the total 2018 profit from all three boats. Finally, apply the same dollar sign and comma separator format used in cells B5-D5 to cells B17-017. What was the Royal Kona's most profitable boat tour in 2018 and how much was that Step-6: The boats do not all make the same number of trips per year. Therefore, the profit information calculated above will not necessarily tell you which boat would earn the most marginal profit if one trip was added. If you had enough demand to warrant an extra trip, then to 01: profit? Homework #3 extra trip (marginal profit). So move down to cell A18 and enter "Added trip profit" Then in cells B18-D18 enter the profit per added trip. You can review the formula used in row 17 and think about how to modify this formula. In other words, figure out this function on your own 02: Whar boat four makes the most profit for Royal Kona in terms of an incremental trip, and how much is this marginal profit? Step 7: In cell A19 enter "Profit margin per added trip". Profit margin also provides an important measure of profit. The profit margin answers the question of how much money are you making on each dollar of revenue. In other words, for every dollar that a customer pays you, how much do you keep? (Note: in some industries the same concept is used but in reverse, so the meaning becomes, for each dollar you receive, how much do you spend, and this is sometimes referred to as the operating ratio.) To see the importance of the profit margin concept, suppose 60 resort guests are traveling as a group. They tell you that their group has earmarked $3500 so that most of their members could take a day cruise. They want you to recommend a tour for them. In this case, you obtain greatest profit by determining which tour provides you with the most profit per dollar, so that you can keep the most possible out of this $3500. To calculate profit margin, divide the profit per added trip (marginal profit) by the total revenue per added trip. Use actual revenues in your spreadsheet, so not the example $3500 from the prior paragraph. Thus, calculate the profit margin for each boat tour, and enter these formulas in cells B19-D19. You should get a number that is at most 1.00, because you cannot keep more than one dollar for every dollar you receive. If you have results that are not valid, then look to see if the formula is clear in specifying which numbers belong in the numerator versus denominator. If you have a problem, you might be able to fix it easily by inserting parentheses to clarify your formula. Q3: What boat tour offers the greatest profit margin per added trip, and what is this profit margin? Step-8: Now look at a financial profitability measure that would be used if Royal Kona managers wanted to make future investments into similar types of boat tours. For this evaluation you need to assess how much profit is generated by each dollar invested. These invested dollars are really the fixed costs. Enter "Return on investment" in cell A20. In cell B20 divide the profit in B17 by the sum of the fixed costs. Next apply this same formula to the other two boats. Since this is a rate of return, express it as a percentage. Homework #3 04: Whar boar four offers the greatest return on investment, and what is this return on investment? Some added comments about this type of Excel use, and a final question. This assignment really draws from a number of areas including accounting, finance, and marketing. Operations management is also reflected in this assignment because an operations manager needs to evaluate the cost effectiveness of production operations. Rather than using revenue as we did here, the production manager would typically measure the number of units produced, for example how many units were produced on each of three assembly lines. After measuring production units, a production manager would then divide these units by the appropriate costs, fixed or variable, to determine which production line is producing at a higher profitability. And, as you saw here, a production manager might compute profitability in several ways, because each profitability measure provides unique information helpful in making decisions. A final benefit of this analysis is that break-even amounts can be calculated very quickly. For example, for any Royal Kona boat tour, you can take any cell containing cost information and change the number in this cell to see how high the cost can go before the boat tour profit margin becomes zero. Or you could look at the cells containing revenue information. If Royal Kona faced increased competition, you might want to see how low the price of a boat tour could become before the tour became profitless. For this question focus on the mean number of passengers per trip on the Sunset tour. Q5: As an added complication, the food cost for each trip is being changed so that it is not fixed per trip, but will be dependent upon the number of passengers on each trip. In other words, if food for one passenger is x. food for 2 passengers would be 2x and so on. With this change (and given that the current price and other fixed costs remain the same). what is the minimum mean number of passengers needed to be profitable in terms of having a positive return on investment? Alternatively, is there a tool you could use for determining the number of passengers needed to make the trip profitable? If so, what tool is that