Please help with filling out the schedule 9-cash budget. PLEASE SHOW WORK OR SHOW CELL REFERENCES. Thank you!
Beginning Balance Sheet: Table Company Budgeted Balance Sheet 31-Dec-21 6,000 151,848 111,860 Current assets: Cash Accounts receivable (net of bad debts): Q3 amount to collect Q1 24,624 Q4 amount to collect Q1 102,600 Q4 amount to collect Q2 24,624 Inventory*: Finished goods inventory Standard table Deluxe table 0 Raw materials inventory Standard table: Wood 25,000 Metal 14,000 Deluxe table Total current assets * note: there is no WIP inventory Property, plant, and equipment: 39,000 0 308,708 625,000 (40,000) Fixed Assets Less Accumulated depreciation Total property, plant, and equipment Total assets 585,000 $ 893,708 Current Liabilities: Interest payable on line of credit Line of credit Accounts payable Dividends payable (QTR 1) Income tax payable Total liabilities 1,000 50,000 60,000 12,000 16,000 139,000 Shareholders' equity: Common shares Retained earnings Total shareholders' equity Total liabilities and shareholders' equity 700,000 54,708 754,708 $ 893,708 Direct Material, Direct Labour, & MOH Payment Information: Percentage of direct materials purchases paid in: Current month Following month 80% 20% All direct labour and MOH are paid in the month occurred. Desired Finished Goods Ending Inventory Information: The company desires to have on hand at the end of each month: 30% Finished goods inventory: equal to the % of the following month's sales 20% Direct materials inventory: equal to the % of the following month's production needs. Dec 31, 2021 ending inventories: standard tables (finished goods) deluxe tables (finished goods) Units 650 Dollar Value $ 111,860 0 0 Regular Wood (square feet) Regular Metal components 5,000 2,000 25,000 14,000 Premium Wood Premium metal components 0 0 Selling & Administration Expenses: Variable Costs: Sales commissions (% of sales) Shipping and handling (per unit) 300% 5.00 Fixed Costs: Advertising Office salaries Property taxes and insurance Other expenses What-if 5,000 25,000 10,000 4,000 Investment and other Information: Line of Credit Information Maximum line of credit Annual Interest Rate on LOC Quarterly Interest Rate 2,000 8% 2% Purchase of Machinery Quarter 1 30,000 Annual Dividend 12,000 Declared QTR 4 Paid in QTR 1 Minimum cash balance 6,000 Tax Information: Income Tax Rate (marginal) 18% Prior year taxes payable Paid in QTR 2 16,000 Schedule 1 - Sales Budget Budgeted Year Q3 Following Year Q1 Q1 Q2 Q4 Year Standard Table Budgeted Unit Sales Selling Price Per Unit Standard table Sales Revenue $ $ 1800 199.50 $ 359,100.00 $ 2016 199.50 $ 402,192 $ 2,258 199.50 $ 450,471 $ 2,529 199.50 $ 504,536 $ 8,603 199.50 1,716,299 2,832 2,974 $ 199.50 $ 199.50 $ 565,080 $ 593,334 Deluxe Table Budgeted Unit Sales Selling Price Per Unit Product 2 Sales Revenue $ $ 340 310.00 $ 105,400 $ 408 310.00 $ 126,480 $ 490 310.00 $ 151,900 $ 588 310.00 $ 182,280 $ 1826 310.00 566,060 617 648 $ 310.00 $ 310.00 $ 191,394 $ 200,964 Total Sales Revenue $ 464,500 $ 528,672 $ 602,371 $ 686,816 $ 2,282,359 $ 756,474 $ 794,297 Sales Mix % Standard table Deluxe table 75% 25% Schedule 2 - Cash Collections Q2 Q3 Q4 Total Q1 Q2 $ Sales Cash sales Credit sales Q1 (a) 464,500 $ 185800 278,700 528,672 $ 211468.8 317,203 602,371 $ 240,948 361,423 686,816 $ 274,726 412,089 2,282,359 912,943 1,369,415 $ 756,474 $ 794,297 302,590 317,719 Credit sales collected in current quarter Credit sales from prior quarter Credit sales from second prior quarter Total Cash Collections 97,545 24,624 102,600 410,569 111,021 139,350 24,624 486,464 126,498 158,602 33,444 559,492 144,231 180,711 38,064 637,733 479,295 503,287 198,732 2,094,258 Show your accounts receivale to be collected (transfer to 2022 Balance Sheet) 158859 166802 206,045 226942 43,371 49,451 408,274 443,195 Uncollectible amount (bad debt) 8,361 9,516 10,843 12,363 41,082 Schedule 3 - Production Budget Budgeted Year Q3 Following Year Q1 Q2 Q1 Q2 Q4 Year 2,974 Standard table Unit Sales Plus: Desired ending inventory (round up) Total needed Less: Beginning inventory Units to produce 1,800 605 2,405 (650) 1,755 2,016 678 2,694 (605) 2,089 2,258 759 3,017 (678) 2,339 2,529 850 3,379 (759) 2,620 8,603 2,892 11,495 (2,692) 8,803 2,832 892 3,725 -850 2,875 848 Deluxe table Unit sales Plus: Desired ending inventory (round up) Total needed Less: Beginning inventory Units to produce 340 123 463 408 147 555 (123) 432 490 177 667 (147) 520 588 212 800 (177) 623 1,826 659 2,485 (447) 2,038 706 255 961 -212 463 749 Schedule 4 - Direct Materials Budget Year 8,803 2,875 10 28,747 Standard table Square foot of wood needed per unit Square foot of wood needed Plus: Desired ending inventory in units (round up) Total quantity of material needed Less: Beginning inventory of material (units) Material to purchase Multiply: Cost per square foot of wood Total costs of material purchases Q1 1,755 10 17,550 4,178 21,728 5,000 16,728 5 83,640 Budgeted Year Q2 Q3 2,089 2,339 10 10 20,890 23,390 4,678 5,240 25,568 28,630 4,178 4,678 21,390 23,952 5 5 106,950 119,760 Q4 2,620 10 26,200 5,750 31,950 5,240 26,710 5 133,550 10 88,030 19,846 107,876 19,096 88,780 5 443,900 4 4 4 11,499 10,480 2,300 Metal components needed per unit Metal components needed Plus: Desired ending inventory in units (round up) Total quantity of material needed Less: Beginning inventory of material in units Material to purchase Multiply: Cost per metal component Total costs of material purchases 4 7,020 1,672 8,692 2,000 6,692 8,356 1,872 10,228 1,672 8,556 7 59,892 4 9,356 2,096 11,452 1,872 9,580 7 67,060 12,780 2,096 10,684 7 74,788 4 35,212 7,940 43,152 7,640 35,512 7 248,584 46,844 520 463 12 5,556 1,037 6,593 2,038 12 24,456 749 12 8,988 Deluxe table Square foot of premium wood needed per unit Square foot of premium wood needed Plus: Desired ending inventory in units (round up) Total quantity of material needed Less: Beginning inventory of material in units Material to purchase Multiply: Cost per square foot of premium wood Total costs of material purchases 5,579 432 12 5,184 1,248 6,432 1,037 5,395 6.5 35,068 12 6,240 1,496 7,736 1,248 6,488 6.5 42,172 623 12 7,476 1,798 9,274 1,496 7,778 6.5 50,557 6,593 6.5 30,035 3,781 26,254 6.5 170,652 42,855 4 4 4 4 2,996 4 1,852 346 2,198 Metal components needed per unit Metal components needed Plus: Desired ending inventory in units (round up) Total quantity of material needed Less: Beginning inventory of material in units Material to purchase Multiply: Cost per metal component Total costs of material purchases 1,728 416 2,144 346 1,798 8 14,384 2,080 499 2,579 416 2,163 8 17,304 2,492 600 3,092 499 2,593 8 20,744 4 8,152 1,861 10,013 1,261 8,752 2,198 8 17,584 8 70,016 Total cost of direct material 190,923 216,294 246,296 279,639 933,152 Schedule 5 - Cash Payments for Direct Material Purchases Q1 Q4 Year Q1 Budgeted Year Q2 Q3 60,000 - 152,738 38,185 173,035 55,928 Accounts payable, from opening balance Sheet First-quarter purchases Second-quarter purchases Third-quarter purchases Fourth-quarter purchases Total cash payments for inventory 43,259 197,037 60,000 190,923 216,294 246,296 223,711 937,224 49,259 223,711 272,970 212,738 211,220 240,296 Schedule 6 - Direct Labour Budget Budgeted Year Q3 Q1 Q2 04 Year Standard table Units to be produced Multiply: Direct labour hours per unit Total hours required Multiply: Direct labour cost per hour Total direct labour cost 1,755 3 5,265 20 105,300 2,089 3 6,267 20 125,340 2,339 3 7,017 20 140,340 2,620 3 7,860 20 157,200 8,803 3 26,409 20 528,180 463 432 520 623 3 3 3 Product 2 Units to be produced Multiply: Direct labour hours per unit Total hours required Multiply: Direct labour cost per hour Total direct labour cost 3 1,389 20 27,780 1,296 20 25,920 1,560 20 31,200 1,869 20 37,380 2,038 3 6,114 20 122,280 Cash payments for direct labour 133,080 151,260 171,540 194,580 650,460 Schedule 7 - MOH Budget Q2 Year 2,521 Total units to be produced Multiply: Direct labour hours per unit Total hours required Q1 2,218 3 6,654 Budgeted Year Q3 2,859 3 8,577 04 3,243 3 9,729 10,841 3 32,523 7,563 Variable overhead costs: Indirect materials Indirect labour Total variable overhead $ 26,616.00 $30,252.00 $ 34,308.00 $ 38,916.00 $ 130,092.00 $ 33,270.00 $37,815.00 $ 42,885.00 $ 48,645.00 $ 162,615.00 $ 59,886.00 $ 68,067.00 $ 77,193.00 $ 87,561.00 $ 292,707.00 Fixed Overhead Costs: Depreciation Supervisor's salary Total fixed overhead $ 2,000.00 $ 4,000.00 $ 4,000.00 $ 4,000.00 $ 14,000.00 $ 15,000.00 $ 15,000.00 $ 15,000.00 $ 15,000.00 $ 60,000.00 $ 17,000.00 $ 19,000.00 $ 19,000.00 $ 19,000.00 $ 74,000.00 Total manufacturing overhead Less: Depreciation (Non-cash expense) Cash payments for MOH $ 76,886.00 $ 87,067.00 $ 96,193.00 $ 106,561.00 $366,707.00 $ 2,000.00 $ 4,000.00 $ 4,000.00 $ 4,000.00 $ 14,000.00 $ 74,886.00 $83,067.00 $92,193.00 $ 102,561.00 $352,707.00 POHR FIXED MOH rate Fixed MOH is applied on the basis of DLH Fixed OH POHR = $ 11.28 round to two decimal places Schedule 8 - Operating Expense Budget Budgeted Year Q3 Q1 Q2 Q4 Year Variable Costs: Sales commissions (% of sales dollars) Shipping & handling (per units sold) Bad debt expense (sch. 2) Total budgeted variable expense 13,935 10,700 8,361 32,996 15,860 12,120 9,516 37,496 18,071 13,740 10,843 42,654 20,604 15,585 12,363 48,552 68,471 52,145 41,082 161,698 Fixed Costs: Advertising Office salaries Property taxes and insurance Other expenses What-if Total budgeted fixed expenses 5,000 25,000 10,000 4,000 5,000 25,000 10,000 4,000 5,000 25,000 10,000 4,000 5,000 25,000 10,000 4,000 20,000 100,000 40,000 16,000 44,000 44,000 44,000 44,000 176,000 Total budgeted operating expenses Less: Bad debt expense Cash payments for operating expenses 76,996 8,361 68,635 81,496 9,516 71,980 86,654 10,843 75,811 92,552 12,363 80,189 337,698 41,082 296,616 Schedule 9 -Cash Budget Budgeted Year Q3 Q1 Q2 Q4 Year Cash balance, beginning Add receipts: Cash collected on sales Total cash available } Cash Inflows Less: Cash payments Direct material Direct labour Manufacturing overhead Operating expense dispursements Income taxes Fixed asset purchases Dividends paid Interest payment on credit line Total cash payments Cash Outflows Excess (deficit) of cash after payments Less: Minimum required cash balance Balance available (required) for financing Financing: Borrowing Repayments Total financing (repayments) } Financing Cash balance after financing (free cash) Add back: Minimum required cash balance Cash balance, ending Line of credit balance, ending 50,000 Beginning Balance Sheet: Table Company Budgeted Balance Sheet 31-Dec-21 6,000 151,848 111,860 Current assets: Cash Accounts receivable (net of bad debts): Q3 amount to collect Q1 24,624 Q4 amount to collect Q1 102,600 Q4 amount to collect Q2 24,624 Inventory*: Finished goods inventory Standard table Deluxe table 0 Raw materials inventory Standard table: Wood 25,000 Metal 14,000 Deluxe table Total current assets * note: there is no WIP inventory Property, plant, and equipment: 39,000 0 308,708 625,000 (40,000) Fixed Assets Less Accumulated depreciation Total property, plant, and equipment Total assets 585,000 $ 893,708 Current Liabilities: Interest payable on line of credit Line of credit Accounts payable Dividends payable (QTR 1) Income tax payable Total liabilities 1,000 50,000 60,000 12,000 16,000 139,000 Shareholders' equity: Common shares Retained earnings Total shareholders' equity Total liabilities and shareholders' equity 700,000 54,708 754,708 $ 893,708 Direct Material, Direct Labour, & MOH Payment Information: Percentage of direct materials purchases paid in: Current month Following month 80% 20% All direct labour and MOH are paid in the month occurred. Desired Finished Goods Ending Inventory Information: The company desires to have on hand at the end of each month: 30% Finished goods inventory: equal to the % of the following month's sales 20% Direct materials inventory: equal to the % of the following month's production needs. Dec 31, 2021 ending inventories: standard tables (finished goods) deluxe tables (finished goods) Units 650 Dollar Value $ 111,860 0 0 Regular Wood (square feet) Regular Metal components 5,000 2,000 25,000 14,000 Premium Wood Premium metal components 0 0 Selling & Administration Expenses: Variable Costs: Sales commissions (% of sales) Shipping and handling (per unit) 300% 5.00 Fixed Costs: Advertising Office salaries Property taxes and insurance Other expenses What-if 5,000 25,000 10,000 4,000 Investment and other Information: Line of Credit Information Maximum line of credit Annual Interest Rate on LOC Quarterly Interest Rate 2,000 8% 2% Purchase of Machinery Quarter 1 30,000 Annual Dividend 12,000 Declared QTR 4 Paid in QTR 1 Minimum cash balance 6,000 Tax Information: Income Tax Rate (marginal) 18% Prior year taxes payable Paid in QTR 2 16,000 Schedule 1 - Sales Budget Budgeted Year Q3 Following Year Q1 Q1 Q2 Q4 Year Standard Table Budgeted Unit Sales Selling Price Per Unit Standard table Sales Revenue $ $ 1800 199.50 $ 359,100.00 $ 2016 199.50 $ 402,192 $ 2,258 199.50 $ 450,471 $ 2,529 199.50 $ 504,536 $ 8,603 199.50 1,716,299 2,832 2,974 $ 199.50 $ 199.50 $ 565,080 $ 593,334 Deluxe Table Budgeted Unit Sales Selling Price Per Unit Product 2 Sales Revenue $ $ 340 310.00 $ 105,400 $ 408 310.00 $ 126,480 $ 490 310.00 $ 151,900 $ 588 310.00 $ 182,280 $ 1826 310.00 566,060 617 648 $ 310.00 $ 310.00 $ 191,394 $ 200,964 Total Sales Revenue $ 464,500 $ 528,672 $ 602,371 $ 686,816 $ 2,282,359 $ 756,474 $ 794,297 Sales Mix % Standard table Deluxe table 75% 25% Schedule 2 - Cash Collections Q2 Q3 Q4 Total Q1 Q2 $ Sales Cash sales Credit sales Q1 (a) 464,500 $ 185800 278,700 528,672 $ 211468.8 317,203 602,371 $ 240,948 361,423 686,816 $ 274,726 412,089 2,282,359 912,943 1,369,415 $ 756,474 $ 794,297 302,590 317,719 Credit sales collected in current quarter Credit sales from prior quarter Credit sales from second prior quarter Total Cash Collections 97,545 24,624 102,600 410,569 111,021 139,350 24,624 486,464 126,498 158,602 33,444 559,492 144,231 180,711 38,064 637,733 479,295 503,287 198,732 2,094,258 Show your accounts receivale to be collected (transfer to 2022 Balance Sheet) 158859 166802 206,045 226942 43,371 49,451 408,274 443,195 Uncollectible amount (bad debt) 8,361 9,516 10,843 12,363 41,082 Schedule 3 - Production Budget Budgeted Year Q3 Following Year Q1 Q2 Q1 Q2 Q4 Year 2,974 Standard table Unit Sales Plus: Desired ending inventory (round up) Total needed Less: Beginning inventory Units to produce 1,800 605 2,405 (650) 1,755 2,016 678 2,694 (605) 2,089 2,258 759 3,017 (678) 2,339 2,529 850 3,379 (759) 2,620 8,603 2,892 11,495 (2,692) 8,803 2,832 892 3,725 -850 2,875 848 Deluxe table Unit sales Plus: Desired ending inventory (round up) Total needed Less: Beginning inventory Units to produce 340 123 463 408 147 555 (123) 432 490 177 667 (147) 520 588 212 800 (177) 623 1,826 659 2,485 (447) 2,038 706 255 961 -212 463 749 Schedule 4 - Direct Materials Budget Year 8,803 2,875 10 28,747 Standard table Square foot of wood needed per unit Square foot of wood needed Plus: Desired ending inventory in units (round up) Total quantity of material needed Less: Beginning inventory of material (units) Material to purchase Multiply: Cost per square foot of wood Total costs of material purchases Q1 1,755 10 17,550 4,178 21,728 5,000 16,728 5 83,640 Budgeted Year Q2 Q3 2,089 2,339 10 10 20,890 23,390 4,678 5,240 25,568 28,630 4,178 4,678 21,390 23,952 5 5 106,950 119,760 Q4 2,620 10 26,200 5,750 31,950 5,240 26,710 5 133,550 10 88,030 19,846 107,876 19,096 88,780 5 443,900 4 4 4 11,499 10,480 2,300 Metal components needed per unit Metal components needed Plus: Desired ending inventory in units (round up) Total quantity of material needed Less: Beginning inventory of material in units Material to purchase Multiply: Cost per metal component Total costs of material purchases 4 7,020 1,672 8,692 2,000 6,692 8,356 1,872 10,228 1,672 8,556 7 59,892 4 9,356 2,096 11,452 1,872 9,580 7 67,060 12,780 2,096 10,684 7 74,788 4 35,212 7,940 43,152 7,640 35,512 7 248,584 46,844 520 463 12 5,556 1,037 6,593 2,038 12 24,456 749 12 8,988 Deluxe table Square foot of premium wood needed per unit Square foot of premium wood needed Plus: Desired ending inventory in units (round up) Total quantity of material needed Less: Beginning inventory of material in units Material to purchase Multiply: Cost per square foot of premium wood Total costs of material purchases 5,579 432 12 5,184 1,248 6,432 1,037 5,395 6.5 35,068 12 6,240 1,496 7,736 1,248 6,488 6.5 42,172 623 12 7,476 1,798 9,274 1,496 7,778 6.5 50,557 6,593 6.5 30,035 3,781 26,254 6.5 170,652 42,855 4 4 4 4 2,996 4 1,852 346 2,198 Metal components needed per unit Metal components needed Plus: Desired ending inventory in units (round up) Total quantity of material needed Less: Beginning inventory of material in units Material to purchase Multiply: Cost per metal component Total costs of material purchases 1,728 416 2,144 346 1,798 8 14,384 2,080 499 2,579 416 2,163 8 17,304 2,492 600 3,092 499 2,593 8 20,744 4 8,152 1,861 10,013 1,261 8,752 2,198 8 17,584 8 70,016 Total cost of direct material 190,923 216,294 246,296 279,639 933,152 Schedule 5 - Cash Payments for Direct Material Purchases Q1 Q4 Year Q1 Budgeted Year Q2 Q3 60,000 - 152,738 38,185 173,035 55,928 Accounts payable, from opening balance Sheet First-quarter purchases Second-quarter purchases Third-quarter purchases Fourth-quarter purchases Total cash payments for inventory 43,259 197,037 60,000 190,923 216,294 246,296 223,711 937,224 49,259 223,711 272,970 212,738 211,220 240,296 Schedule 6 - Direct Labour Budget Budgeted Year Q3 Q1 Q2 04 Year Standard table Units to be produced Multiply: Direct labour hours per unit Total hours required Multiply: Direct labour cost per hour Total direct labour cost 1,755 3 5,265 20 105,300 2,089 3 6,267 20 125,340 2,339 3 7,017 20 140,340 2,620 3 7,860 20 157,200 8,803 3 26,409 20 528,180 463 432 520 623 3 3 3 Product 2 Units to be produced Multiply: Direct labour hours per unit Total hours required Multiply: Direct labour cost per hour Total direct labour cost 3 1,389 20 27,780 1,296 20 25,920 1,560 20 31,200 1,869 20 37,380 2,038 3 6,114 20 122,280 Cash payments for direct labour 133,080 151,260 171,540 194,580 650,460 Schedule 7 - MOH Budget Q2 Year 2,521 Total units to be produced Multiply: Direct labour hours per unit Total hours required Q1 2,218 3 6,654 Budgeted Year Q3 2,859 3 8,577 04 3,243 3 9,729 10,841 3 32,523 7,563 Variable overhead costs: Indirect materials Indirect labour Total variable overhead $ 26,616.00 $30,252.00 $ 34,308.00 $ 38,916.00 $ 130,092.00 $ 33,270.00 $37,815.00 $ 42,885.00 $ 48,645.00 $ 162,615.00 $ 59,886.00 $ 68,067.00 $ 77,193.00 $ 87,561.00 $ 292,707.00 Fixed Overhead Costs: Depreciation Supervisor's salary Total fixed overhead $ 2,000.00 $ 4,000.00 $ 4,000.00 $ 4,000.00 $ 14,000.00 $ 15,000.00 $ 15,000.00 $ 15,000.00 $ 15,000.00 $ 60,000.00 $ 17,000.00 $ 19,000.00 $ 19,000.00 $ 19,000.00 $ 74,000.00 Total manufacturing overhead Less: Depreciation (Non-cash expense) Cash payments for MOH $ 76,886.00 $ 87,067.00 $ 96,193.00 $ 106,561.00 $366,707.00 $ 2,000.00 $ 4,000.00 $ 4,000.00 $ 4,000.00 $ 14,000.00 $ 74,886.00 $83,067.00 $92,193.00 $ 102,561.00 $352,707.00 POHR FIXED MOH rate Fixed MOH is applied on the basis of DLH Fixed OH POHR = $ 11.28 round to two decimal places Schedule 8 - Operating Expense Budget Budgeted Year Q3 Q1 Q2 Q4 Year Variable Costs: Sales commissions (% of sales dollars) Shipping & handling (per units sold) Bad debt expense (sch. 2) Total budgeted variable expense 13,935 10,700 8,361 32,996 15,860 12,120 9,516 37,496 18,071 13,740 10,843 42,654 20,604 15,585 12,363 48,552 68,471 52,145 41,082 161,698 Fixed Costs: Advertising Office salaries Property taxes and insurance Other expenses What-if Total budgeted fixed expenses 5,000 25,000 10,000 4,000 5,000 25,000 10,000 4,000 5,000 25,000 10,000 4,000 5,000 25,000 10,000 4,000 20,000 100,000 40,000 16,000 44,000 44,000 44,000 44,000 176,000 Total budgeted operating expenses Less: Bad debt expense Cash payments for operating expenses 76,996 8,361 68,635 81,496 9,516 71,980 86,654 10,843 75,811 92,552 12,363 80,189 337,698 41,082 296,616 Schedule 9 -Cash Budget Budgeted Year Q3 Q1 Q2 Q4 Year Cash balance, beginning Add receipts: Cash collected on sales Total cash available } Cash Inflows Less: Cash payments Direct material Direct labour Manufacturing overhead Operating expense dispursements Income taxes Fixed asset purchases Dividends paid Interest payment on credit line Total cash payments Cash Outflows Excess (deficit) of cash after payments Less: Minimum required cash balance Balance available (required) for financing Financing: Borrowing Repayments Total financing (repayments) } Financing Cash balance after financing (free cash) Add back: Minimum required cash balance Cash balance, ending Line of credit balance, ending 50,000
