ACCT WEEK 6 PROBLEMS

Pr. 11-6

Problem 11-6

* represents an incorrect N answer =COUNTIF(A14:H27,"~*")

Name:

Akia Carter

0

Section:

P11-6

" " represents an unanswered N box - counts as an incorrect. =COUNTIF(A14:H27," ")

28

Score:

0%

" " represents a correct blank answer or N answer =COUNTIF(A14:H27," ")

0

Key Code:

2

Total SUM(AV13:AV15)

Instructions

28

Answers are entered in the cells with gray backgrounds.

Percentage =AD6/AD8

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

0%

A red asterisk (*) will appear beside, above, or immediately below an incorrect answer.

Notes:

" " represents an unanswered N box - counts as an incorrect.

" " represents a correct blank answer or N answer

1.

Total number of answers = sum of above

ORGANIC HEALTH CARE PRODUCTS INC.

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.

Estimated Income Statement

For the Year Ending December 31, 2012

Steps:

Sales

Open this sheet and macro sheet

Cost of goods sold:

Open old templated, then change color palet to this sheet's

Direct materials

Insert new header - change problem number and reformat

Direct labor

Copy these formulas (column AD) to new sheet.

Factory overhead

Update to new edition's names and numbers

Cost of goods sold

Copy new error check formulas. For N-boxes

Gross profit

=IF(sol.!$C$5="OFF","",IF(AC25=""," ",IF(AC25<>sol.!AC25,"*"," ")))

Operating expenses:

Selling expenses:

Advertising

Sales salaries and commissions

For B-Boxes

Travel

=IF(sol.!$C$5="OFF","",IF(AC30<>sol.!AC30,"*"," "))

Miscellaneous selling expense

Total selling expenses

Copy Score formula from this template to new sheet.

Administrative expenses:

=IF(sol.!$C$5="OFF","","Score:")

=IF(sol.!$C$5="OFF","",AD10)

Office and officers' salaries

Supplies

=IF(sol.!$C$5="OFF","","*Since some answer boxes are correct when left blank, the beginning score is greater than 0%.")

Miscellaneous administrative expense

Total administrative expenses

Total expenses

Income from operations

2.

Contribution margin ratio =

-

=

0.0%

3.

Break-even sales (units) =

=

0

0

Break-even sales (dollars) =

=

$ - 0

sales

4.

Use the Autoshapes line feature to construct a cost-volume-profit chart indicating the break-even point.

Click and drag either of the lines to sketch the total revenue and total cost functions on the graph.

This requirement is not automatically scored.

$15,000,000

$12,500,000

$10,000,000

$7,500,000

$5,000,000

$2,500,000

$0

0

100,000

200,000

300,000

400,000

500,000

600,000

5.

Margin of safety:

Expected sales (in dollars)

Break-even point (in dollars)

Margin of safety (in dollars)

or

Margin of safety (in percent) =

=

0.0%

6.

Operating leverage =

=

0.0

sol.

Problem 11-6																													* represents an incorrect N answer =COUNTIF(A14:H27,"~*")
Name:		SOLUTION																											0
Section:																													" " represents an unanswered N box - counts as an incorrect. =COUNTIF(A14:H27," ")
Score:		See student sheet for student's score																											0
Scoring:		ON																											" " represents a correct blank answer or N answer =COUNTIF(A14:H27," ")
																													8
																													Total SUM(AV13:AV15)
Instructions																													8
Answers are entered in the cells with gray backgrounds.																													Percentage =AD6/AD8
Cells with non-gray backgrounds are protected and cannot be edited.																													100%
A red asterisk (*) will appear beside, above, or immediately below an incorrect answer.																													Notes:
																													" " represents an unanswered N box - counts as an incorrect.
																													" " represents a correct blank answer or N answer
1.																													Total number of answers = sum of above
		ORGANIC HEALTH CARE PRODUCTS INC.																											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.
		Estimated Income Statement
		For the Year Ending December 31, 2012
																													Steps:
		Sales								$ 10,000,000																			Open this sheet and macro sheet
		Cost of goods sold:																											Open old templated, then change color palet to this sheet's
		Direct materials						$3,200,000																					Insert new header - change problem number and reformat
		Direct labor						1,200,000																					Copy these formulas (column AD) to new sheet.
		Factory overhead						800,000																					Update to new edition's names and numbers
		Cost of goods sold								5,200,000																			Copy new error check formulas. For N-boxes
		Gross profit								$ 4,800,000																			=IF(sol.!$C$5="OFF","",IF(AC25=""," ",IF(AC25<>sol.!AC25,"*"," ")))
		Operating expenses:
		Selling expenses:
		Sales salaries and commissions				$1,450,000
		Advertising				833,000																							For B-Boxes
		Travel				340,000																							=IF(sol.!$C$5="OFF","",IF(AC30<>sol.!AC30,"*"," "))
		Miscellaneous selling expense				42,000
		Total selling expenses						$2,665,000																					Copy Score formula from this template to new sheet.
		Administrative expenses:																											=IF(sol.!$C$5="OFF","","Score:")		=IF(sol.!$C$5="OFF","",AD10)
		Office and officers' salaries				$300,000
		Supplies				210,000																							=IF(sol.!$C$5="OFF","","*Since some answer boxes are correct when left blank, the beginning score is greater than 0%.")
		Miscellaneous administrative expense				25,000
		Total administrative expenses						535,000
		Total expenses								3,200,000
		Income from operations								$ 1,600,000
2.
		Contribution margin ratio =		$10,000,000	-	$6,000,000	=	40.0%
				$10,000,000
3.
		Break-even sales (units) =		$2,400,000	=	240,000	units
				$10
		Break-even sales (dollars) =		$2,400,000	=	$ 6,000,000	sales
				40%
4.	Use the Autoshapes feature on the menu bar at the bottom of the screen to construct a cost-volume-profit chart indicating the break-even point.
	Click on "AutoShapes," then select the "lines" option. Use the straight line function to sketch the total revenue and total cost functions.
		$15,000,000
		$12,500,000
													Operating Profit Area
		$10,000,000
						Break-Even Point
		$7,500,000
		$5,000,000
		$2,500,000
				Operating Loss area
		$0
		0		100,000		200,000		300,000		400,000		500,000		600,000
5.
		Margin of safety:
		Expected sales (in dollars)		$ 10,000,000
		Break-even point (in dollars)		6,000,000
		Margin of safety (in dollars)		$ 4,000,000
		or
		Margin of safety (in percent) =		$4,000,000	=	40.0%
				$10,000,000
6.
		Operating leverage =		$4,000,000	=	2.5
				$1,600,000

P11-7.xlsx