Question

Sailing Voyages Inc. is a company operated by an individual as a summer tourist attraction on the Great Lakes. It operates a sailing schooner offering day cruises for individuals and groups. Over the last few years, the average number of tourists per cruise was 30. The average charge per person for the cruise including group discounts was $100. The company operates from mid-May until mid-September. On average, the ship sails 100 days during this period. ‘The Canadian’ (the name of the schooner) requires a crew of 6, and is captained by the owner of the company. University students with extensive sailing experience have been willing to work on a per diem basis of $100. They are paid only if the ship is cruising. The ship provides non-alcoholic refreshments and a light lunch. These are acquired daily from a local delicatessen and cost, on average, $25 per person. The daily operating expenses fuel and miscellaneous supplies average $50 per cruise. The company has a variety of annual expenses including: maintenance, depreciation, marketing, licenses, etc., totaling approximately $85,000. Required: Prepare an Excel Workbook to answer the following questions in a professional manner. Ensure that you are utilizing Excel features (including links between spreadsheets, formulas, formatting, graphing).

1. Compute the revenue and variable costs for each cruise. Use this to compute the contribution margin per cruise.

2. Compute the number of cruises that ‘Canadian’ must have each year to break-even. Use your knowledge gained in this course to show the different formulas, graphs etc for break-even analysis.

3. The owner expects a total return on capital and remuneration of $125,000. Using the concept of ‘contribution margin’, cost-volume-profit, and target profit calculations, estimate how many cruises the Canadian needs to make to reach this objective. Is this a realistic expectation? Add your thoughts, proposals, and recommendations.

4. Prepare a contribution margin income statement for Sailing Voyages Inc. If the owner wishes to adjust or achieve his income goal, what changes can he make? How can these changes be easily estimated and projected to show how these changes affect net income. Use your imagination, and your knowledge of cost-volume-profit analysis. Highlight your ideas by utilizing the various graphing tools in Excel.

Answer 1

ANSWER:

1. Revenue and Variable Expense
Revenue per person		100
Average no of Tourist on per cruise		30
Hence Revenue Per cruise	100*30	3000
Variable Expense:
University Studend Expense	6*100	600
Refreshment	25*30	750
Operating Expense		50
Total Variable Expense		1400
2. Break Even
Revenue per cruise	3000
Less: Variable Cost per cruise	-1400
Contribution Margin per cruise (3000-1400)	1600
Fixed Cost	85000
Break Even Point	85000/1600	54 Cruise to Break Even
(Fixed Cost/Contribution Margin per cruise)
3. Target Profit 125000
Revenue per cruise	3000
Less: Variable Cost per cruise	-1400
Contribution Margin per cruise (3000-1400)	1600
Fixed Cost	85000
Target Proft	125000
Break Even Point	(85000+125000)/1600	132 Cruise for 125000 Profit
(Fixed Cost+Target Profit/Contribution Margin per cruise)
Yes, its realistic as as cruise sails 100 days
4. Income Statement
Revenue per cruise	3000*100	300000
Less: Variable Cost per cruise
University Studend Expense	60000
Refreshment	75000
Operating Expense	5000	140000
Contribution Margin		160000
Less: Fixed Cost		85000
Net Profit		75000
For target income, no of cruise needs to be increased.

Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you.