Can you help me answering numbers 10 to 13. I already have the answers from 1 to 9 for reference. Thank you

12:31 TBA, Inc., manufactures and sells concrete block for residential and commercial building. TBA expects to sell the following in 20x1: Quarter 1 Quarter 2 Quarter 3 Quarter 4 Units 2.000.000 6.000.000 6,000,000 2.000.000 Unit selling price S0.70 S0.70 SO SO SOS TBA expects the following unit sales and desired ending inventory in 20x1: Quarter Unit Sales Ending Inventory 2.000.000 500.000 6.000.000 6.000.000 100.000 2,000,000 100,000 500.000 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 laboris 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 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 Depreciation Travel $20,000 5,000 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. advertisina expense 12:31 Salaries Depreciation Travel $20,000 5,000 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 Insurance Depreciation Travel $35,000 4,000 12.000 2,000 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 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. 12:31 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: $ 525,000 Balance Sheet December 31, 20x0 Assets Current assets $ 120.000 Accounts receivable 300,000 Materials inventory..... 50,000 Finished goods inventory.. 55,000 Total current assets.. Property, plant, and equipment (PPSE $ 2.500.000 Buildings and equipment... 9,000,000 Accumulated depreciation... 14,500,000 Total PP&E.. Total assets Liabilities and Stockholders' Equity Current liabilities: Accounts payable... Stockholders' equity Common stock, no par .. $ 600,000 Retained earnings 6,825.000 Total stockholders' equity..... Total liabilities and stockholders' equity 7.000.000 57 325,000 $ 100,000 7.425.000 $7.525.000 REQUIREMENTS (to be completed using Excel) 1. Construct a sales budget for the coming year. Show total sales by quarter and in total for the year 2. Construct a production budget for the coming year. Show total units produced by quarter and in total for the year. 3. 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. 4. Construct a direct labor budget for the coming year. Show total amounts by quarter and in total for the year. 5. 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 in total for the year 8. Construct 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 12:31 $ Stockholders' equity Common stock, no par Retained earnings... Total stockholders' equity... Total liabilities and stockholders' equity. 600.000 6, 125.000 7,425,000 REQUIREMENTS (to be completed using Excel) 1. Construct a sales budget for the coming year. Show total sales by quarter and in total for the year 2. Construct a production budget for the coming year. Show total units produced by quarter and in total for the year. 3. 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. 4. Construct a direct labor budget for the coming year. Show total amounts by quarter and in total for the year. 5. 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 in total for the year 8. Construct an administrative expense budget for the coming year. Show total amounts by quarter and in total for the year. 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 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 1. Sales Budget Annual 16,000,000 Sales in units Selling Price Sales Revenue First 2,000,000 $ 0.70 $ 1,400,000 Second ,000,000 $ 0.70 $ 4,200,000 Third 6,000,000 $ 0.80 $ 4,800,000 Fourth 2,000,000 $ 0.80 $ 1,600,000 $ 12,000,000 2. Production Budget Sales needs add: Desired Ending inventory Total Needs Less: Beginning Inventory Total Production First 2,000,000 500,000 2,500,000 -100,000 2,400,000 Second 6,000,000 500.000 6,500,000 -500,000 6,000,000 Third F 6,000,000 100.000 6 ,100,000 -500,000 5,600,000 ourth 2,000,000 100.000 2,100,000 -100,000 2,000,000 Annual 15,000,000 100,000 16,100,000 -100,000 16,000,000 3. Direct Material Purchase Budget Finished Goods Production DM per unit needed DM Needed for production add: Desired Ending inventory Total Needs Less: Beginning Inventory DM Purchase in units Purchase cost per unit Cost of DM Purchase First 2,400,000 26.00 62,400,000 800,000 63,200,000 -500,000 62,700,000 0.01 $ 627,000 Second Third Fourth Annual 6,000,000 5,600,000 2,000,000 16,000,000 26.00 26.00 26.00 26.00 156,000,000 145,600,000 $2,000,000 416,000,000 800,000 500,000 500,000 500,000 156,800,000 146,100,000 52,500,000 416,500,000 -800,000 -800,000 -500,000 - 500,000 156,000,000 145,300,000 52,000,000 416,000,000 $ 0.01 $ 0.01 $ 0.01 $ 0.01 $ 1,560,000 $ 1,453,000 $ 520,000 $ 4,160,000 4. Direct Labor Budget First Finished Goods Production Hours required per unit Total Hours needed Hourly Labor Rate Direct Labor Cost 2,400,000 0.015 36,000 14.00 504,000 Second Third Fourth Annual 6,000,000 5 ,600,000 2,000,000 16,000,000 0.015 0.015 0.015 0.015 90,000 84,000 30,000 240,000 $ 14.00 $ 14.00 $ 14.00 $ 14.00 $ 1,260,000 $ 1,176,000 $ 420,000 $ 3,360,000 $ $ 5. Overhead Budget First Budgeted Hours Variable overhead per hour Budgeted Variable Overhead Budgeted Fixed Overhead Total Overheads Less:Depreciation Cash Disbursment for Overheads $ $ $ $ $ $ 36,000 8.00 288,000 320,000 608,000 200,000 808,000 Second 90,000 $ 8.00 $ 720,000 $ 320,000 $ 1,040,000 $ 200,000 $ 1,240,000 Third Fourth 84,000 30,000 $ 8.00 $ 8.00 $ 672,000 $ 240,000 $ 320,000 $ 320,000 $ 992,000 $ 560,000 $ -200,000 $ 200,000 $ 1,192,000 $ 760,000 Annual 240,000 $ 8.00 $ 1,920,000 $ 1,280,000 $ 3,200,000 $ 800,000 $ 4,000,000 6 cost of goods sold budget goods produced raw material consumed raw material cost @0.01 per pound total labor hrs@0.015 labor cost @14 per hr total variable overhead @8 per hr fixed overhead cost of production COGS (opeing + production - closing) 2 2,400,000 6,000,000 5,600,000 2,000,000 62,400,000 156000000 145600000 624000 1560000 1456000 520000 36000 90000 88400 30,000 504000 1260000 1237600 420000 288000 720000 707200 240000 320,000 320,000 320,000 320,000 1,736,000 3,860,000 3,720,800 1,500,000 1429333.3 39,00,000 38,80,952.38 14,89,714.29 7 marketing expense budget variable commision (0.05) fixed total plus advertising total marketing expenses 100,000 38000 138,000 2 300,000 43000 343,000 300,000 38000 338,000 100,000 38000 138,000 administrative expenses budget fixed total 1 53000 2 53000 3 53000 4 53000 9 production budget opeining block closing block sales production.(sales + closing - opening) 2. 100,000 500,000 500,000 500,000 2,000,000 6,000,000 2,400,000 6,000,000 3 500,000 100,000 6,000,000 5,600,000 100,000 100,000 2,000,000 2,000,000