ABT, Inc. Balance Sheet December 31, 20x0 Assets Current assets: Cash Accounts receivable. Materials inventory.. Finished goods inventory. Total current assets... $ 120,000 300,000 50,000 55,000 $ 525,000 Property, plant, and equipment (PP&E): Land Buildings and equipment... Accumulated depreciation.. Total PP&E. $ 2,500,000 9,000,000 (4,500,000) 7,000,000 $7,525,000 Total assets.. Liabilities and Stockholders' Equity Current liabilities: Accounts payable... Stockholders' equity: Common stock, no par Retained earnings.. Total stockholders' equity Total liabilities and stockholders' equity $ 100,000 $ 600,000 6,825,000 7,425,000 $7,525,000
TBA, Inc., manufactures and sells concrete block for residential and commercial building. TBA expects to sell the following in 20x1:
| quarter 1 | 2 | 3 | 4 | |
| units | 2000000 | 6000000 | 6000000 | 2000000 |
| unit selling price | $0.70 | $0.70 | $0.80 | $0.80 |
TBA expects the following unit sales and desired ending inventory in 20x1:
| quarter | unit sales | ending inventory |
| 1 | 2000000 | 500000 |
| 2 | 6000000 | 500000 |
| 3 | 6000000 | 100000 |
| 4 | 2000000 | 1000000 |
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 hour; direct labor is paid $14 per direct labor hour.
Variable
TBA also provided the information that 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:
| salaries | 20000 |
| depreciation | 5000 |
| travel | 3000 |
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:
| salaries | 35000 |
| insurance | 4000 |
| depreciation | 12000 |
| travel | 2000 |
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 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 expense, and $12,000 for administrative expense. (Remember that depreciation is not a cash expense and must be deleted from total expenses before the
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
REQUIREMENTS (to be completed using Excel)
- Construct a sales budget for the coming year. Show total sales by quarter and in total for the year
- Construct a production budget for the coming year. Show total units produced by quarter and in total for the year.
- Construct a direct materials purchases budget for the raw materials for the coming year. Show total amounts by quarter and in total for the year.
- Construct a direct labor budget for the coming year. Show total amounts by quarter and in total for the year.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 4 images
