Johnson Company cash budget and budgeted balance sheet.

Johnson Company is a manufacturing company. Its balance sheet for the year ending December 31, 2010, is presented below.

Johnson Company
Balance Sheet
Year Ending December 31st, 2010

Cash $20,000 Accounts Payable $ 6,800
Short-term Investments 4,000 Long-term Debt 80,000
Accounts Receivable 50,000 Total Liabilities $86,800
Raw Materials Inventory 7,200 Common Stock - $10 par $75,000
*Finished Goods Inven. 20,600 Additional Paid-in Capital 15,000
Equipment 120,000 Retained Earnings 33,000
Accumulated Depreciation (12,000) Total Shareholders? Equity 123,000
Total Assets $209,800
Total Liabilities & SE $209,800

*Note: Finished Goods Inventory consists of 800 bowling balls at an average cost of $25.75 per unit.

The master budget needs to include the following components, and should be presented in a professional format using either an Excel spreadsheet or a Word document:

1. Sales budget
2. Production budget
3. Direct materials purchase budget
4. Sales budget
5. Manufacturing overhead budget
6. Selling and administrative expenses budget
7. Ending finished goods inventory/Cost of goods sold
8. Cash budget
9. Pro forma income statement for the year ending December 31, 2011
10. Pro forma balance sheet as of December 31, 2011

Sales Volume: sales are anticipated as follows.

2011:
Quarter 1: 3,600 units
Quarter 2: 3,800 units
Quarter 3: 4,400 units
Quarter 4: 4,000 units

2012:
Quarter 1: 4,200 units
Quarter 2: 3,800 units

The average selling price is $40 per unit. Cash collections are as follows: 70% is collected in period sold, and the remaining 30% is collected in the following quarter. The December 2010 accounts receivable is expected to be collected in full the first quarter of 2011.

Production: The company has adopted the policy that the desired finished goods ending inventory for any quarter should be equal to 40% of the next quarter?s budgeted sales volume.

Raw Materials: The company has adopted the policy that the desired ending inventory for raw materials should be equal to 25% of the next quarter?s production needs. It takes 4-lbs. of the raw material to make one completed bowling pin. The expected cost per pound for raw materials is $2.50. The company?s disbursement policy for payment of raw materials: 80% is paid for in the quarter purchased, and the remaining 20% is paid in the following quarter. The December 2010 accounts payable is expected to be paid in full the first quarter of 2011.

Direct Labor: It takes one-quarter hour of direct labor (15-minutes) to complete one unit. The average hourly wage is $15. The company pays all direct labor costs in the quarter incurred.

Manufacturing Overhead: The following is a breakdown of the variable and fixed overhead expected costs. The company pays for all manufacturing overhead in the quarter incurred. Ten Pin uses a predetermined overhead rate based on the number of direct labor hours expected to be incurred.

Variable (per DLH):
Indirect Materials: $2.50
Indirect Labor: $2.30
Utilities: $1.90
Maintenance: $1.30

Fixed (annual basis):
Supervisor salary: $62,000
Depreciation: $72,000
Property taxes and insurance: $19,800
Maintenance: $13,000

Total Unit Cost:
Raw materials ($2.5 x 4) $ 10.00
Direct Labor ($15 x .25) 3.75
Variable OH ($8 x .25 DLH) 2.00
Fixed OH 10.00
Total Unit Cost $25.75

Selling and Administrative expenses: the following is a breakdown of the variable and fixed selling and administrative expected costs. The company pays all selling and administrative costs in the quarter incurred.

Variable (per unit basis):
Commission: $1.00
Freight-out: $.50

Fixed (annual basis):
Advertising: $4,000
Sales Salaries: $25,000
Office Salaries: $14,000
Depreciation: $10,000
Insurance: $3,000

Other additional information:

- The company expects to purchase new equipment costing $100,000 cash in the second quarter of 2011 (depreciation has been accounted in the above facts).

- Short-term investments are expected to be sold for $10,000 cash in the first quarter. There will be a gain on the sale (selling price less cost basis).

- The company makes equal quarterly payments ($10,000/quarter) for its estimated annual income taxes which are expected to total $40,000 for 2011.

- The company has adopted the policy of a $20,000 minimum cash balance. If it needs to borrow, a revolving line of credit is available up to $100,000. The company can borrow and repay in increments of $1,000 only. The interest is due in the following quarter on any borrowings outstanding at the rate of 6% per quarter.

- The company repaid $20,000 worth of its long-term debt in the first quarter. For simplicity assume there is a quarterly interest expense of $2,000 on this debt ($8,000 for the year).

- The company paid dividends to shareholders totaling $30,000 in the fourth quarter.

Please use Excel in the response.