Reference no: EM132556953

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).

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

Question 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.

Question 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.

Question 4. Prepare 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.