week 3 skills building

canada2016

week2skillsbuilding.xlsx

Home >Business & Finance homework help >week 3 skills building

Pr. 19(5)-6A

Problem 19(5)-6A

Name:

Section:

# N-box Incorrects due to blanks COUNTIF(B15:AT24," ")

Score:

# N-box +B-box corrects COUNTIF(B15:AT24," ")

Key Code:

[Key code here]

Total SUM(AD13:AD15)

Instructions

Answers are entered in the cells with gray backgrounds.

Percentage =(AD16-AD13-AD14)/AD16

Cells with non-gray backgrounds are protected and cannot be edited.

An asterisk (*) will appear to the right of an incorrect entry.

Notes:

If number-entry box is blank (this would be an incorrect answer for N-boxes), error check returns two spaces, " "

If number-entry or blank-entry box is incorrect, returns "*"

Wolsey Industries Inc.

If number-entry or blank-entry box is correct, returns single space, " "

Estimated Income Statement

Accounts Receivable

Use data verification to set data entry to whole number >= 0, and use drop-downs for lables and names, so that students can't enter a space in a box and have it counted as correct.

For the Year Ended December 31, 20Y3

Accounts Payable

Conditional formatting might be used but wasn't here, to hide some of the error check return symbols. If A1 = "~*", then font = red, if something else, then font = background color.

Sales

Cost of goods sold:

Direct materials

Materials inventory, May 1, 2012

Direct labor

Materials inventory, May 31, 2012

Factory overhead

Cost of goods sold

Cost of goods manufactured

Gross profit

Cost of materials available for use

Expenses:

Factory overhead

Selling expenses

Machinery depreciation

Sales salaries and commissions

Materials inventory

Advertising

Miscellaneous cost

Travel

Property taxes

Miscellaneous selling expense

Supplies

Total selling expenses

Administrative expenses:

Office and officers' salaries

Finished goods inventory, May 1, 2012

Supplies

Finished goods inventory, May 31, 2012

Miscellaneous administrative expense

Materials inventory, May 1, 2012

Total administrative expenses

Materials inventory, May 31, 2012

Total expenses

Work in process inventory, May 1, 2012

Income from operations

Work in process inventory, May 31, 2012

Cash

Contribution margin ratio:

Income Summary

Sales

Units ´ Unit Variable Cost

Variable costs

Contribution margin

Sales

Contribution margin ratio

Break-even sales:

Fixed costs

Sale Price - Unit Variable Cost

Unit contribution margin

Break-even sales (units)

Sale price

Break-even sales (dollars)

For each unit level of sales, enter the total sales dollars and total costs. The chart at right will be plotted as you enter the amounts.

After all points are plotted, grab and move the labels provided at the left to identify each area.

Units

Sales $

Costs $

3,000

6,000

9,000

12,000

15,000

18,000

21,000

24,000

27,000

Margin of safety:

Sale Price

Units

Expected sales

Break-even point

Margin of safety (in dollars)

Expected sales

Margin of safety (as a percentage of sales)

Mark Sears: Enter as a formula.

Operating leverage:

Unit CM $

Units

Contribution margin

Mark Sears: Enter unit contribution margin from part 3 above.

Mark Sears: Enter expected sales in units.

Mark Sears: Enter a formula of sales units x unit sale price.

Mark Sears: This will be the fixed costs that do not change regardless of sales level.

Mark Sears: Enter a formula of fixed costs plus (units x variable cost per unit).

Income from operations

Operating leverage

Mark Sears: Enter as a formula.

Cost-Volume-Profit Chart

0 3000 6000 9000 12000 15000 18000 21000 24000 27000 Sales $ Costs $

Units

Sales and Costs

Sol

Problem 19(5)-6A
Name:		Solution
Section:
Score:		ON
Instructions
Answers are entered in the cells with gray backgrounds.
Cells with non-gray backgrounds are protected and cannot be edited.
An asterisk (*) will appear to the right of an incorrect entry.
1.	Wolsey Industries Inc.
	Estimated Income Statement
	For the Year Ended December 31, 20Y3
		Sales									$ 3,500,000
		Cost of goods sold:
		Direct materials							$ 1,006,250
		Direct labor							875,000
		Factory overhead							637,500
		Cost of goods sold									2,518,750
		Gross profit									$ 981,250
		Expenses:
		Selling expenses
		Sales salaries and commissions					$ 285,000
		Advertising					40,000
		Travel					12,000
		Miscellaneous selling expense					29,475
			Total selling expenses						$ 366,475
		Administrative expenses:
		Office and officers' salaries					$ 132,000
		Supplies					97,500
		Miscellaneous administrative expense					35,275
			Total administrative expenses						264,775
		Total expenses									631,250
		Income from operations									$ 350,000
2.
	Contribution margin ratio:
		Sales							$ 3,500,000
				Units ´ Unit Variable Cost
		Variable costs		21,875		$120			2,625,000
		Contribution margin							$ 875,000
		Sales						¸	3,500,000
		Contribution margin ratio							25.0%
3.
	Break-even sales:
		Fixed costs							$ 525,000
				Sale Price - Unit Variable Cost
		Unit contribution margin		$160		$120		¸	$40
		Break-even sales (units)							13,125
		Sale price						´	$160
		Break-even sales (dollars)							$ 2,100,000
4.	For each unit level of sales, enter the total sales dollars and total costs. The chart at right will be plotted as you enter the amounts.
	After all points are plotted, grab and move the labels provided at the left to identify each area.
		Units	Sales $	Costs $
		0	$ -	$ 525,000 Mark Sears: This will be the fixed costs that do not change regardless of sales level.
		3,000	480,000 Mark Sears: Enter a formula of sales units x unit sale price.	885,000 Mark Sears: Enter a formula of fixed costs plus (units x variable cost per unit).
		6,000	960,000	1,245,000
		9,000	1,440,000	1,605,000
		12,000	1,920,000	1,965,000
		15,000	2,400,000	2,325,000
		18,000	2,880,000	2,685,000
		21,000	3,360,000	3,045,000
		24,000	3,840,000	3,405,000
		27,000	4,320,000	3,765,000
5.
	Margin of safety:
				Sale Price	´	Units
		Expected sales		$160		21,875			$ 3,500,000
		Break-even point		$160		13,125			2,100,000
		Margin of safety (in dollars)							$ 1,400,000
		Expected sales						¸	3,500,000
		Margin of safety (as a percentage of sales)							40.0% Mark Sears: Enter as a formula.
6.
	Operating leverage:
				Unit CM $	´	Units
		Contribution margin		$40 Mark Sears: Enter unit contribution margin from part 3 above.		21,875 Mark Sears: Enter expected sales in units.	Mark Sears: Enter a formula of sales units x unit sale price.		Mark Sears: This will be the fixed costs that do not change regardless of sales level.	Mark Sears: Enter a formula of fixed costs plus (units x variable cost per unit).		$ 875,000
		Income from operations						¸	350,000
		Operating leverage							2.5 Mark Sears: Enter as a formula.

Cost-Volume-Profit Chart

Sales $ 0 3000 6000 9000 12000 15000 18000 21000 24000 27000 0 480000 960000 1440000 1920000 2400000 2880000 3360000 3840000 4320000 0 3000 6000 9000 12000 15000 18000 21000 24000 27000 Costs $ 0 3000 6000 9000 12000 15000 18000 21000 24000 27000 525000 885000 1245000 1605000 1965000 2325000 2685000 3045000 3405000 3765000

Units

Sales and Costs