TBA, lnc.. manufactures and sells concrete block for residential and commercial building. TBA expects the following unit sales. unit selling price and desired ending inventory in 2020: Quarter Sales Selling Price Ending Inventory (Units) {per unit} {units} 1 2.000.000 $.70 500.000 2 6.000.000 $.70 500.000 3 6.000.000 $.80 100 .000 4 2.000.000 $.80 100.000 Inventory on both January 1, 2020. and December 31, 2020. is expected to be 500.000 blocks. Each block requires 26 pounds of raw materials (a mixture of cement. sand. gravel, shale, pumice, and water). TBA's raw materiais inventory policy is to have 5 mitiion pounds in ending inventory for the third and fourth quarters and 8 million pounds in ending inventory for the rst and second quarters. Thus. desired direct materiats inventory on both January 1, 2020. and December 31, 2020. 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 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 beginning nished goods inventory is $55,000: and the ending nished goods inventory budget for TBA for the year $6?.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 = $20,000 I Depreciation = $5.000 I Travel = $3.000 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 = 5535.000 Insurance = $4.000 Depreciation = $12.000 Travel = $2.000 Income tax rate Is 30% 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 sates for the fourth quarter of 2019 totaied $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 purchasep for the fourth quarter of 2019 were $500,000. TBA requires a $100,000 minimum cash baianoe for the end of each quarter. On December 31. 2019, the cash balance was $120,000. Money can be bonowed and repaid in multiples of $100,000. Interest is 12 percent per year. interest payments are made onlyr 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 cash budget is prepared.) The capital budget for 2020 revealed plans to purchase additional equipment for $600,000 in the rst quarter. The acquisition will be nanced with operating cash, supplementing it with short-tent: 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] 1. Construct a sales budget for the coming year. Show total sales by quarter and in totat 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 totat amounts by quarter and in total for the year. Constmct a direct labor budget for the coming year. Show total amounts by quarter and in total for the year. Construct an overhead budget for the coming year. Show total amounts by quarter and in total for the year. 6. Prepare a cost of goods sold budget for the coming year. 7. Construct a marketing expense budget for the coming year. Show total amounts by quarter and 9. in total for the year Constmct an administrative expense budget for the coming year. Show total amounts by quarter and in total for the year. Construct a budgeted income statement for the coming year. 10. Construct a cash receipts budget for each quarter of the coming year. 11.Constn.tct a cash payments budget 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