Question

Use Excel to complete this exam. Use one spreadsheet with tabs for each question. Label the tabs as "Question 1," "Question 2, etc. Submit your completed spreadsheet in the appropriate D2L Dropbox by Friday, November 15, 2019. Each question is worth 25 points. 1. Using the information in Cases 6-61 and 6-60 (given below) construct an Excel spreadsheet to compute the operating profit (loss) for the base case and each of the three alternatives.. 6-61 Sensitivity Analysis Using Excel. Refer to the information for Rafting Products, Inc., in Skill-Building Case 60. Prepare an Excel spreadsheet to calculate the operating profit (loss) for the base case and for each of the three scenarios presented in the case. Using the spreadsheet in the Computer Application box in this chapter as a guide, include "data entry" and "sensitivity analysis results" sections, and combine variable cost of goods sold and selling and administrative costs on one line and fixed cost of goods sold and selling and administrative costs on another line. 6-60 Group Activity: Sensitivity Analysis and Decision Making. Rafting Products, Inc., produces inflatable rafts used for river rafting. Sales have grown slowly over the years, and cost increases are causing Rafting Products to incur losses. Financial data for the most recent year are shown. $2,600,000 (= $2,000 x 1,300 units) $1,040,000 (= $800 x 1,300 units) 390,000 (= $300 x 1,300 units) 1,430,000 $1,170,000 Sales Variable costs Cost of goods sold Selling and administrative costs Total variable costs Contribution margin Fixed costs Cost of goods sold Selling and administrative costs Total fixed costs Operating loss $800,000 400,000 1,200,000 ($ 30,000) Members of the management group at Rafting Products arrived at these three possible courses of action to return the company to profitability (each scenario is independent of the others)

Answer 1

Solution to the above problem is as under:

Question 1

Computation of Operating Profit/(Loss) [Base Question]
Particulars	Working	Amount ($)
Sales (A)	$2000 X 1300 Units	2600000
Variable Costs:
Cost of Goods Sold	$800 X 1300 Units	1040000
Selling and Administrative Costs	$300 X 1300 Units	390000
Total Variable Costs (B)		1430000
Contribution Margin (A-B)		1170000
Fixed Costs:
Cost of Goods Sold	800000
Selling and Administrative Costs	400000
Total Fixed Costs		1200000
Operating Loss		-30000

Question 2:

Computation of Operating Profit/(Loss) [Alternative-1]
Particulars	Working	Amount ($)
Sales (A)	$2200 X 1235 Units	2717000
Variable Costs:
Cost of Goods Sold	$800 X 1235 Units	988000
Selling and Administrative Costs	$300 X 1235 Units	370500
Total Variable Costs (B)		1358500
Contribution Margin (A-B)		1358500
Fixed Costs:
Cost of Goods Sold	800000
Selling and Administrative Costs	400000
Total Fixed Costs		1200000
Operating Profit		158500

Alternative-1: Sales price per raft increase by 10% and sales volume drop by 5%

Question 3:

Computation of Operating Profit/(Loss) [Alternative-2]
Particulars	Working	Amount ($)
Sales (A)	$1800 X 1404 Units	2527200
Variable Costs:
Cost of Goods Sold	$800 X 1404 Units	1123200
Selling and Administrative Costs	$300 X 1404 Units	421200
Total Variable Costs (B)		1544400
Contribution Margin (A-B)		982800
Fixed Costs:
Cost of Goods Sold	800000
Selling and Administrative Costs	400000
Total Fixed Costs		1200000
Operating Loss		-217200

Alternative-2: Sales price per raft drops by 10% and sales volume increases by 8%.

Question-4:

Computation of Operating Profit/(Loss) [Alternative-3]
Particulars	Working	Amount ($)
Sales (A)	$2000 X 1495 Units	2990000
Variable Costs:
Cost of Goods Sold	$800 X 1495 Units	1196000
Selling and Administrative Costs	$300 X 1495 Units	448500
Total Variable Costs (B)		1644500
Contribution Margin (A-B)		1345500
Fixed Costs:
Cost of Goods Sold	800000
Selling and Administrative Costs	600000
Total Fixed Costs		1400000
Operating Loss		-54500

Alternative-3: Sales volume increase by 15% with an increase in the Fixed Selling and Administrative Cost (Advertising Cost) by $200000.