TBA, Inc., manufactures and sells concrete block for residential and commercial building. In preparation for a master budget for the 2023, management gathers the following information.

Quarterly sales for 2022 are shown in the table below. 

Quarter	Sales in Units	Selling Price Per Block
1	2,000,000	$.70
2	5,000,000	$.70
3	4,000,000	$.75
4	3,000,000	$.80

Management reports that 1,000,000 blocks and 5,000,000 pounds of raw materials are in inventory at the beginning of 2023.

To helps protect against lost sales caused by unfulfilled demands from customers or delays in shipments from suppliers, TBA decides that the number of units (blocks) in its desired ending finished goods inventory should equal to 50% of next quarter's budgeted sales. 

Each block requires 25 pounds of raw materials (a mixture of cement, sand, gravel, shale, pumice, and water). Each pound of raw materials costs $0.01. Starting in 2023, TBA's policy is to have ending materials equal to 50% of next quarter's materials needed.

Each block requires .02 direct labor hours; direct labor cost is $17 per hour. 

Variable overhead rate is $5 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's variable marketing expense is a $0.05 commission per unit (block) sold. Fixed marketing expenses for each quarter include the following:

• Salaries = $52,000
• Depreciation = $5,000
• Travel = $3,000

In addition, TBA plans to spend 1% of previous quarter's revenue on advertising & promotions. 

TBA has no variable general & administrative expenses. Fixed general & administrative expenses for each quarter include the following:

• Salaries = $55,000
• Insurance = $4,000
• Depreciation = $12,000
• Travel = $2,000

Income tax is 30% of income before taxes; estimated taxes are paid quarterly at the end of each quarter.

30% of the sales are cash sales. Of the sales on account, 80 percent are collected in the quarter of sale; the remaining 20 percent are collected in the next quarter. 

All materials are purchased on account; 70 percent of purchases are paid for in the quarter of purchase. The remaining 30 percent are paid in the next quarter. The material purchases in the fourth quarter of 2022 were $500,000.

TBA plans to purchase additional equipment for $125,000 in the first quarter. The acquisition will be financed with cash, supplementing it with the business line of credit if necessary.

TBA recently established a $500,000 business line of credit with the interest rate of 12% per year. TBA requires a $100,000 minimum cash balance at the end of each quarter. Money can be borrowed from the business line of credit if the cash balance falls below the minimum cash requirement. If the cash balance exceeds $100,000 at quarter-end, TBA uses the excess to repay the business line of credit. All borrowing takes place at the beginning of the quarter, and all repayment takes place at the end of the quarter. 

If the quarter-end preliminary cash balance still exceeds the minimum cash requirement after repaying the business line of credit, TBA's policy is to pay cash dividends equal to 50% of the excess amount. 

The cash balance reported on 12/31/2022 balance sheet was $120,000.

REQUIREMENTS

Prepare the following budgets using Excel

• Prepare the quarterly Sales Budget for the coming year (2023) considering sales predictions, market conditions, advertising plan, and business capacity assuming that TBA is currently utilizing only 80% of its capacity. Show total sales by quarter and in total for the year. (5 points)
• Prepare the Production budget for the coming year. Show total units produced by quarter and in total for the year. Your group can decide a different amount of desired ending finished goods inventory for each quarter to better protect against lost sales caused by unfulfilled demands from customers or delays in shipments from suppliers. (10 points)
• Prepare the Direct Materials Purchases budget for the raw materials for the coming year. Show total amounts by quarter and in total for the year. Your group can change the policy regarding the desired ending materials inventory if necessary. (10 points)
• Prepare the Direct Labor budget for the coming year. Show total amounts by quarter and in total for the year. (10 points)
• Prepare the Overhead budget for the coming year. Show total amounts by quarter and in total for the year. (10 points)
• Prepare Budgeted Product Cost and the Cost of Goods Sold budget for the coming year. (10 points)
• Prepare the Selling Expense budget for the coming year. Show total amounts by quarter and in total for the year. (5 points)
• Prepare the General & Administrative expense budget for the coming year. Show total amounts by quarter and in total for the year. (5 points)
• Prepare the Capital Expenditures budget for the coming year. (5 points)
• Prepare the cash receipts budget, cash payments budget, and the Cash budget for each quarter of the coming year. (15 points)
• Prepare the budgeted income statement for the coming year. (15 points)

TBA, Inc. Cash Budget For the Year Ended December 31, 20x1 Q1 Q2 Q3 Q4 Year Beginning Cash Balance Estimated Cash Receipts from: Cash Sales Received from Accounts Receivalbe 30% of prior quarter's credit sales 70% of current quarter's credit sales Total Cash Available Cash Payments: Payments for puruchases: 20% of prior quarter purchases 80% of current quarter purchases Direct Labor Overhead (excluding depreciation exp.) Marketing expense (exclude depreciation exp.) Administrative expense (exclude depreciation exp) Income Tax Capital investment (equipment) Total cash payments Minimum cash balance $ 700,000 $1,750,000 $1,500,000 $1,200,000 $ 5,150,000 $ - $210,000 $525,000 $450,000 $ 1,185,000 $ 3,605,000 $ 9,940,000 $ 490,000 $1,225,000 $1,050,000 $840,000 $1,190,000 $3,185,000 $3,075,000 $2,490,000 $ 178,000 $ 224,000 $ 174,000 $ 712,000 $ 896,000 $ 696,000 $ 496,000 $1,190,000 $1,530,000 $1,190,000 $850,000 $470,000 $570,000 $470,000 $370,000 $ 176,000 $ 319,000 $ 292,500 $ 237,000 $ 61,000 $ 61,000 $ 61,000 $ $ 125,000 69 69 $ $ 576,000 $ 2,800,000 $ 4,760,000 $ 1,880,000 $ 1,024,500 61,000 $ 244,000 $ $ 100,000 $ 100,000 $100,000 $ 100,000 Total cash needs Excess (Deficiency) Financing: Borrowings Repayments Interest Total financing Add: minimum cash balance Ending cash balance