Required: Using Excel, prepare the master budget. Begin with the Balance Sheet – 2013; include all operating budgets; include a cash budget; and end with the Balance Sheet – 2014. You must prepare your own budget template and answers.

profiledono.9
budgeting_project_workbook_1.xls
Home>Business & Finance homework help>Accounting homework help>Required: Using Excel, prepare the master budget. Begin with the Balance Sheet – 2013; include all operating budgets; include a cash budget; and end with the Balance Sheet – 2014. You must prepare your own budget template and answers.

InputData

Projected: Cash collections 2013
Sales (units) Sell price In quarter of sale 80%
Qtr 4 - 2013 2,600,000 $ 3.75 In quarter following sale 18%
Qtr 1 - 2014 1,500,000 $ 3.75 Uncollectible 2%
Qtr 2 - 2014 900,000 $ 3.75 100%
Qtr 3 - 2014 1,225,000 $ 3.75
Qtr 4 - 2014 1,950,000 $ 3.75 Cash collections 2014
Qtr 1 - 2015 1,800,000 $ 3.75 In quarter of sale 80%
Qtr 2 - 2015 950,000 $ 3.75 In quarter following sale 18%
Uncollectible 2%
Ending Finished Goods Inventory Policy: 100%
16% of following quarter's sales
Cash payments:
Per Unit Cost of Beginning Finished Goods Inventory: In quarter of purchase 75%
$ 1.30 In quarter following purchase 25%
100%
Direct Materials per Unit:
0.6 ounces Bank loan (begin of qtr):
1st qtr $ 100,000
Ending Direct Materials Inventory Policy: 2nd qtr 0
19% of following quarter's production needs 3rd qtr 0
4th qtr 0
Direct Material Cost per ounce-previous: $ 0.20 Interest rate (annual) 4%
Direct Material Cost per ounce-current: $ 0.30 Repayment:
1st qtr $ 25,000
Direct labor per Unit: 0.05 hours 2nd qtr $ 25,000
Direct Labor Rate per Hour: $ 13.50 3rd qtr $ 25,000
4th qtr $ 25,000
Plant additions:
1st qtr $ 30,000
2nd qtr 20,000
3rd qtr 20,000
4th qtr 25,000
Variable Mfg Overhead Costs: Variable SGA Costs:
Indirect materials 0.19 Sales commissions $ 0.80
Electricity 0.14 Freight-out 0.30
Predetermined var. mfg. overhead rate $ 0.33 Miscellaneous 0.20
Variable SGA expenses rate $ 1.30
Fixed Mfg Overhead Costs:
Production runs $ 62,000 Fixed SGA expenses:
Design costs 15,000 Licensing and fees $ 15,500
Supervisor salaries 145,000 Sales salaries 25,000
Maintenance and repairs 38,000 Advertising 7,500
Insurance and property taxes 25,000 Clerical wages 22,000
Depreciation 105,000 $ 70,000
Utilities 15,000
$ 405,000
&L&Z&F&R&A

2013-BS

Balance sheet 12/31/13:
Current assets:
Cash $ 359,000
Accounts receivable (net) 1,200,650 prev qtr sales units * sell price
Inventories: cash collection %
Raw materials $ 50,000
Finished goods 312,000 362,000
Supplies 60,000
Total current assets $ 1,981,650
Property, plant & equipment:
Land $ 3,500,000
Buildings & equipment 4,000,000
Less: accumulated depreciation (2,350,000)
Total PP&E 5,150,000
TOTAL ASSETS $ 7,131,650
Current liabilities:
Accounts payable $ 52,889 prev qtr dm purchases *
cash pmt % follow qtr
Long-term liabilities:
Note payable (non-interest bearing; due 12/31/17) 2,000,000
Total liabilities $ 2,052,889
Owner's equity 5,078,761
TOTAL LIABILITIES & EQUITY $ 7,131,650
$ - 0

Sales Budget

prepare sales budget

Production Budget

prepare production budget
creat new worksheets, and prepare all the budgets requested