I just need a Schedule of Cash Disbursements for materials (one schedule per each material) - using this excel format

For the year ended December 31, 2020 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Year VP from November VP from December X Jan X X X X X Feb Mar Apr May Jun x X X X X X Jul X X Aug Oct Nov X Dec Wood Parts-Musical Instruments Ltd Wood Parts-Musical Instruments LTD (WPMI) is a worldwide wooden musical part manufacturing firm based in North America. After many years in the market, the Peterborough division, which produces one product called XylophonelBX reaches a spike on the sales level. The Management wants to have a significant investment to expand the facility and increase production, but it is requesting from the division to prepare a monthly 2021 budget. The fiscal year ends on Dec 31. The division manager forecast the following sales: For the year ended Dec 31, 2020: 95,000 units at $50.00 each * (*Expected sales for the year ended December 31, 2020 are based on actual sales to date and budgeted sales for the duration of the year) For the year ended Dec 31, 2021: 100,000 units at $50.00 each For the year ended Dec 31, 2022: 110,000 units at $50.00 each. 1. The management expect a distribution of sales for each month based on percentage of the total budgeted as follow: Jan, Feb & Mar 2% each; Apr, Aug and Sep 6% each; May, Jun & Jul 9% each; Oct 10%; Nov 17% & Dec 22%. 2. From previous experience, management has determined that an ending inventory equal to 20% of the next month's sales is required to fit the buyer's demands. 3. Because sales are seasonal, Wood Parts-Musical Instruments must rent an additional storage facility from October to December to house the additional inventory on hand. The only related cost is a flat $10,000 per month, payable at the beginning of the month. 4. The Xylophone lbX requires three types of raw materials: . Plastic PR is a compact material that is purchased in powder form. Each XylophonelBX requires 0.5 kilograms of Plastic PR, at a cost of $25.00 per kilogram. The supplier of Plastic PR tends to be somewhat erratic so Wood Parts-Musical Instruments finds it necessary to maintain an inventory balance equal to 40% of the following month's production needs as a precaution against stock-outs. . A plastic wheel assembly is purchased in kit form, and is attached during the assembly process. For a small premium, Wood Parts-Musical Instruments has made a JIT agreement with the supplier which includes on-time and quality assurances. Each Xylophone1BX uses two kits, which cost $1.00 each. The supplier must be paid in cash . The final component for the musical instrument is a length of cord which is used to pull the mobile Xylophone1BX along the ground or floor. The cord supplier must be paid in cash. On the first day of every month she delivers exactly the right amount to manufacture the budgeted number of units for that month. It costs $2.00 per meter and Wood Parts-Musical Instruments uses one-half meter for each XylophonelBX. 3 5. The beginning accounts payable (associated with Plastic PR purchases only) will consist of $101,278 arising from the following estimated material purchases for November and December of 2020: Material purchases in November 2020: Material purchases in December 2020: $213,850 $ 138,400 Wood Parts-Musical Instruments pays for 50% of a month's purchases in the month of purchase, 35% in the following month and the remaining 15% two months after the month of purchase. There is no early payment discount. 6. The manufacturing process for Xylophone1BXs is divided into three separate activities. The first step is the forming process, during which the Plastic PR is liquefied and formed into several shapes that snap together to make the musical instrument. During the next stage, the molded pieces are fused together using heat. This step is referred to as the assembly stage. The last stage is for finishing, during which the wheel and the cord are attached and the musical instrument is prepared for shipping. 7. The first two steps of the manufacturing process are highly automated, so the only employees are three supervisors, who are trained to operate the equipment and make repairs as required. The supervisors work shifts, allowing the plant to operate for longer hours during the busier months. They are also responsible for managing the employees who work in the finishing department. 8. The last step, finishing, is the only part of the manufacturing process that employs direct labour. Most of the staff work on a part-time basis, so their hours can be set based on production requirements. This also eliminates the need for overtime. These employees are paid based on the number of units produced. They receive an average of $20.00 per hour including employee benefits. Each Xylophone1BX spends 18 minutes in the finishing department. 9. Because of the large difference in the manufacturing stages, Wood Parts-Musical Instruments uses two separate variable manufacturing overhead rates. The forming and assembly departments use similar equipment and with the company's concentration on a single product, the manufacturing overhead is allocated based on volume (i.e. the units produced). The combined unit variable overhead manufacturing rate for forming and assembly is $6.50, consisting of: Utilities--$3.00; Indirect Materials--$1.00; Plant maintenance--$1.50; environmental fee--$0.70; and Other--$0.30. The best cost driver for the finishing department is considered to be direct labour hours. Here the predetermined variable manufacturing overhead is expected to be $4.00 per hour. 10. Fixed manufacturing overhead costs are not separated between departments. The total costs for the entire year are as follows: Training and development Supervisor's salary Depreciation on equipment Insurance Other $ 43,620 269,400 178,800 96,000 117,600 $ 705,420 . The annual insurance premium is paid at the beginning of September each year. There should be no change in the premium from last year. . All other "cash-related" fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred. Wood Parts-Musical Instruments Ltd uses the straight line method of Depreciation. 11. Selling and administrative expenses are known to be a mixed cost; however, there is a lot of uncertainty about the porti that is fixed. Previous year's experience has provided the following information (rounded): Lowest level of sales: Highest level of sales: 70,000 units Total Operating Expenses: $ 769,800 110,000 units Total Operating Expenses: $1,009,800 The annual amount of Depreciation on office furniture and equipment is only $21,000and this amount is already included in the fixed portion of the selling and administration expenses. Not included in the above expenses is bad debt expense. Payments for selling and administrative expenses occur in the month in which they are incurred. 12. Sales are on cash and on account (credit), 70% of the sales are collected during the month of sales, 20% the following month, 9.5% the month thereafter. 1/2 of 1% of sales are considered uncollectible (Bad debt expense) 13. Sales in November and December 2020 are expected to be $807,500 and $1,045,000 respectively. Based on the above collection pattern this will result in Accounts Receivable of $384,988 at December 31, 2020 which will be collected in January and February, 2021. 14. During the fiscal year December 31, 2021, ood Parts-Musical Instruments will be required to make monthly income tax installment payments of $5,000. Outstanding income taxes from the year ended December 31, 2020 must be paid in April 2021. Income tax expense is estimated to be 30% of net income. Income taxes for the year ended December 31, 2021, in excess of installment payments, will be paid in April, 2022. 15. Wood Parts-Musical Instruments approved to purchase new equipment for $306,000 cash. Three equal payments to the suppliers will be in February 2021, March 2021 and April 2021. The equipment depreciation from this equipment, is already considered (included) in the manufacturing overhead cost given. 16. The company wants to maintain at the end of each month, a minimum bank balance of $80,000. In case the company has a deficiency of money or it is not able to reach this minimum bank balance, the company can borrow from a line of credit at the same bank at the rate of 12% per annum. All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month. All borrowings and repayments from the bank should be in multiples of $1,000 and interest must be paid at the end of each month. Interest is calculated on the balance at the beginning of the month, which includes any amounts borrowed that month. 17. Wood Parts-Musical Instruments LTd pays dividends at the end of each quarter, and based on the last meetings, the company will continue paying $50,000 dividends per quarter. 18. A listing of the estimated balances in the company's ledger accounts as of December 31, 2020 is given below: Statement of Financial Position at Dec 31, 2020. Assets Cash Accounts receivable Inventory-raw materials (PlasticRP) Inventory-finished goods Prepaid Insurance Capital assets (net) Total assets $ 83,365 384,988 10,000 14,200 64,000 724,000 $1.280,553 Liabilities and Shareholders' Equity Accounts payable $ 101,278 Income taxes payable 22,500 Capital stock 1,000,000 Retained Earnings 156,775 Total liabilities and shareholders' equity $1.280.553 For the year ended December 31, 2020 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Year VP from November VP from December X Jan X X X X X Feb Mar Apr May Jun x X X X X X Jul X X Aug Oct Nov X Dec Wood Parts-Musical Instruments Ltd Wood Parts-Musical Instruments LTD (WPMI) is a worldwide wooden musical part manufacturing firm based in North America. After many years in the market, the Peterborough division, which produces one product called XylophonelBX reaches a spike on the sales level. The Management wants to have a significant investment to expand the facility and increase production, but it is requesting from the division to prepare a monthly 2021 budget. The fiscal year ends on Dec 31. The division manager forecast the following sales: For the year ended Dec 31, 2020: 95,000 units at $50.00 each * (*Expected sales for the year ended December 31, 2020 are based on actual sales to date and budgeted sales for the duration of the year) For the year ended Dec 31, 2021: 100,000 units at $50.00 each For the year ended Dec 31, 2022: 110,000 units at $50.00 each. 1. The management expect a distribution of sales for each month based on percentage of the total budgeted as follow: Jan, Feb & Mar 2% each; Apr, Aug and Sep 6% each; May, Jun & Jul 9% each; Oct 10%; Nov 17% & Dec 22%. 2. From previous experience, management has determined that an ending inventory equal to 20% of the next month's sales is required to fit the buyer's demands. 3. Because sales are seasonal, Wood Parts-Musical Instruments must rent an additional storage facility from October to December to house the additional inventory on hand. The only related cost is a flat $10,000 per month, payable at the beginning of the month. 4. The Xylophone lbX requires three types of raw materials: . Plastic PR is a compact material that is purchased in powder form. Each XylophonelBX requires 0.5 kilograms of Plastic PR, at a cost of $25.00 per kilogram. The supplier of Plastic PR tends to be somewhat erratic so Wood Parts-Musical Instruments finds it necessary to maintain an inventory balance equal to 40% of the following month's production needs as a precaution against stock-outs. . A plastic wheel assembly is purchased in kit form, and is attached during the assembly process. For a small premium, Wood Parts-Musical Instruments has made a JIT agreement with the supplier which includes on-time and quality assurances. Each Xylophone1BX uses two kits, which cost $1.00 each. The supplier must be paid in cash . The final component for the musical instrument is a length of cord which is used to pull the mobile Xylophone1BX along the ground or floor. The cord supplier must be paid in cash. On the first day of every month she delivers exactly the right amount to manufacture the budgeted number of units for that month. It costs $2.00 per meter and Wood Parts-Musical Instruments uses one-half meter for each XylophonelBX. 3 5. The beginning accounts payable (associated with Plastic PR purchases only) will consist of $101,278 arising from the following estimated material purchases for November and December of 2020: Material purchases in November 2020: Material purchases in December 2020: $213,850 $ 138,400 Wood Parts-Musical Instruments pays for 50% of a month's purchases in the month of purchase, 35% in the following month and the remaining 15% two months after the month of purchase. There is no early payment discount. 6. The manufacturing process for Xylophone1BXs is divided into three separate activities. The first step is the forming process, during which the Plastic PR is liquefied and formed into several shapes that snap together to make the musical instrument. During the next stage, the molded pieces are fused together using heat. This step is referred to as the assembly stage. The last stage is for finishing, during which the wheel and the cord are attached and the musical instrument is prepared for shipping. 7. The first two steps of the manufacturing process are highly automated, so the only employees are three supervisors, who are trained to operate the equipment and make repairs as required. The supervisors work shifts, allowing the plant to operate for longer hours during the busier months. They are also responsible for managing the employees who work in the finishing department. 8. The last step, finishing, is the only part of the manufacturing process that employs direct labour. Most of the staff work on a part-time basis, so their hours can be set based on production requirements. This also eliminates the need for overtime. These employees are paid based on the number of units produced. They receive an average of $20.00 per hour including employee benefits. Each Xylophone1BX spends 18 minutes in the finishing department. 9. Because of the large difference in the manufacturing stages, Wood Parts-Musical Instruments uses two separate variable manufacturing overhead rates. The forming and assembly departments use similar equipment and with the company's concentration on a single product, the manufacturing overhead is allocated based on volume (i.e. the units produced). The combined unit variable overhead manufacturing rate for forming and assembly is $6.50, consisting of: Utilities--$3.00; Indirect Materials--$1.00; Plant maintenance--$1.50; environmental fee--$0.70; and Other--$0.30. The best cost driver for the finishing department is considered to be direct labour hours. Here the predetermined variable manufacturing overhead is expected to be $4.00 per hour. 10. Fixed manufacturing overhead costs are not separated between departments. The total costs for the entire year are as follows: Training and development Supervisor's salary Depreciation on equipment Insurance Other $ 43,620 269,400 178,800 96,000 117,600 $ 705,420 . The annual insurance premium is paid at the beginning of September each year. There should be no change in the premium from last year. . All other "cash-related" fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred. Wood Parts-Musical Instruments Ltd uses the straight line method of Depreciation. 11. Selling and administrative expenses are known to be a mixed cost; however, there is a lot of uncertainty about the porti that is fixed. Previous year's experience has provided the following information (rounded): Lowest level of sales: Highest level of sales: 70,000 units Total Operating Expenses: $ 769,800 110,000 units Total Operating Expenses: $1,009,800 The annual amount of Depreciation on office furniture and equipment is only $21,000and this amount is already included in the fixed portion of the selling and administration expenses. Not included in the above expenses is bad debt expense. Payments for selling and administrative expenses occur in the month in which they are incurred. 12. Sales are on cash and on account (credit), 70% of the sales are collected during the month of sales, 20% the following month, 9.5% the month thereafter. 1/2 of 1% of sales are considered uncollectible (Bad debt expense) 13. Sales in November and December 2020 are expected to be $807,500 and $1,045,000 respectively. Based on the above collection pattern this will result in Accounts Receivable of $384,988 at December 31, 2020 which will be collected in January and February, 2021. 14. During the fiscal year December 31, 2021, ood Parts-Musical Instruments will be required to make monthly income tax installment payments of $5,000. Outstanding income taxes from the year ended December 31, 2020 must be paid in April 2021. Income tax expense is estimated to be 30% of net income. Income taxes for the year ended December 31, 2021, in excess of installment payments, will be paid in April, 2022. 15. Wood Parts-Musical Instruments approved to purchase new equipment for $306,000 cash. Three equal payments to the suppliers will be in February 2021, March 2021 and April 2021. The equipment depreciation from this equipment, is already considered (included) in the manufacturing overhead cost given. 16. The company wants to maintain at the end of each month, a minimum bank balance of $80,000. In case the company has a deficiency of money or it is not able to reach this minimum bank balance, the company can borrow from a line of credit at the same bank at the rate of 12% per annum. All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month. All borrowings and repayments from the bank should be in multiples of $1,000 and interest must be paid at the end of each month. Interest is calculated on the balance at the beginning of the month, which includes any amounts borrowed that month. 17. Wood Parts-Musical Instruments LTd pays dividends at the end of each quarter, and based on the last meetings, the company will continue paying $50,000 dividends per quarter. 18. A listing of the estimated balances in the company's ledger accounts as of December 31, 2020 is given below: Statement of Financial Position at Dec 31, 2020. Assets Cash Accounts receivable Inventory-raw materials (PlasticRP) Inventory-finished goods Prepaid Insurance Capital assets (net) Total assets $ 83,365 384,988 10,000 14,200 64,000 724,000 $1.280,553 Liabilities and Shareholders' Equity Accounts payable $ 101,278 Income taxes payable 22,500 Capital stock 1,000,000 Retained Earnings 156,775 Total liabilities and shareholders' equity $1.280.553