Principles of Management Accounting Excel Problems

m2a3_problems.xlsx

Home >Business & Finance homework help >Accounting homework help >Principles of Management Accounting Excel Problems

P16A-17B

P16a.17b	Page 898
	Example of the time line from page 882 of the text
Requirement 1. Fill-in the time line for Sue Electronics.
	Conversion Costs
	Start	Complete				Complete
Requirement 2. Compute the equivalent units in the Assembly Department for April. (For entries with a 0 balance, make sure to enter
"0" in the appropriate column.
	Sue Electronics
	Assembly Department
	Equivalent Unit Computation
	Month Ended April 30
				Equivalent Units
	Flow of Production		Flow of Physical Units	Direct Materials	Conversion Costs
	Units accounted for:
	Completed and transferred out
	Ending work in process, April 30
	Total physical units accounted for		$ -
	Equivalent units			$ -	$ -
Compute the costs per equivalent unit in the Assembly Department for April. (For entries with $0 balance, make sure to enter "0"
in the appropriate column. Round the cost per equivalent unit to two decimal places.)
	Sue Electronics
	Assembly Department
	Cost per Equivalent Unit
	Month Ended April 30
	Flow of Production		Direct Materials	Conversion Costs
	Beginning work in process		$ -	$ -
	Costs added during April
	Divide by equivalent units
	Cost per equivalent unit		ERROR:#DIV/0!	ERROR:#DIV/0!
Requirement 3. Assign total costs in the Assembly department to (a) units completed and transferred out to Programming and (b)
units still in process at April 30. (Round your answers to the nearest whole dollar. Enter the cost per equivalent unit amounts in the
same order as calculated in the preceding step.)
	Sue Electronics
	Assembly Department
	Cost Assignment
	Month Ended April 30
	Assign Costs:		Direct Materials	Conversion Costs	Total
	a. Completed and transferred out		=$76,100 X (3.76 + 2.99)		ERROR:#DIV/0!
	b. Ending work in process, April 30
	Direct materials		=$23,99 X 3.76		ERROR:#DIV/0!
	Conversion costs			=$9560 X 2.99	ERROR:#DIV/0!
	Total ending work in process inventory, April 30				ERROR:#DIV/0!
	Total cost accounted for				ERROR:#DIV/0!
Requirement 4. Prepare a T-account for Work in process inventory--Assembly to show activity during April, including the
April 30 balance. (Leave unused cells blank.)
	Work in process inventory -- Assembly
	Bal, March 31	0	Transferred to Programming		ERROR:#DIV/0!
	Directy materials	$ -
	Direct labor
	Manufacturing overhead
	Bal, April 30	ERROR:#DIV/0!

Sue Electronics makes CD players in three processes: assembly, programming, and packaging. Direct materials are added at the beginning of the assembly process. Conversion costs are incurred evenly throughout the process. The Assembly Department had no Work in process on March 31. In mid-April, Sue Electronics started production on 100,000 CD players. Of this number 76,100 CD players were assembled during April and transferred out ot the Programming Department. The April 30 Work in process in the Assembly Department was 40% of the way through the assembly process. Direct materials costing $375,720 were placed in production in Assembly during April, and direct labor of $157,700 and manufacturing overhead of $98,505 were assigned to that department. Requirements 1. Draw a time line for the Assembly Department 2. Use the time line to help you compute the number of equivalent units and the cost per equivalent unit in the Assembly Department for April. 3. Assign total costs in the Assembly Department to (a) units completed and transferred to Programming during April and (b) units still in process at April 30. 4. Prepare a T-account for Work in Process Inventory--Assembly to show its activity during April, including the April 30 balance.

P16A-17B Solution

P16a.17b	Page 898
	Example of the time line from page 882 of the text
Requirement 1. Fill-in the time line for Sue Electronics.
	Conversion Costs
	Start	40%	Complete			100%	Complete
	Direct materials added						Units transferred to Programming
		76,100 units completed and transferred out
	23,900 units incomplete
Requirement 2. Compute the equivalent units in the Assembly Department for April. (For entries with a 0 balance, make sure to enter
"0" in the appropriate column.
	Sue Electronics
	Assembly Department
	Equivalent Unit Computation
	Month Ended April 30
					Equivalent Units
	Flow of Production			Flow of Physical Units	Direct Materials	Conversion Costs
	Units accounted for:
	Completed and transferred out			$ 76,100	$ 76,100	$ 76,100
	Ending work in process, April 30			$ 23,900	$ 23,900	$ 9,560
	Total physical units accounted for			$ 100,000
	Equivalent units				$ 100,000	$ 85,660
Compute the costs per equivalent unit in the Assembly Department for April. (For entries with $0 balance, make sure to enter "0"
in the appropriate column. Round the cost per equivalent unit to two decimal places.)
	Sue Electronics
	Assembly Department
	Cost per Equivalent Unit
	Month Ended April 30
	Flow of Production			Direct Materials	Conversion Costs
	Beginning work in process			$ -	$ -
	Costs added during April			$ 375,720	$ 256,205
	Divide by equivalent units			$ 100,000	$ 85,660
	Cost per equivalent unit			$ 3.76	$ 2.99
Requirement 3. Assign total costs in the Assembly department to (a) units completed and transferred out to Programming and (b)
units still in process at April 30. (Round your answers to the nearest whole dollar. Enter the cost per equivalent unit amounts in the
same order as calculated in the preceding step.)
	Sue Electronics
	Assembly Department
	Cost Assignment
	Month Ended April 30
	Assign Costs:			Direct Materials	Conversion Costs	Total
	a. Completed and transferred out			=$76,100 X (3.76 + 2.99)		$ 513,534
	b. Ending work in process, April 30
	Direct materials			=$23,99 X 3.76		$ 89,797
	Conversion costs				=$9560 X 2.99	$ 28,594
	Total ending work in process inventory, April 30					$ 118,391
	Total cost accounted for					$ 631,925
Requirement 4. Prepare a T-account for Work in process inventory--Assembly to show activity during April, including the
April 30 balance. (Leave unused cells blank.)
	Work in process inventory -- Assembly
	Bal, March 31		0	Transferred to Programming		$ 513,534
	Directy materials		$ 375,720
	Direct labor		157,700
	Manufacturing overhead		98,505
	Bal, April 30		$ 118,391

P16A-19B

P16A-19B	Page 899
	Sheets					Costs
	Beginning work in process inventory				- 0	Beginning work in process inventory		- 0
	Started production				3,300	Costs adding during March:
	Completed and transferred out to					Wood		2,600
	Compression in March				1,900	Adhesives		1,365
						Direct labor		640
	Ending work in process inventory (45%					Manufacturing overhead		2,445
	of the way through the preparation process)				1,400	Total costs		7,050
Requirement 1. Fill-in the time line for the Preparation Department.
	Conversion Costs
	Start	45%	Complete			100%	Complete
							Adhesives added
	Wood added						Units transferred out
		1,900 units completed and transferred out
	1,400 units incomplete
Requirement 2. Compute the equivalent units. (For entries with a 0 balance, make sure to enter "0" in the appropriate column.)
	Root's Exteriors
	Preparation Department
	Equivalent Unit Computation
	Month Ended March 31
					Equivalent Units
	Flow of Production			Flow of Physical Units	Wood	Adhesives	Conversion Costs
	Units accounted for:
	Completed and transferred out			1,900	1,900	1,900	1,900
	Ending work in process, March 31			1,400	1,400	- 0	630
	Total physical units accounted for			3,300
	Equivalent units				3,300	1,900	2,530
Requirement 3. Compute the total cost of the units (sheets) (a) completed and transferred out to the Compression Department and
(b) the units in the Preparation Department's Ending work in process inventory.
Begin by computing the cost per equivalent unit for wood, adhesives and coversion costs. (For entries with $0 balance, make
sure to enter "0" in the appropriate column. Round the cost per equivalent unit to the nearest cent.)
	Root's Exteriors
	Preparation Department
	Cost per Equivalent Unit
	Month Ended March 31
	Flow of Production			Wood	Adhesives	Conversion Costs
	Beginning work in process			$ -	$ -	$ -
	Costs added during April			$ 2,600	$ 1,365	$ 3,085
	Divide by equivalent units			$ 3,300	$ 1,900	$ 2,530
	Cost per equivalent unit			$ 0.79	$ 0.72	$ 1.22
Compute the total cost of the units (a) completed and transferred out to the Compression Department and (b) the units in the
Preparation Department's Ending work in process inventory. (Round your answers to the nearest whole dollar. Enter the cost
per equivalent unit amounts in the same order as calculated in the preceding step.)
	Root's Exteriors
	Preparation Department
	Cost Assignment
	Month Ended March 31
	Assign Costs:			Wood	Adhesives	Conversion Costs	Total
	a. Completed and transferred out			=1,900 X ($0.79 + $0.72 + $1.22)			$ 5,179
	b. Ending work in process, April 30
	Wood			=1,400 X $0.79			$ 1,103
	Adhesives				- 0		$ -
	Conversion costs					= 630 X $1.22	$ 768
	Total ending work in process inventory, April 30						$ 1,871
	Total cost accounted for						$ 7,050
Requirement 4. Prepare the journal entry to record the cost of the sheets completed and transferred out to the Compression Department.
	Journal Entry
	Date	Acounts			Debit	Credit
		Work in process inventory-Compression			5,179
		Work in process inventory-preparation				5,179
Requirement 5. Show the activity for March in the T-account and determine the ending balance. (Leave unused cells blank)
	Work in process inventory -- Preparation
	Bal, Feb 28		0	Transferred out to Compression		$ 5,179
	Directy materials		$ 3,965
	Manufacturing overhead		2,445
	Direct labor		640
	Bal, Mar 31		$ 1,871

Root's Exterios produces exterior siding for homes. The Preparation Department begins with wood, which is chopped into small bits. At the end of the process, an adhesive is added. Then the wood/adhesive mixture goes on to the Compression Department, where the wood is compressed into sheets. Conversion costs are added evenly throughout the preparation process. March data for the Preparation Department are as follows (in millions):

Requirements 1. Draw a time line for the Preparation Department 2. Use the time line to help you compute the equivalent. (Hint: Each direct material added at a different point in the productiion process requires its own equivalent unit computation.) 3. Compute the total costs of the units (sheets) a. Completed and transferred out to the Compression Department. b. In the Preparation Department's Ending work in process inventory. 4. Prepare the journal entry to record the cost of the sheets completed and transferred out to the Compression Department. 5. Post the journal entries to the Work in process inventory--Preparation T-account. What is the ending balance?

P16A-19B Solution

P16A-19B	Page 899
	Sheets					Costs
	Beginning work in process inventory				- 0	Beginning work in process inventory		- 0
	Started production				3,300	Costs adding during March:
	Completed and transferred out to					Wood		2,600
	Compression in March				1,900	Adhesives		1,365
						Direct labor		640
	Ending work in process inventory (45%					Manufacturing overhead		2,445
	of the way through the preparation process)				1,400	Total costs		7,050
Requirement 1. Fill-in the time line for the Preparation Department.
	Conversion Costs
	Start		Complete				Complete
Requirement 2. Compute the equivalent units. (For entries with a 0 balance, make sure to enter "0" in the appropriate column.)
	Root's Exteriors
	Preparation Department
	Equivalent Unit Computation
	Month Ended March 31
					Equivalent Units
	Flow of Production			Flow of Physical Units	Wood	Adhesives	Conversion Costs
	Units accounted for:
	Completed and transferred out						- 0
	Ending work in process, March 31					- 0	- 0
	Total physical units accounted for			- 0
	Equivalent units				- 0	- 0	- 0
Requirement 3. Compute the total cost of the units (sheets) (a) completed and transferred out to the Compression Department and
(b) the units in the Preparation Department's Ending work in process inventory.
Begin by computing the cost per equivalent unit for wood, adhesives and coversion costs. (For entries with $0 balance, make
sure to enter "0" in the appropriate column. Round the cost per equivalent unit to the nearest cent.)
	Root's Exteriors
	Preparation Department
	Cost per Equivalent Unit
	Month Ended March 31
	Flow of Production			Wood	Adhesives	Conversion Costs
	Beginning work in process			$ -	$ -	$ -
	Costs added during April
	Divide by equivalent units			$ -	$ -	$ -
	Cost per equivalent unit			ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Compute the total cost of the units (a) completed and transferred out to the Compression Department and (b) the units in the
Preparation Department's Ending work in process inventory. (Round your answers to the nearest whole dollar. Enter the cost
per equivalent unit amounts in the same order as calculated in the preceding step.)
	Root's Exteriors
	Preparation Department
	Cost Assignment
	Month Ended March 31
	Assign Costs:			Wood	Adhesives	Conversion Costs	Total
	a. Completed and transferred out			=1,900 X ($0.79 + $0.72 + $1.22)			ERROR:#DIV/0!
	b. Ending work in process, April 30
	Wood			=1,400 X $0.79			ERROR:#DIV/0!
	Adhesives				- 0		$ -
	Conversion costs					= 630 X $1.22	ERROR:#DIV/0!
	Total ending work in process inventory, April 30						ERROR:#DIV/0!
	Total cost accounted for						ERROR:#DIV/0!
Requirement 4. Prepare the journal entry to record the cost of the sheets completed and transferred out to the Compression Department.
	Journal Entry
	Date	Acounts			Debit	Credit
		Work in process inventory-Compression			ERROR:#DIV/0!
		Work in process inventory-preparation				ERROR:#DIV/0!
Requirement 5. Show the activity for March in the T-account and determine the ending balance. (Leave unused cells blank)
	Work in process inventory -- Preparation
	Bal, Feb 28		0	Transferred out to Compression		ERROR:#DIV/0!
	Directy materials
	Manufacturing overhead
	Direct labor
	Bal, Mar 31		ERROR:#DIV/0!

P18.24a

P18.24a	Page 979
Requirement 1. Compute revenue and variable costs for each show.
	Revenue is determined by multiplying the number of tickets sold by the ticket price.
	________ tickets sold at $___ per ticket = _____ * _____ =
	Variable costs are the _______________________ plus the cost of _________________.
	______________ times $_____ each =
	____________ times $__ =
		Variable costs per show =		- 0
Requirement 2. Use the income statement equation approach to compute the number of shows British Productions must perform
each year to break even.
	Breakeven formula using income statement equation
	Sales Revenue - variable expenses - fixed expenses = 0
	Sales Revenue = (Sales price per unit X Units Sold)
	Variable expenses = (Variable expense per unit X Units Sold)
	(Sales price per unit X Units Sold) - (Variable expense per unit X units sold) - Fixed expenses = operating income.
	(nn,nnn X Units sold) - (nn,nnn X Units sold) - nnn,nnn = 0
	(nn,nnn - nn,nnn) X units sold - nnn,nnn = 0
	nn,nnn X Units sold = nnn,nnn
	nnn,nnn/nn,nnn =		shows needed annually to break even.
Requirement 3. Use the contribution margin approach to compute the number of shows needed each year to earn a profit of
$3,825,000.00
	Units sold = (fixed costs + operating income) / contribution margin per unit
	Contribution margin = sales revenue minus variable costs.
	Contribution margin per unit = sales revenue per unit minus variable costs per unit.
	nn,nnn unit sales revenue minus nn,nnn unit variable expenses = nn,nnn unit contribution margin.
	Units sold = (nnn,nnn + $n,nnn,nnn) / nn,nnn
	Units sold = $n,nnn,nnn / nn,nnn =			shows annually to earn a profit of $3,825,000
Is this profit goal realistic? Give your reasoning.
The profit goal of $3,825,000 is ____________ since British Productions currently performs ____ shows a year.
Requirement 4. Prepare British Productions' contribution margin income statement for 120 shows performed in 2011. Report only
two categories of costs: variable and fixed.
British Productions
Contribution Margin Income Statement
Year Ended December 31, 2011
Sales revenue
Variable costs
Contribution margin	- 0
Fixed costs
Operting income (loss)	$ -

P18.24a Solution

P18.24a	Page 979
Requirement 1. Compute revenue and variable costs for each show.
	Revenue is determined by multiplying the number of tickets sold by the ticket price.
	1,200 tickets sold at $50 per ticket = 1,200 * 50 =				$ 60,000
	Variable costs are the cast members payments plus the cost of program printing.
	70 cast members times $300 each =			21,000
	1,200 guests (tickets sold) times $7 =			8,400
		Variable costs per show =		29,400
Requirement 2. Use the income statement equation approach to compute the number of shows British Productions must perform
each year to break even.
	Breakeven formula using income statement equation
	Sales Revenue - variable expenses - fixed expenses = 0
	Sales Revenue = (Sales price per unit X Units Sold)
	Variable expenses = (Variable expense per unit X Units Sold)
	(Sales price per unit X Units Sold) - (Variable expense per unit X units sold) - Fixed expenses = operating income.
	(60,000 X Units sold) - (29,400 X Units sold) - 459,000 = 0
	(60,000 - 29,400) X units sold - 459,000 = 0
	30,600 X Units sold = 459,000
	459,000/30,600 =	15	shows needed annually to break even.
Requirement 3. Use the contribution margin approach to compute the number of shows needed each year to earn a profit of
$3,825,000.00
	Units sold = (fixed costs + operating income) / contribution margin per unit
	Contribution margin = sales revenue minus variable costs.
	Contribution margin per unit = sales revenue per unit minus variable costs per unit.
	60,000 unit sales revenue minus 29,400 unit variable expenses = 30,600 unit contribution margin.
	Units sold = (459,000 + $3,825,000) / 30,600
	Units sold = $4,284,000 / 30,600 =		140	shows annually to earn a profit of $3,825,000
Is this profit goal realistic? Give your reasoning.
The profit goal of $3,825,000 is unrealistic since British Productions currently performs 120 shows a year.
Requirement 4. Prepare British Productions' contribution margin income statement for 120 shows performed in 2011. Report only
two categories of costs: variable and fixed.
British Productions
Contribution Margin Income Statement
Year Ended December 31, 2011
Sales revenue	$ 7,200,000	60,000 x 120
Variable costs	3,528,000	29,400 x 120
Contribution margin	3,672,000
Fixed costs	459,000
Operting income (loss)	$ 3,213,000

P18.26a

Page 980

Fixed costs:

Office rent

$ 8,200

Dep of off furn

1,500

Utilities

2,300

Special phone lines

1,300

Online brokerage connection

2,900

Salary - Financial Analyst

11,800

Total Fixed Costs

$ 28,000

Variable costs:

Financial planner payments

of revenue

Advertising

12%

of revenue

Supplies and postage

of revenue

Usage fees (lines)

of revenue

Total variable costs

30%

of revenue

Contribution margin = Sales - variable costs

Contribution margin ratio = contribution margin / Sales

Breakeven in dollas = Fixed Costs / CM Ratio

Income Statement Equation:

Profit = (Sales - Variable Expenses) - Fixed Expenses

Sales = Selling price per unit X Quantity sold = P X Q; Variable expenses = Variabile expenses per unit X Quantity sold = V X Q

Profit = (P X Q - V X Q) - Fixed expenses

Requirement 1. Use the contribution margin ratio CVP formula to compute Big Time's breakeven revenue in

dollars. If the average trade leads to $800 in revenue for Big Time, how many trades must be made to break even?

Big Time must earn

in sales dollars to break even.

=$nnn x nn% = $nnn variable costs per trade

=$nnn - nnn = $nnn Contribution margin

Big Time must make

trades to break even.

=nnn / $nnn = nn% Contribution margin ratio

=nn,nnn / nn% = $nn,nnn

dollars to break even

=$nn,nnn / $nnn = nn

trades to break even

Requirement 2. Use the income statement equation approach to compute the dollar revenues needed to earn a traget monthly operating income of $11,200.

Profit = (P X Q - V X Q) - Fixed expenses

nn,nnn = (nnn X Q - nnn X Q) - nn,nnn

nn,nnn = nnnQ - nn,nnn

nn,nnn = nnnQ

nn,nnn divided by nnn = nn

nn units X $nnn = $nn,nnn in revenue to earn $11,200 in operating income.

Big Time must needs

in revenues to earn a target monthly operatin income of $11,200

Requirement 3. Graph Big Time's CVP relationships. Assume that an average trade leads to $800 in revenue for Big Time. Show the breakeven point, the sales

revenue line, the fixed cost line, the total cost line, the operating loss area, the operating income area, and the sales in units (trades) and dollars when monthly

operating income of $11,200 is earned. The graph should range from 0 to 80 units.

Begin graphing the CVP relationships by first plotting the two points: breakeven point and the point where monthly operating income of

$11,200 is earned.

Next plot the sales revenue line, fixed cost line, and the total cost line.

The following are the correctly shaded areas for operating income and operating loss.

Requirement 4. Suppose that the average revenue Big Time earns increases to $900 per trade. Compute the new breakeven point in trades.

How does this affect the breakeven point? (Round your answer to the nearest whole number.)

With the increase in the average revenue per trade, the breakeven point in numbers of trades ____________ to __.

Dollars (Thousands)

Units (Trades)

0 10 20 30 40 50 60 70 80

Dollars (Thousands)

Units (Trades)

0 10 20 30 40 50 60 70 80

Monthly income

Break even point

Fixed cost line

Total cost line

Sales revenue line

Dollars (Thousands)

Units (Trades)

0 10 20 30 40 50 60 70 80

Fixed cost line

Total cost line

Sales revenue line

Loss

Income

P18.26a Solution

P18.26a

Page 980

Fixed costs:

Office rent

$ 8,200

Dep of off furn

1,500

Utilities

2,300

Special phone lines

1,300

Online brokerage connection

2,900

Salary - Financial Analyst

11,800

Total Fixed Costs

$ 28,000

Variable costs:

Financial planner payments

of revenue

Advertising

12%

of revenue

Supplies and postage

of revenue

Usage fees (lines)

of revenue

Total variable costs

30%

of revenue

Contribution margin = Sales - variable costs

Contribution margin ratio = contribution margin / Sales

Breakeven in dollas = Fixed Costs / CM Ratio

Income Statement Equation:

Profit = (Sales - Variable Expenses) - Fixed Expenses

Sales = Selling price per unit X Quantity sold = P X Q; Variable expenses = Variabile expenses per unit X Quantity sold = V X Q

Profit = (P X Q - V X Q) - Fixed expenses

Requirement 1. Use the contribution margin ratio CVP formula to compute Big Time's breakeven revenue in

dollars. If the average trade leads to $800 in revenue for Big Time, how many trades must be made to break even?

Big Time must earn

$40,000

in sales dollars to break even.

=$800 x 30% = $240 variable costs per trade

=$800 - 240 = $560 Contribution margin

Big Time must make

trades to break even.

=560 / $800 = 70% Contribution margin ratio

=28,000 / 70% = $40,000

dollars to break even

=$40,000 / $800 = 50

trades to break even

Requirement 2. Use the income statement equation approach to compute the dollar revenues needed to earn a traget monthly operating income of $11,200.

Profit = (P X Q - V X Q) - Fixed expenses

11,200 = (800 X Q - 240 X Q) - 28,000

11,200 = 560Q - 28,000

39,000 = 560Q

39,000 divided by 560 = 70

70 units X $800 = $56,000 in revenue to ear $11,200 in operating income.

Big Time must needs

$56,000

in revenues to earn a target monthly operatin income of $11,200

Requirement 3. Graph Big Time's CVP relationships. Assume that an average trade leads to $800 in revenue for Big Time. Show the breakeven point, the sales

revenue line, the fixed cost line, the total cost line, the operating loss area, the operating income area, and the sales in units (trades) and dollars when monthly

operating income of $11,200 is earned. The graph should range from 0 to 80 units.

Begin graphing the CVP relationships by first plotting the two points: breakeven point and the point where monthly operating income of

$11,200 is earned.

Next plot the sales revenue line, fixed cost line, and the total cost line.

The following are the correctly shaded areas for operating income and operating loss.

Requirement 4. Suppose that the average revenue Big Time earns increases to $900 per trade. Compute the new breakeven point in trades.

How does this affect the breakeven point? (Round your answer to the nearest whole number.)

With the increase in the average revenue per trade, the breakeven point in numbers of trades decreased to 44.

Dollars (Thousands)

Units (Trades)

0 10 20 30 40 50 60 70 80

Dollars (Thousands)

Units (Trades)

0 10 20 30 40 50 60 70 80

Monthly income

Break even point

Fixed cost line

Total cost line

Sales revenue line

Dollars (Thousands)

Units (Trades)

0 10 20 30 40 50 60 70 80

Fixed cost line

Total cost line

Sales revenue line

Loss

Income

Principles of Management Accounting Excel Problems

P16A-17B

P16A-17B Solution

P16A-19B

P16A-19B Solution

P18.24a

P18.24a Solution

P18.26a

P18.26a Solution

Sheet3