Since each question correlates to the other question please

Since each question correlates to the other question please supply all ten questions in a spreadsheet style format :

Brad\'s Baskets, a manufacturing business that sells baskets, wants a master budget prepared for the first three months of this year (January, February and March).

The managers of the different departments have provided the following information: The Sales Manager has projected the following sales:

January           5,000 units

February         4,000 units

March              6,000 units

April                5,000 units

May                11,250 units

Projected selling price is $35.00/unit

*Your Production Manager gave the following information:

Ending Inventory is to be 20% of next month’s production needs

April’s Projected Sales 5,000 units

December 20X5 Ending Inventory was 1,000 units and December unit cost was $23.50.

*The Manufacturing Manager has estimated the following:

Each unit will require 4 grams of material

Material in Ending Inventory is 20% of next month’s needs

December’s Ending Material Inventory was 4,800 g

Projected cost of material: $2.50/gram

*The Personnel Manager has estimated that Direct Labor will be projected at:

0.75 hours of Direct Labor per unit

Direct Labor Cost: $8.50/hour

*The Facilities Manager has estimated that the Manufacturing Overhead will be projected at:

Variable Overhead Rate to be $8 per Direct Labor hours

Fixed Overhead Rate to be $3,000 per month

*The Accounting Department Manager has provided the following information:

Selling and Administrative Expenses are projected to be a monthly cost of:

Salaries               $6,000

Rent                     $1,500

Advertising          $1,100

Telephone              $300

Other                       $500

*Cash Receivable:

December’s Sales were $150,000

80% of sales is collected in the month in which they were made

20% of sales collected in the following month in which they were made

Bad Debts is negligible

*Accounts Payable:

80% of Payables is paid for in the current month

20% of Payables is paid for in the following month

December’s purchases were $50,000

*Federal Income Tax is estimated at 22% average.

*Brad\'s Baskets

has a $20,000 cash balance for the beginning of January

pays Dividends of $8,000 to be paid in March

pays projected Federal Income tax in March

depreciation on the building is $150 per month

does not carry any WIP inventory

uses FIFO inventory costing

*From the beginning Balance Sheet:

Land = $150,000

Building = $45,000

Depreciation (Building) = $11,250

Retained Earnings = $58,780

Capital Stock = $200,470

For the Master Budget, you are expected to prepare the following:

1. Sales budget plus schedule of accounts receivable collections

2. Production budget

3. Direct materials budget and schedule of cash payments for purchases

4. Direct labor budget

5. Manufacturing overhead budget

6. Cost of Goods Sold Budget (When you prepare the cost of goods sold budget, you must calculate a unit cost for each month. You must also calculate cost of goods manufactured.)

7. Selling & Administrative Expenses Budget

8. Budgeted income statements

9. Cash budget

10. Budgeted balance sheet for each month plus a beginning balance sheet

Note: there is no Work in Process inventory but you must calculate direct materials used.

Check your work figures: These are the values of some of the totals you should have to verify correct calculations

Total March sales, $210,000

Total February cash collections, $147,000 Total February units to produce, 4,400

Total March direct materials purchase, $58,900

Total February cash disbursements for raw materials, $46,400 Total January direct labor, $30,600

Total March overhead, $37,800

Total January selling & admin, $9,400 Total February cost of goods sold, $92,182 Total March cost per unit, $22.89

Total March cost of goods manufactured, $132,775 Total January 1 Assets, $269,250

Ending cash, March 31, $120,209 Net income, February, $29,849 Total Assets, March, $380,901

Solution

1 Sales budget Jan Feb March Quarter Units 5000 4000 6000 15000 Per unit price 35 35 35 35 Total sales 175000 140000 210000 525000 Budgeted Cash receipt Jan Feb March Quarter Accounts receivable 30000 30000 Collection for July 140000 35000 175000 Collection for August 112000 28000 140000 Collection for September 168000 168000 Total collection 170000 147000 196000 513000 2 Purchase budget Jan Feb March Quarter April Sales 5000 4000 6000 15000 5000 Add Ending inventory 20%of next month 800 1200 1000 1000 2250 Total needs 5800 5200 7000 16000 7250 Less Beginning inventory 1000 800 1200 1000 1000 Required production in Dollars 4800 4400 5800 15000 6250 3 Direct material purchase budget Jan Feb March Quarter April Production budget units 4800 4400 5800 15000 6250 Per unit of material required 4 4 4 4 4 Material needed for production 19200 17600 23200 60000 25000 Add ending material inventory 20% 3520 4640 5000 13160 Total material requirements 22720 22240 28200 73160 Less beginning material inventory 4800 3520 4640 12960 Material to be purchased 17920 18720 23560 60200 Material price per unit 2.50 2.50 2.50 2.50 Total cost of direct material purchases 44800 46800 58900 150500 4 direct labor budget Jan Feb March Quarter Production budget units 4800 4400 5800 15000 Per unit direct hours required 0.75 0.75 0.75 0.75 Total labor hours needed 3600 3300 4350 11250 Labor price per hour 8.5 8.5 8.5 8.5 Labor dollars 30600 28050 36975 95625