Managerial accounting chapter 8 master budget continued

CH.8 Excel

Doing calendar Q2
Doing calendar Q2
Doing calendar Q2
Doing calendar Q2
Doing calendar Q2
Doing calendar Q2
Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Units Sales	20,000	50,000	30,000	25,000	15,000	32,000	36,000	42,000	66,000
Price each	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00
Budgeted Sales	200,000	500,000	300,000	250,000	150,000	320,000
Period ending cash	CASH	90,000	30%	uncollected
Collections	75,000	25%/30% of end Q1 A/R will be collected
70%	of current priod	175,000	105,000	224,000
25%	of prior period	62,500	37,500
Sum	Cash collected	250,000	167,500	261,500	679,000	679,000
Looking at Q2
Ending inventory units	20%	Mar	Apr	May	Jun	Jul	Aug
Units Sales	30,000	25,000	15,000	32,000	36,000	42,000
Budget Ending Inventory	4,000	3,000	6,400	7,200	8,400	[20% next mo. Sales]
Sales + Ending	Given	28,000	21,400	39,200	44,400
Less Beginning = prior month-end	(4,000)	(3,000)	(6,400)	(7,200)
= Unit Production	24,000	18,400	32,800	37,200
Cost/Lb	Cost/Unit
Quantity per unit in Lbs.	5.00	$ 0.40	$ 2.00	Ending Inventory % next month	10%	Looking at Q2
Mar	Apr	May	Jun	Jul
= Unit Production	- 0	24,000	18,400	32,800	37,200
Required for Production/Lbs	- 0	120,000	92,000	164,000	186,000
$s Into FG for Production	$ 48,000	$ 36,800	$ 65,600	$ 150,400	Qtr. Total
Budget Ending Inventory	13,000	9,200	16,400	18,600	10% of following Month
Sales + Ending	129,200	108,400	182,600
Less Beginning = prior month-end	(13,000)	(9,200)	(16,400)
Qty. Purchase [additions] of Raw material	116,200	99,200	166,200	Material budget
$s. Purchase of Raw material	$ 46,480	$ 39,680	$ 66,480	Material budget	$ 0.40	per lb.
Apr	Apr.
CASH	Ending A/P	$ 12,000	$ 12,000	Beg	$ 12,000
Cr. To A/P = purchases	$ 46,480	$ 39,680	$ 66,480	$ 46,480	Add	$ 46,480
Pay 50% current [given company policy]	$ 23,240	$ 19,840	$ 33,240	1/2 April	$ (23,240)	Paid	$ (35,240)
Pay prior	$ 12,000	$ 23,240	$ 19,840	Cash budget	$ 35,240	$ 23,240
Total paid	$ 35,240	$ 43,080	$ 53,080	Cash budget	$ 131,400	Paid	A/P	Ending
Ending A/P [Beginning + Additions - payments]	$ 23,240	$ 19,840	$ 33,240	Qtr. Total
Guaranteed	Hours	Rate
Payment for quarter	1500	$ 10.00
Required Hrs. per unit	0.05	3	minutes
DL$s. per unit	$ 0.50	700
used for ending Q2 inventory
Apr	May	Jun	Qtr sum
= Unit Production	24,000	18,400	32,800	75,200
HRs of Prodctn. at Required per unit of 0.05 hrs. ea.	1,200	920	1,640	3,760
DL Cost of production into units at 10 per Hr	$ 12,000	$ 9,200	$ 16,400	37,600
unfavorable variance of	$ 8,800
Hrs paid	1,500	1,500	1,640	4,640	880	Hours
$s paid	$ 15,000	$ 15,000	$ 16,400	46,400
ADDED
Productivity at budget earned HRs/paid HRs	80%	61%	100%	Spend > Used by	$ 8,800	into CoGS
*******
Variable OH $s per HR	$ 20.00	Given: rate is per DL hr.
Required Hrs. per unit	0.05	Hrs. per unit	3	minutes
Variable OH $s per unit	$ 1.00
Fxd. MOH per month	$50,000
Non cash MOH	$20,000
Cash Mfg. OH	$30,000
Actual Overhead rates NOT predetermined rates
Apr	May	Jun	Qtr. Sum	Required Hrs. per unit	0.05
= Unit Production	24,000	18,400	32,800	75,200	Fxd. OH spending	$50,000	$50,000	$50,000
# Hrs.	1,200	920	1,640
HRs of Prodctn. at Required per unit of 0.05 hrs. ea.	1,200	920	1,640	3,760	per Hr.	41.67	54.35	30.49
VOH Cost of production at $20 per DL Hr	$24,000	$18,400	$32,800	per unit	2.08	2.72	1.52
Fixed manufacturing OH per period	$50,000	$50,000	$50,000	$ 150,000	ADDED
Fxd. + Var. Mfg. OH	Total MOH per Month	$74,000	$68,400	$82,800	$ 225,200	$$$$	QTY	$ 225,200
Fxd. Mfg. OH rate/hr.	$ 41.67	$ 54.35	$ 30.49	39.89	$ 150,000	3,760	3,760
Fxd. Mfg. OH unit	$ 2.08	$ 2.72	$ 1.52	$ 1.99	0.05	hrs. per unit	59.89
Fxd. Mfg. OH rate/hr.	Quarter averageò	Apr	May	Jun	per Hr.
Budgeted MOH rate per period Fxd.+ Var.	61.67	74.35	50.49	59.89	Fxd + Var	$ 41.67	$ 54.35	$ 30.49	Fxd rate
$ 20.00	$ 20.00	$ 20.00	V. Rate
0	Non-cash expense	($20,000)	($20,000)	($20,000)	$ 61.67	$ 74.35	$ 50.49
Cash MOH	$ 54,000	$ 48,400	$ 62,800	$ 165,200
Qtr Total
Product Cost using Qtr. Average Fxd.unit	Variable	Fixed
0.40	$5.00	Materials	$ 2.00	5 lbs	$0.40/lb
0.05	$10.00	DL	$ 0.50	This example: Std. Hrs./unit	$ 10
0.05	$20.00	V Mfg. OH	$ 1.00	0.05
0.05	$59.89	F MFG. OH	1.99	This example: Actual rate for Qtr.	$ 0.50
Sum	$ 3.50	$ 1.99
$ 5.49	average per unit for Qtr
Product cost without labor variance
CoGS chart	Qty	4,000	No WIP in Example
given	$ 5.49	$ 21,979	Beginning Materials
Excel C	Beginning FG	$ 22,000	4,000	Excel B	Beginning FG
For Inome Statement
+ Input addtions
Excel C	Materials	$ 150,400	CoGS =
Excel D	Labor	$ 46,400	with variance	+ Beginning
Excel E	Overhead	$ 225,200	75,200	Excel B	+ Additions
Total	$ 422,000	- Ending	Ending Inventory Q2
Average per FG unit	5.49	Excel E	= CoGS
$39,562	$ 0.40	per lb.
- Ending	7,200	(39,562)	7,200	Excel B	End Qty.	7,200	FG	18,600	# lbs.
= CoGS	$ 404,438	Each	5.49	End $s	RM	$ 7,440	$47,002
$47,002	total Inv.
Variable unit period cost	$ 0.50
Fixed period costs	$ 70,000
Non cash expenses	$ 10,000
Cash Expense	$ 60,000
Selling and Administrative
Period Costs
Apr	May	Jun	Qtr. Sum
Units Sales	25,000	15,000	32,000	72,000
Variable Period costs/unit sold	$ 0.50	$ 0.50	$ 0.50
Variable unit period expenses	12,500	7,500	16,000	36,000
Fixed Period Expense	$ 70,000	$ 70,000	$ 70,000	210,000
Total	82,500	77,500	86,000	246,000	to Income statement
Non cash portion	(10,000)	(10,000)	(10,000)	(30,000)
Cash Period Expense	72,500	67,500	76,000	216,000	CASH
Example uses the Direct Method of Receipts and Disbusrsement
for Cash Budgets; large companies use the Balance Ssheet Indirect method
We'll assume the debt exists for full quarter; borrowing may be drwn down as needed
and result is different result
Target Minimum Cash Balance	$10,000	given
Quarter June 30
Beginning Cash Balance	$ 40,000	Given
+ Collections	$ 679,000	Excel A
Cash avaialble	$ 719,000
Cash disbursements:
Materials	$ 131,400	Excel C
Direct labor	$ 46,400	Excel D
Mfg. Overhead=Fxd. + Var - Non-cash	$ 165,200	Excel E	required without interest	$ 35,000
Selling/Admin.	$ 216,000	Excel G	Borrowing	$ 19,000	Average borrowing given
Equipment Purchased	$ 125,000	Given data	# months	3
Interest	$ 285	6%	interest	$ 285	6%
Total	$ 684,285
Management judged Cash balance adequate to operate did not pay down debt
Cash Balance	$ 34,715	Could reduce Cash or change borrowing
Debt on BS	$ - 0	Excel K Below	Can balance BS with CASH or With Borrowing
Royal Company
Statement of Income
QE: 6/30	GAAP, FAC
Sales	720,000	100.0%	Excel A
Less: Cost of Goods Sold	$ 404,438	56.2%	Excel F
Gross Margin	$ 315,562	43.8%
Selling & Admin, Expense	246,000	34.2%	Excel G
Operating Income	69,562	9.7%
Interest Expense	$ 285	0.0%
Income before taxes	$ 69,847	9.7%
Beg Cash	$ 40,000
Royal Company	Period Cash	$ 34,715
Month ending 6/30	$ 74,715
Balance Sheet
Assets	debt to Balance
Cash	$ 74,715	Excel H	Keep Cash	392,717	Assets
sold	320,000	Accounts receivable	96,000	Excel A	$ (33,240)
70% collected	(224,000)	Inventory	47,002	Excel F	$ (200,000)
30% not collected	96,000	Land	50,000	Given	$ (156,422)
Equipment	125,000	Given	3,055
Statement of Retained Earnings	Total assets	392,717
Beginning	$ 86,575
less: Dividends	0	Liabilities & Stockholders' Equity
Plus: Income	$ 69,847	Accounts Payable	$ 33,240	Excel C
Ending Retained Earnings	$ 156,422	Long term debt	$ 3,055	Excel H	Keep Cash	Back into to Balance
Common stock	$ 200,000	Given
Retained Earnings	$ 156,422	ççright
Total Liabilities & Stockholders' Equity	$ 392,717
Minimize cash pay debt: balance with cash	Cash	$ 71,660	Accounts Payable	$ 33,240	debt to Balance
Accounts receivable	$ 96,000	Common stock	$ 200,000	$ 389,662	assets
Inventory	$ 47,002	Retained Earnings	$ 156,422	$ (33,240)
Land	$ 50,000	$ 389,662	$ (200,000)
Equipment	$ 125,000	Debt to balance	$ - 0	$ (156,422)	pay down debt to -0-
$ 389,662	$ 389,662	$ - 0

Excel A Sales Budget

Excel D Direct Labor

Excel E Manufacturing Overhead

Excel F CoGS

Excel G S&A Expense

Excel B FG budget

Excel H Cash

Excel I Statement of Income

Excel J // Balance Sheet

2

1

5

3

6

7

4

8

9

10

Excel C Materials

invested capital

Sheet2

Sheet3