Please help..

tonya_hopes_draft.xlsx

Home >Business & Finance homework help >Accounting homework help >Please help..

Information

		Q1	Q2	Q3	Q4	next year q1
Sales
Expected sales (units)	Units	20,000	22,000	24,000	26,000	28,000
Selling price per unit	$ 80
FG Ending inventory 5% of the next quarter's budgeted sales volume	Units	1,100	1,200	1,300	1,400
Cash Sales	25%
Credit Sales	75%
Raw Material
RM requirement (8 rm for making 2 bats)	Units
CP per unit	$ 45
RM Ending Inventory 10% of next quarter’s raw material needs to be on hand at the end of the budget period.
Labor
1 hour 10 bats by 1 worker
CP per hour	$ 60
Manufacturing overhead cost:
Variable	$ 2.40
Fixed (Includes depreciation expense of $25,000)		$ 286,440	$ 286,440	$ 286,440	$ 286,440
Selling overhead cost:
Variable (20% of sales revenue)
Fixed		$ 250,000	$ 250,000	$ 250,000	$ 250,000
Collection from accounts receivables
Same quarter	80%
Next quarter	20%
Last year sales AR (20%)	$ 75,000
Payment to accounts payable
Same quarter	75%
Next quarter	25%
Last year sales AP (25%)	$ 23,000
Investment in new equipment
Cost		$ 200,000
Payment for the same		$ 100,000	$ 50,000	$ 50,000
Cash Management
Cash on hand		$ 200,000
Minimum cash balance at or above	$ 200,000
Short term borrowings and repayments in multiples of	$ 5,000
No interest is charged if the loans are repaid by the end of the next quarter
Bank Loan at beginning		$ - 0
Other details (end of the year balances)
Property Plant and Equipment (Net)	$ 750,000		200,000	800,000
Long Term Liabilities	$ - 0		650,000	195,946
Common Stock	$ 800,000		75,000	23,000
Retained Earnings	$ 1,094,925		45,225
Note that this is a corporation, so the equity section of the balance sheet should include common stock and retained earnings.			46,725
			1,016,950	1,018,946
				(1,995)

Sales

Beth’s Bats
Sales Budget
For the year 2014
Particulars	Quarter				Year	Next Year	Last Year
	1	2	3	4		Q1	Q4
Budgeted Sales of Bats	20,000	22,000	24,000	26,000	92,000	28,000	18,000
x Price per bat	$ 80	$ 80	$ 80	$ 80	$ 80
Total Gross Sales	$ 1,600,000	$ 1,760,000	$ 1,920,000	$ 2,080,000	$ 7,360,000
Less: Sales didcount & allowances	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
Total Net Sales	$ 1,600,000	$ 1,760,000	$ 1,920,000	$ 2,080,000	$ 7,360,000
Sales Bifercation:
Cash Sales	$ 400,000	$ 440,000	$ 480,000	$ 520,000	$ 1,840,000
Credit Sales	$ 1,200,000	$ 1,320,000	$ 1,440,000	$ 1,560,000	$ 5,520,000
Total Sales	$ 1,600,000	$ 1,760,000	$ 1,920,000	$ 2,080,000	$ 7,360,000

Production

Beth’s Bats
Production Budget
For the year 2014
Particulars	Quarter				Year	Next Year		Last Year
	1	2	3	4		Q1		Q4
Budgeted number of bat sales	20,000	22,000	24,000	26,000	92,000	28,000	30,000	18,000
Add: Budgeted Ending Inventory of Bats	1,100	1,200	1,300	1,400	5,000	1,500		1,000
Total Production need	21,100	23,200	25,300	27,400	97,000	29,500		19,000
Less: Beginning Inventory of Bats	(1,000)	(1,100)	(1,200)	(1,300)	(4,600)	(1,400)		(900)
Number of Bats to be manufactured	20,100	22,100	24,100	26,100	92,400	28,100		18,100

RM Purchase

Beth’s Bats
Raw Material (foot board) purchase budget
For the year 2014
Particulars	Quarter				Year	Last Year
	1	2	3	4		Q4
Budgeted number of bats to be manufactured	20,100	22,100	24,100	26,100	92,400	18,100
x Foot board required for each bat	4	4	4	4		4
Total foot board needed	80,400	88,400	96,400	104,400	369,600	72,400
Add: Budgeted Ending Inventory of foot board	8,840	9,640	10,440	11,240	40,160	8,040
Total raw material (footboard) requirement	89,240	98,040	106,840	115,640	409,760	80,440
Less: Beginning Inventory of foot board	(8,040)	(8,840)	(9,640)	(10,440)	(36,960)	(7,240)
Raw material units to be purchased (Number of foot boards to be purchased)	81,200	89,200	97,200	105,200	372,800	73,200
x Raw material cost per unit	$ 5.63	$ 5.63	$ 5.63	$ 5.63		$ 5.63
Raw material purchase cost	$ 456,750	$ 501,750	$ 546,750	$ 591,750	$ 2,097,000	$ 411,750
Material requirement is 50% of a board.
Unit cost is $45 each board.

Direct labor

Beth’s Bats
Direct labor cost budget
For the year 2014
Particulars	Quarter				Year	Last Year
	1	2	3	4		Q4
Budgeted number of bats to be manufactured	20,100	22,100	24,100	26,100	92,400	18,100
x Labor hours required for each bat	0.10	0.10	0.10	0.10		0.10
Total hours needed	2,010	2,210	2,410	2,610	9,240	1,810
x Labor rate per hour	$ 60	$ 60	$ 60	$ 60		$ 60
Direct labor cost	$ 120,600	$ 132,600	$ 144,600	$ 156,600	$ 554,400	$ 108,600

Mfg overhead

Beth’s Bats
Manufacturing overhead cost budget
For the year 2014
Particulars	Quarter				Year	Last Year
	1	2	3	4		Q4
Variable Manufacturing Overhead:
Budgeted production units	20,100	22,100	24,100	26,100	92,400	18,100
x Manufactuiring Variable overhead cost per unit	$ 2.40	$ 2.40	$ 2.40	$ 2.40		$ 2.40
Total Variable manufacturing overhead (A)	$ 48,240	$ 53,040	$ 57,840	$ 62,640	$ 221,760	$ 43,440
Fixed Manufacturing Overhead:
Depreciation	$ 25,000	$ 25,000	$ 25,000	$ 25,000	$ 100,000	$ 25,000
Other Fixed manufacturing overhead	$ 261,440	$ 261,440	$ 261,440	$ 261,440	$ 1,045,760	$ 261,440
Total Fixed manufacturing overhead (B)	$ 286,440	$ 286,440	$ 286,440	$ 286,440	$ 1,145,760	$ 286,440
Total Manufacturing overhead (A+B)	$ 334,680	$ 339,480	$ 344,280	$ 349,080	$ 1,367,520	$ 329,880
Less: Depreciation	$ (25,000)	$ (25,000)	$ (25,000)	$ (25,000)	$ (100,000)	$ (25,000)
Cash Disbursement for Manufaturing overhead	$ 309,680	$ 314,480	$ 319,280	$ 324,080	$ 1,267,520	$ 304,880
Don't take out depreciation here

Mfg c.p.u.

Beth’s Bats
Budgeted Manufacturing Cost Per Unit
For the year 2014
Particulars	Quarter				Year	Last Year
	1	2	3	4		Q4
Direct Material Purchases	$ 456,750	$ 501,750	$ 546,750	$ 591,750	$ 2,097,000	$ 411,750
Add: Beginning Direct Material	$ 45,225	$ 49,725	$ 54,225	$ 58,725	$ 45,225	$ 40,725
Less: Ending Direct Material	$ (49,725)	$ (54,225)	$ (58,725)	$ (63,225)	$ (63,225)	$ (45,225)
Direct Material cost	$ 452,250	$ 497,250	$ 542,250	$ 587,250	$ 2,079,000	$ 407,250
Direct Labor cost	$ 120,600	$ 132,600	$ 144,600	$ 156,600	$ 554,400	$ 108,600
Manufacturing overhead cost	$ 334,680	$ 339,480	$ 344,280	$ 349,080	$ 1,367,520	$ 329,880
Total Manufacturing cost	$ 907,530	$ 969,330	$ 1,031,130	$ 1,092,930	$ 4,000,920	$ 845,730
÷ Number of units produced	20,100	22,100	24,100	26,100	92,400	18,100
Budgeted Manufacturing Cost Per Unit	$ 45	$ 44	$ 43	$ 42	$ 43	$ 47
One amount for the year. Use the example in the book.

COGS

Beth’s Bats
Cost of Goods Sold Budget
For the year 2014
Particulars	Quarter				Year
	1	2	3	4
Beginning Finished goods inventory	$ 46,725	$ 49,666	$ 52,633	$ 55,621	$ 46,725
Add: Cost of goods manufactured	$ 907,530	$ 969,330	$ 1,031,130	$ 1,092,930	$ 4,000,920
Goods available for sale	$ 954,255	$ 1,018,996	$ 1,083,763	$ 1,148,551	$ 4,047,645
Less: Ending Finished goods inventory	$ (49,666)	$ (52,633)	$ (55,621)	$ (58,625)	$ (58,625)
Cost of goods sold	$ 904,590	$ 966,363	$ 1,028,142	$ 1,089,927	$ 3,989,021
Use the example from the book.

Selling & Admin

Beth’s Bats
Selling and Administrative expense budget
For the year 2014
Particulars	Quarter				Year
	1	2	3	4
Variable S&D Overhead:
Sales Revenue	1,600,000	1,760,000	1,920,000	2,080,000	7,360,000
x % of sales revenue	20%	20%	20%	20%
Total Variable S&D overhead	$ 320,000	$ 352,000	$ 384,000	$ 416,000	$ 1,472,000
Fixed S&D Overhead	$ 250,000	$ 250,000	$ 250,000	$ 250,000	$ 1,000,000
Total S&D overhead	$ 570,000	$ 602,000	$ 634,000	$ 666,000	$ 2,472,000

Income Stat

Beth’s Bats
Budgeted Income Statement
For the year 2014
Particulars	Quarter				Year
	1	2	3	4
Net Sales	$ 1,600,000	$ 1,760,000	$ 1,920,000	$ 2,080,000	$ 7,360,000
Less: Cost of goods sold	$ (904,590)	$ (966,363)	$ (1,028,142)	$ (1,089,927)	$ (3,989,021)
Gross margin	$ 695,410	$ 793,637	$ 891,858	$ 990,073	$ 3,370,979
Less: Selling and Administration Expenses	$ (570,000)	$ (602,000)	$ (634,000)	$ (666,000)	$ (2,472,000)
Net Operating profit	$ 125,410	$ 191,637	$ 257,858	$ 324,073	$ 898,979
Less: Interest Expense	$ - 0	$ - 0	$ - 0	$ - 0	$ - 0
Net Income	$ 125,410	$ 191,637	$ 257,858	$ 324,073	$ 898,979
Use the example from the book.

Cash Receipt

Beth’s Bats
Budgeted Cash Receipt
For the year 2014
Particulars	Quarter				Year
	1	2	3	4
Cash Sales	$ 400,000	$ 440,000	$ 480,000	$ 520,000	$ 1,840,000
Collection from Account receivable:
Same month	$ 960,000	$ 1,056,000	$ 1,152,000	$ 1,248,000	$ 4,416,000
Next Month	$ 75,000	$ 240,000	$ 264,000	$ 288,000	$ 867,000
Total Cash collection from account receivables	$ 1,035,000	$ 1,296,000	$ 1,416,000	$ 1,536,000	$ 5,283,000
Total Cash reciept	$ 1,435,000	$ 1,736,000	$ 1,896,000	$ 2,056,000	$ 7,123,000
Use the example from the book.
Parts are missing.

Cash Payt.

Beth’s Bats
Budgeted Cash Payment
For the year 2014
Particulars	Quarter				Year
	1	2	3	4
Payment to Accounts Payables:
Same month	$ 342,563	$ 376,313	$ 410,063	$ 443,813	$ 1,572,750
Next Month	$ 23,000	$ 114,188	$ 125,438	$ 136,688	$ 399,313
Total Cash collection from account receivables	$ 365,563	$ 490,500	$ 535,500	$ 580,500	$ 1,972,063
Other exepnses:
Direct labor expense	$ 120,600	$ 132,600	$ 144,600	$ 156,600	$ 554,400
Manufacturing overhead cash expense	$ 309,680	$ 314,480	$ 319,280	$ 324,080	$ 1,267,520
Selling and distribution expenses	$ 570,000	$ 602,000	$ 634,000	$ 666,000	$ 2,472,000
	$ 1,000,280	$ 1,049,080	$ 1,097,880	$ 1,146,680	$ 4,293,920
Equipment purchased	$ 100,000	$ 50,000	$ 50,000	$ - 0	$ 200,000
Total Cash Disbursement	$ 1,465,843	$ 1,589,580	$ 1,683,380	$ 1,727,180	$ 6,465,983
Use the example from the book.
Parts are missing.

Cash Budget

Beth’s Bats
Budgeted Cash Budget
For the year 2014
Particulars	Quarter				Year
	1	2	3	4
Beginning Cash Balance	$ 200,000	$ 204,158	$ 315,578	$ 528,198	$ 200,000
Add: Budgeted Cash Receipts	$ 1,435,000	$ 1,736,000	$ 1,896,000	$ 2,056,000	$ 7,123,000
Total Cash Available for Use	$ 1,635,000	$ 1,940,158	$ 2,211,578	$ 2,584,198	$ 7,323,000
Less: Cash Disbursements	$ (1,465,843)	$ (1,589,580)	$ (1,683,380)	$ (1,727,180)	$ (6,465,983)
Cash Surplus/(Deficit)	$ 169,158	$ 350,578	$ 528,198	$ 857,018	$ 857,018
Financing:
Borrowings	$ 35,000				$ 35,000
Repayments		$ (35,000)			$ (35,000)
Interest					$ - 0
Net Cash from Financing	$ 35,000	$ (35,000)	$ - 0	$ - 0	$ - 0
Budgeted Ending Cash Balance	$ 204,158	$ 315,578	$ 528,198	$ 857,018	$ 857,018

Balance sheet

Beth’s Bats Corporation			Beth’s Bats Corporation
Balance Sheet as on 31st Dec 2013			Balance Sheet as on 31st Dec 2013
Particulars	Amount	Amount	Particulars	Amount	Amount
Current Aseets			Current Aseets
Cash on hand	$ 200,000		Cash on hand	$ 857,018
Account Receivable	$ 75,000		Account Receivable	$ 312,000
RM Stock	$ 45,225		RM Stock	$ 63,225
FG Stock	$ 46,725		FG Stock	$ 58,625
Other Current Assets (Balancing figure)	$ 1,995		Other Current Assets (Balancing figure)	$ 1,995
Total Current Assets		$ 368,945	Total Current Assets		$ 1,292,862
Fixed Assets:
Property Plant and Equipment (Net)		$ 650,000	Property Plant and Equipment (Net)		$ 750,000
Total Assets		$ 1,018,945	Total Assets		$ 2,042,862
Current Liabilities:			Current Liabilities:
Accounts Payables		$ 23,000	Accounts Payables		$ 147,938
Long Term Liabilities		$ - 0	Long Term Liabilities		$ - 0
Shareholders' Equity:			Shareholders' Equity:
Common Stock	$ 800,000		Common Stock	$ 800,000
Retained Earnings	$ 195,946		Retained Earnings	$ 1,094,925
Total Equity		$ 995,946	Total Equity		$ 1,894,925
Total Liabilities		$ 1,018,946	Total Liabilities		$ 2,042,863
		$ (0)			$ (0)