Sailing Voyages Inc. is a company operated by an individual as asummer tourist attraction on the Great Lakes. It operates a sailingschooner offering day cruises for individuals and groups. Over thelast few years, the average number of tourists per cruise was 30.The average charge per person for the cruise including groupdiscounts was $100. The company operates from mid-May untilmid-September. On average, the ship sails 100 days during thisperiod. ‘The Canadian’ (the name of the schooner) requires a crewof 6, and is captained by the owner of the company. Universitystudents with extensive sailing experience have been willing towork on a per diem basis of $100. They are paid only if the ship iscruising. The ship provides non-alcoholic refreshments and a lightlunch. These are acquired daily from a local delicatessen and cost,on average, $25 per person. The daily operating expenses fuel andmiscellaneous supplies average $50 per cruise. The company has avariety of annual expenses including: maintenance, depreciation,marketing, licenses, etc., totaling approximately $85,000.Required: Prepare an Excel Workbook to answer the followingquestions in a professional manner. Ensure that you are utilizingExcel features (including links between spreadsheets, formulas,formatting, graphing).
1. Compute the revenue and variable costs for each cruise. Usethis to compute the contribution margin per cruise.
2. Compute the number of cruises that ‘Canadian’ must have eachyear to break-even. Use your knowledge gained in this course toshow the different formulas, graphs etc for break-evenanalysis.
3. The owner expects a total return on capital and remunerationof $125,000. Using the concept of ‘contribution margin’,cost-volume-profit, and target profit calculations, estimate howmany cruises the Canadian needs to make to reach this objective. Isthis a realistic expectation? Add your thoughts, proposals, andrecommendations.
4. Prepare a contribution margin income statement for SailingVoyages Inc. If the owner wishes to adjust or achieve his incomegoal, what changes can he make? How can these changes be easilyestimated and projected to show how these changes affect netincome. Use your imagination, and your knowledge ofcost-volume-profit analysis. Highlight your ideas by utilizing thevarious graphing tools in Excel.
