TBA, Inc., manufactures and sells concrete blocks for residential and commercial buildings. TBA expects to sell the following in 20x1:

TBA expects the following unit sales and desired to end inventory in 20x1:

Inventory on both January 1, 20x1, and January 1, 20x2, is expected to be 100,000 blocks.
Each block requires 26 pounds of raw materials (a mixture of cement, sand, gravel, shale, pumice, and water). TBA's raw materials inventory policy is to have 5 million pounds in ending inventory for the third and fourth quarters and 8 million pounds in ending inventory for the first and second quarters. Thus, desired direct materials inventory on both January 1, 20x1, and January 1, 20x2, is 5,000,000 pounds of materials. Each pound of raw materials costs $0.01.
Each block requires 0.015 direct labor hours; direct labor is paid $14 per direct labor hour.
Variable overhead is $8 per direct labor hour. Fixed overhead is budgeted at $320,000 per quarter ($100,000 for supervision, $200,000 for depreciation, and $20,000 for rent).
TBA also provided the information that the beginning finished goods inventory is $55,000, and the ending finished goods inventory budget for ABT for the year $67,000.
TBA's only variable marketing expense is a $0.05 commission per unit (block) sold. Fixed marketing expenses for each quarter include the following:

Advertising expense is $10,000 in Quarters 1, 3, and 4. However, at the beginning of the summer building season, TBA increases advertising; in Quarter 2, advertising expense is $15,000.
TBA has no variable administrative expense. Fixed administrative expenses for each quarter include the following:

Income taxes are paid at the rate of 30 percent of operating income.
Of the sales on account, 70 percent are collected in the quarter of sale; the remaining 30 percent are collected in the quarter following the sale. Total sales for the fourth quarter of 20x0 totaled $2,000,000.
All materials are purchased on the account; 80 percent of purchases are paid for in the quarter of purchase. The remaining 20 percent are paid in the following quarter. The purchases for the fourth quarter of 20x0 were $500,000.
TBA requires a $100,000 minimum cash balance for the end of each quarter. On December 31, 20x0, the cash balance was $120,000.
Money can be borrowed and repaid in multiples of $100,000. Interest is 12 percent per year. Interest payments are made only for the amount of the principal being repaid. All borrowing takes place at the beginning of a quarter, and all repayment takes place at the end of a quarter.
Budgeted depreciation is $200,000 per quarter for overhead, $5,000 for marketing expenses, and $12,000 for administrative expenses. (Remember that depreciation is not a cash expense and must be deleted from total expenses before the cash budget is prepared.)
The capital budget for 20x1 revealed plans to purchase additional equipment for $600,000 in the first quarter. The acquisition will be financed with operating cash, supplementing it with short-term loans as necessary.
Corporate income taxes of $20,700 will be paid at the end of the fourth quarter.
The balance sheet for the beginning of the year is given:

REQUIREMENTS (to be completed using Excel)

9 Construct a budgeted income statement for the coming year.
10. Construct a cash receipts budget for each quarter of the coming year.
11. Construct a cash payments budget for each quarter of the coming year.
12. Prepare a cash budget for each quarter of the coming year.
13. Prepare the Budgeted Balance Sheet for the coming year

 

Current assets: Cash Accounts receivable... Materials inventory... Finished goods inventory. Total current assets... Property, plant, and equipment (PP&E): Land. ****** Buildings and equipment.... Accumulated depreciation...... Total PP&E.. Total assets........ Current liabilities: ABT, Inc. Balance Sheet December 31, 20x0 Accounts payable............ Stockholders equity: Assets Liabilities and Stockholders Equity Common stock, no par. Retained earnings .......... Total stockholders equity. Total liabilities and stockholders equity. $ 120,000 300,000 ************** 50,000 55,000 $ 2,500,000 9,000,000 (4,500,000) $ 600,000 6,825,000 $ 525,000 7,000,000 $7,525,000 $ 100,000 7,425,000 $7,525,000 A Quarter Q1 Q2 Q3 Q4 Year Total B C TBA Inc., Sales Budget For the Year Ended Decemeber 31, 20x1 Unit Sales Volume 2,000,000 6,000,000 6,000,000 2,000,000 16,000,000 Unit Selling Price Totals D $0.70 $1,400,000.00 $0.70 $4,200,000.00 $0.80 $4,800,000.00 $0.80 $1,600,000.00 $3.00 $12,000,000.00 Expected Units to be sold Desired Ending Inventory Total Units Available Estimated Beginning Inventory Total Units to be Produced Q1 TBA, Inc. Production Budget For the Year Ended 20x1 Q2 Q3 Year Total 2,000,000.00 6,000,000.00 6,000,000.00 500,000.00 500,000.00 2,000,000.00 16,000,000.00 100,000.00 1,200,000.00 2,100,000.00 17,200,000.00 100,000.00 2,500,000.00 6,500,000.00 6,100,000.00 (100,000.00) (500,000.00) (100,000.00) (1,200,000.00) 2,400,000.00 6,000,000,00 5,600,000.00 2,000,000.00 16,000,000.00 (500,000.00) Q4 Lbs required for production (Blocks): Raw Materials required (Note A): Desired Inventory, End of Quarter Total Lbs available Est. Inventory, Beg. of Quarter Total Lbs to be purchased Unit Price/Lb Total DM to be purchased Note A Q1 Q1: Q2: Q3: Q4: TBA, Inc. Direct Materials Purchases Budget For the Year Ended 20x1 Q2 $0.01 $0.01 $ 699,000.00 $ 1,635,000.00 2,400,000.00 X 26 Lb s= 6,000,000.00 X 26 Lb s= 5,600,000.00 X 26 Lb s= 2,000,000.00 X 26 Lb s = Direct Materials: Q3 62,400,000.00 156,000,000.00 145,600,000.00 52,000,000.00 416,000,000.00 8,000,000.00 8,000,000.00 5,000,000.00 5,000,000.00 26,000,000.00 70,400,000.00 164,000,000.00 150,600,000.00 57,000,000.00 442,000,000.00 (500,000.00) (500,000.00) (100,000.00) (100,000.00) (1,200,000.00) 69,900,000.00 163,500,000.00 150,500,000.00 56,900,000.00 440,800,000.00 0.01 $0.01 $0.01 S $1,505,000.00 $ 569,000.00 $4,408,000.00 62,400,000 156,000,000 145,600,000 52,000,000 Q4 Total: Hours required for production: Concrete Block (Note A): Hourly Rate: Total Direct Labor Cost: Note A Q1: Q2: Q3: Q4: Q1 $ TBA, Inc. Direct Labor Cost Budget For the Year Ended 20x1 Q2 Q3 36,000.00 14.00 $ $504,000.00 2,400,000.00 X 0.015 dlh= 6,000,000.00 X 0.015 dih= 5,600,000.00 X 0.015 dih= 2,000,000.00 X 0.015 dih= 90,000.00 14.00 $ $1,260,000.00 36,000.00 90,000.00 84,000.00 30,000.00 84,000.00 Q4 Total 30,000.00 240,000 14.00 S 14.0 14.00 $ $1,176,000.00 $ 420,000.00 $3,360,000.C Variable OH: DL FO Fixed OH: Rent Total Fixed OH Total OH Q1 Supervision $ Depreciation $ $ Note A: Q1: Q2: Q3: Q4: $ $ $ DLH X S S $ Q2 288,000 $ 100,000 $ 200,000 $ 20,000 $ 320,000 $ 608,000 $ TBA, Inc. Factory Overhead Budget For the Year Ended 20x1 Q3 36,000 $ 90,000 $ 84,000 $ 36,000 $ 720,000 $ 100,000 $ 200,000 $ 20,000 $ 320,000 $ 1,040,000 $ VDL OH Rate DL FO 8 $ 8 $ 8 $ 8 $ Q4 672,000 $ 100,000 $ 200,000 $ 20,000 $ 320,000 $ 992,000 $ 288,000 720,000 672,000 288,000 Year Total 288,000 $ 1,968,000 100,000 $ 200,000 $ 20,000 $ 320,000 $ 1,280,000 608,000 $ 3,248,000 400,000 800,000 80,000 TBA, Inc. Cost of Goods Sold Budget For the Year Ending December 31, 20x1 Finished Goods Inventory, January 20x1 Work in Process Inventory, January 20x1 Direct Materials: Direct Materials Inventory January 1, 20x1 Direct Material Purchases Cost of direct Materials available for use $ 260,000.00 $4,408,000.00 $ 4,668,000.00 Direct Materials Inventory December 31, 20x1 $ (260,000.00) Cost of Direct Materials Placed in production $4,408,000.00 Direct Labor $ 3,360,000.00 Factor Overhead $3,248,000,00 Total Manufacturing Costs Total Work in Process during period Work in Process inventory, December 31, 20x1 Cost of Goods Manufactured Cost of finished Goods available for sale Finished Goods Inventory, December 31, 20x1 Cost of Goods Sold $ $ 11,016,000.00 $ 55,000.00 2 60 S 67,000.00 G