10.38 Part B

profileNewMe2014
copy_of_problem_10.38_with_assumptions.xlsx
Home>Business & Finance homework help>Accounting homework help>10.38 Part B

Sheet1

10.38 Assumptions and Master Budget for Unique Sinks
INPUT SECTION--BUDGET ASSUMPTIONS Revenue Budget
Quarter Average Total Budgeted
First Second Third Fourth Sink Per Sink Market Units Price Budgeted
Revenue Assumptions: Quarter Houses House Sales Share Sold Per Unit Revenue
Forecasted housing starts (local area) 6,000 24,000 7,000 2,000 First 6,000 3.0 18,000 35% 6,300 $85 $ 535,500
Average number of sinks per house 3.0 2.9 3.0 3.1 Second 24,000 2.9 69,600 35% 24,360 $85 2,070,600
Market share 35% 35% 35% 35% Third 7,000 3.0 21,000 35% 7,350 $85 624,750
Average selling price per unit $85.00 $85.00 $85.00 $85.00 Fourth 2,000 3.1 6,200 35% 2,170 $85 184,450
$ 3,415,300
Production Cost Assumptions:
Variable Production Costs:
Direct material-Pounds per unit 40 40 40 40 Production Budget (units)
Direct material-Cost per pound $0.50 $0.50 $0.50 $0.50 Targeted Finished Less
Direct labor-Hours per unit 3 3 3 3 Ending Units Units Beginning Required
Direct labor-Cost per hour $12.00 $12.00 $12.00 $12.00 Quarter Inventory Sold Needed Inventory Production
Desired ending sink inventory as % of next quarter's sales 10% 10% 10% 10% First 2,436 6,300 8,736 (600) 8,136
Desired ending DM inventory as % of next quarter's DM requirement 10% 10% 10% 10% Second 735 24,360 25,095 (2,436) 22,659
Variable Production Costs Per Direct Labor Hour: Third 217 7,350 7,567 (735) 6,832
Indirect labor $0.3000 $0.3000 $0.3000 $0.3000 Fourth 630 2,170 2,800 (217) 2,583
Supplies $0.2667 $0.2667 $0.2667 $0.2667
Other $0.1000 $0.1000 $0.1000 $0.1000 Assuming first quarter sales next year are the same as first quarter this year.
Fixed Production Cost:
Depreciation $9,000 $9,000 $9,000 $9,000
Fixed overhead allocation base: Direct labor hours
Direct Material and Direct Labor Resource Requirements
Selling and Administration Cost Assumptions: Pounds Pounds Labor Labor
Variable Costs: Required of Material of Material Hours Hours
Commission as % of revenue 10% 10% 10% 10% Quarter Production Per Unit Used Per Unit Used
Bad debts as % of revenue 2% 2% 2% 2% First 8,136 40 325,440 3 24,408
Fixed Costs: Second 22,659 40 906,360 3 67,977
Salaries $40,000 $40,000 $40,000 $40,000 Third 6,832 40 273,280 3 20,496
Depreciation $2,400 $2,400 $2,400 $2,400 Fourth 2,583 40 103,320 3 7,749
Other $8,300 $7,400 $9,200 $7,300 Total 1,608,400 120,630
Cash Flow Assumptions:
Revenue Collections: Direct Materials Purchases Budget (Pounds)
During quarter sold 66% 66% 66% 66% Desired Total Direct Less
During next quarter 32% 32% 32% 32% Ending Production Materials Beginning Required
Direct Material Payments: Quarter Inventory Requirements Required Inventory Purchases
During quarter purchased 66.6667% 66.6667% 66.6667% 66.6667% First 90,636 325,440 416,076 (20,000) 396,076
During next quarter 33.3333% 33.3333% 33.3333% 33.3333% Second 27,328 906,360 933,688 (90,636) 843,052
Current Year Income Tax--Estimated Payments $0 $80,000 $40,000 $40,000 Third 10,332 273,280 283,612 (27,328) 256,284
All other costs paid as incurred Fourth 32,544 103,320 135,864 (10,332) 125,532
Dividends Declared & Paid $0 $0 $0 $50,000
Short-Term Financing Assumptions: Direct Materials and Direct Labor Budget
Minimum Cash Balance $30,000 $30,000 $30,000 $300,000 Direct Cost Total Labor Cost Total Total Direct
Annual Interest Rate for Borrowing 8% 8% 8% 8% Material Per Material Hours Per Labor Material and
Assume no interest earned on cash balances Quarter Purchases Pound Cost Used Hour Cost Labor Costs
First 396,076 $0.50 $ 198,038 24,408 $12.00 $ 292,896 $ 490,934
Income Statement Assumption: Second 843,052 $0.50 421,526 67,977 $12.00 815,724 1,237,250
Income Tax Rate 30% 30% 30% 30% Third 256,284 $0.50 128,142 20,496 $12.00 245,952 374,094
Fourth 125,532 $0.50 62,766 7,749 $12.00 92,988 155,754
Prior Year Balances Carried Over to Current Year: Total 1,620,944 $ 810,472 120,630 $ 1,447,560 $ 2,258,032
Assets:
Cash $ 45,820
Raw materials inventory (cost) $ 10,000 20,000 Pounds Manufacturing Overhead Budget
Finished goods inventory (Cost) $ 30,250 600 Sinks Quarter
Accounts receivable $ 118,000 First Second Third Fourth Total
Allowance for bad debts $ (7,378) Variable Overhead Costs
Land, building and equipment $ 912,000 Indirect labor $ 7,322 $ 20,393 $ 6,149 $ 2,325 $ 36,189
Accumulated depreciation $ (114,000) Supplies 6,510 18,129 5,466 2,067 32,172
Other 2,441 6,798 2,050 775 12,063
Liabilities and Equity: Total variable costs 16,273 45,320 13,665 5,166 80,424
Accounts payable (purchases) $ 72,370 Fixed Overhead Costs
Income taxes payable $ 7,000 Depreciation 9,000 9,000 9,000 9,000 36,000
Credit Line Loan Payable $ - Total fixed costs 9,000 9,000 9,000 9,000 36,000
Common stock $ 750,000 Total manufacturing overhead $ 25,273 $ 54,320 $ 22,665 $ 14,166 $ 116,424
Retained Earnings $ 165,322
Total Fixed Overhead Costs $ 36,000
Total Labor Hours Used in Production 120,630
Beginning Balance Sheet Math Check: Fixed Manufacturing Overhead Allocation Rate Per Direct Labor Hour $ 0.2984
Total Assets $ 994,692
Total Liab & Equity $ 994,692
Total Production Cost Per Unit
Quarter
First Second Third Fourth
Direct material $ 20.0000 $ 20.0000 $ 20.0000 $ 20.0000
Direct labor 36.0000 36.0000 36.0000 36.0000
Variable overhead 2.0001 2.0001 2.0001 2.0001
Total Variable Cost Per Unit 58.0001 58.0001 58.0001 58.0001
Fixed overhead 0.8953 0.8953 0.8953 0.8953
Total Cost Per Unit $ 58.8954 $ 58.8954 $ 58.8954 $ 58.8954
Budgeted Statement of Cost of Goods Manufactured and Sold
Quarter
First Second Third Fourth Total
Beginning direct materials $ 10,000 $ 45,318 $ 13,664 $ 5,166 $ 10,000
Plus purchases 198,038 421,526 128,142 62,766 810,472
Less ending direct materials (45,318) (13,664) (5,166) (16,272) (16,272)
Cost of direct materials used 162,720 453,180 136,640 51,660 804,200
Cost of direct labor used 292,896 815,724 245,952 92,988 1,447,560
Allocated manufacturing overhead 23,557 65,607 19,781 7,479 116,424
Cost of goods manufactured 479,173 1,334,511 402,373 152,127 2,368,184
Beginning finished goods 30,250 143,469 43,288 12,780 30,250
Goods available for sale 509,423 1,477,980 445,661 164,907 2,398,434
Less ending finished goods (143,469) (43,288) (12,780) (37,104) (37,104)
Cost of goods sold $ 365,954 $ 1,434,692 $ 432,881 $ 127,803 $ 2,361,330
Selling and Administration Budget
Quarter
First Second Third Fourth Total
Variable costs
Commissions $ 53,550 $ 207,060 $ 62,475 $ 18,445 $ 341,530
Bad debts 10,710 41,412 12,495 3,689 68,306
Total variable costs 64,260 248,472 74,970 22,134 409,836
Fixed costs
Salaries 40,000 40,000 40,000 40,000 160,000
Depreciation 2,400 2,400 2,400 2,400 9,600
Other 8,300 7,400 9,200 7,300 32,200
Total fixed costs 50,700 49,800 51,600 49,700 201,800
Total Selling and Administration Costs $ 114,960 $ 298,272 $ 126,570 $ 71,834 $ 611,636
Cash Receipts and Disbursements Budget
Quarter
First Second Third Fourth Total
Receipts
From current quarter's sales $ 353,430 $ 1,366,596 $ 412,335 $ 121,737 $ 2,254,098
From prior quarter (net of bad debts) 110,622 171,360 662,592 199,920 1,144,494
Total receipts 464,052 1,537,956 1,074,927 321,657 3,398,592
Disbursements
For current quarter's purchases 132,025 281,017 85,428 41,844 540,315
For prior quarter's purchases 72,370 66,013 140,509 42,714 321,605
Direct labor costs 292,896 815,724 245,952 92,988 1,447,560
Indirect labor 7,322 20,393 6,149 2,325 36,189
Supplies 6,510 18,129 5,466 2,067 32,172
Other production costs 2,441 6,798 2,050 775 12,063
Salaries 40,000 40,000 40,000 40,000 160,000
Commissions 53,550 207,060 62,475 18,445 341,530
Other selling & administration costs 8,300 7,400 9,200 7,300 32,200
Income taxes 7,000 80,000 40,000 40,000 167,000
Dividends 0 0 0 50,000 50,000
Total Disbursements 622,414 1,542,534 637,228 338,457 3,140,634
Excess Receipts (Disbursements) $ (158,362) $ (4,578) $ 437,699 $ (16,800) $ 257,958
Short-Term Financing Budget
Quarter
First Second Third Fourth Total
Beginning cash balance $ 45,820 $ 30,000 $ 30,000 $ 314,728 $ 45,820
Excess receipts (disbursements) (158,362) (4,578) 437,699 (16,800) 257,958
Line of credit:
Borrowings 142,542 7,429 0 2,072 152,044
Repayments 0 0 (149,971) 0 (149,971)
Interest on borrowing 0 (2,851) (2,999) 0 (5,850)
Ending Cash Balance $ 30,000 $ 30,000 $ 314,728 $ 300,000 $ 300,000
Beginning Line of Credit Balance $0 $142,542 $149,971 $0 $0
Ending Line of Credit Balance $142,542 $149,971 $0 $2,072 $2,072
Budgeted Statement of Income and Retained Earnings
Quarter
First Second Third Fourth Total
Sales $ 535,500 $ 2,070,600 $ 624,750 $ 184,450 $ 3,415,300
Cost of Goods Sold 365,954 1,434,692 432,881 127,803 2,361,330
Gross Margin 169,546 635,908 191,869 56,647 1,053,970
Selling and Administration 114,960 298,272 126,570 71,834 611,636
Operating income 54,586 337,636 65,299 (15,187) 442,334
Interest expense 0 2,851 2,999 0 5,850
Pretax income 54,586 334,785 62,299 (15,187) 436,484
Income taxes 16,376 100,436 18,690 (4,556) 130,945
Net Income $ 38,210 $ 234,350 $ 43,610 $ (10,631) 305,539
Beginning Retained Earnings 165,322
Less Dividends (50,000)
Ending Retained Earnings $ 420,861
Budgeted Balance Sheet
Beginning of Year End of Year
Assets
Cash $ 45,820 $ 300,000
Raw materials inventory 10,000 16,272
Finished goods inventory 30,250 37,104
Accounts receivable, net 110,622 59,024
Total Current Assets 196,692 412,400
Land, building and equipment $ 912,000 $ 912,000
Accumulated depreciation (114,000) 798,000 (159,600) 752,400
Total Assets $ 994,692 $ 1,164,800
Liabilities and Equity
Accounts payable (purchases) $ 72,370 $ 20,922
Income taxes payable 7,000 (29,055)
Common stock 750,000 750,000
Retained Earnings 165,322 420,861
Total Liabilities and Equity $ 994,692 $ 1,162,728

Sheet2

Sheet3