Question

CUP 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. S25 per person. The daily operating expenses fuel and miscellaneous supplies average S50 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). Please have a professional cover sheet that includes your name, student ID, Faculty Name, Course code and name, A title, Due date. 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.

Its an accounting case study

please solve it

Answer 1

Revenue = Variable cost + contribution

contribution Margin = Fixed cost + Profit Margin

A) Statement showing Calculation of revenue and variable cost per cruise

Particulars	Amount per cruise ($)	Total Amount per cruise (30 pax)	Amount ($) 100 days cruise (30 pax)	working
Revenue	100 per pax	3000	300000
less variable cost :
6 crew salary	100 per crew	600	60000 #	# 100*6*100
refreshment	25 per pax	750	75000
operating exp	50 per cruise	50	5000 #	# 50*100
Total variable cost		1400	140000
Contribution Margin		1600	160000	revenue - variable cost
less fixed cost		-85000	-85000
profit Margin		-83400	75000	contribution - fixed cost

so Revenue per cruise = 3000

variable cost per cruise = 1400

contribution margin = revenue - variable cost

= (3000-1400)

= $ 1600 ------( 1)

b) Break even means the Revenue needed to be earned  to recover the cost (i.e fixed cost )

Break even = Fixed cost / contibution margin

Fixed cost = 85000

Contribution margin from (1) = $1600

Break even = 85000/1600

= 53.125

So to recover break even company has to atleast run crusie for 53.125 days

Break even analysis 180000 160000 160000 140000 120000 100000 revenue 85000 85000 85000 80000 85000 80000 contibution fixed cost 60000 40000 32000 20000 16000 10 100 2050 No of days

c) owner expects to earn $125000

Target profit = 125000

fixed cost =85000

contribution Margin = 125000+85000

=210000

contribution margin per cruise = 1600

so number  of days Canadian need cruise

as per cost volume profit formula = 210000/1600

=131.25 days

As company operates mid may to mid September

total number of days company between mid may to mid September = 123 days

and to earn the revenue of $125000 company has to run cruise =131.25 days

so its not a realistic approach fro the company.

so to earn the $ 125000 company has to increase its contribution per margin to $1707.32 ($210000/123)

This can be possible only if company will increase its cruise price per passenger and other things remain constant

d) Statement showing contribution margin income statement to earn profit of $125000

we will increase the cruise price per passenger and all other things will remain constant

we have 123 days

fixed cost = 85000

variable cost = 1400

profit to be earned = 125000

so contribution margin= fixed + profit

=85000+125000

=210000

so contribution per cruise = 210000/123

=$1707.32

so sale price per cruise = contibution margin + variable cost

= 1707.32+1400

=3107.32

so cruise price per passenge = 3107.32/30

=103.577

Statement showing Calculation of revenue when price per cruise is estimated $3107.32

Particulars	Amount per cruise ($)	Total Amount per cruise (30 pax)	Amount ($) 123 days cruise (30 pax)	working
Revenue	103.577	3107.32	382200.36	3107.32*123
less variable cost :
6 crew salary	100 per crew	600	73800 #	# 100*6*123
refreshment	25 per pax	750	92250	750*123
operating exp	50 per cruise	50	6150 #	# 50*123
Total variable cost		1400	172200
Contribution Margin		1707.32	210000.36	revenue - variable cost
less fixed cost			-85000
profit Margin			125000.36	contribution - fixed cost