I need help completing schedule 7,

Rice Products Pty- Ltd- is a local rm that produces two moulded plastic products: kickboards and pool buoys. The products are manufactured in a two step process and each process is treated as a separate cost centre. In the rst process, the mixing department, a special resin is combined with a hardener. Moulding occurs in the second process where the output from the mixing department is poured into special shaped moulds. Due to the nature of the chemical compounds contained in the resin and hardener, the manufacturing process occurs very rapidly- Production is therefore scheduled so that no work-in-process inventory is held at the end of each day- The following information has been extracted from the accounting records of Rice Products or obtained through discussions with the senior management team: 1- Balance Sheet [Statement of Financial Position} as at 3'3 June 22: Cash $ RUDD Trade Creditors $ 3,520 Accwnts Receivable 35,21 Shareholders\" Equity 1,[l Raw Materials Inventory 8,408 Retained Earnings $1,548 Finished Goods Inventory 11,4511] {Kickboard $3,496; Pool buoy $2,954} Plant and Equipment {Net} 1251100 TOTAL ASSETS 3 235963 TOTAL LIABILITY ti: EQUITY $ 235,055 2- The following schedule details the recent actual monthly unit sales achieved for each product to 3D June 22t}. Additionally, the sales manager has projected sales volume forecasts for each product to November EDED: mm M mmmmmmm 3- Kickboards sell for $28 each and pool buoys for $24 each. Due to the tight cost control practices Rice Products has been able to maintain selling prices for the last Ei months and, in the absence of policy changes, do not foresee any change in the selling prices in the next 6 month period- 4- All sales are on credit: 15% are collected in the month of sale, 45% in the month following sale and the remaining time is collected in the second month following sale- 5. Details of the standard costs to manufacture one unit of each product are provided below: FACTORS OF PRODUCTION KICKBOARDS POOL BUOYS Materials: Resin 2 litres @$0.45/litre 3 litres @$0.45/litre Hardener 5 litres @$0.70/litre 2 litres @$0.70/litre Labour: Mixing Department 18 minutes @$20/hour 12 minutes @$20/hour Moulding Department 12 minutes @$30/hour 12 minutes @$30/hour Factory Overhead: Mixing Department $7.00/direct labour hour $7.00/direct labour hour Moulding Department $11.00/direct labour hour $11.00/direct labour hour 6. Depreciation on factory equipment is computed to be $1.00 per labour hour for each department and is included in the factory overhead rates shown above. 7. Materials are purchased on credit. Rice pays 60% of accounts in the month of purchase and the remaining 40% in the following month. Labour costs and all overhead costs (except depreciation) are paid as they are incurred. Monthly differences between applied and actual overhead costs are expected to be negligible. 8. Rice has an inventory policy in place where purchases of raw materials are scheduled to be 60% of the next month's anticipated production needs. Additionally, production is scheduled so that the number of finished units on hand at the end of each month is sufficient to support 30% of the following month's forecast sales. 9. The number of inventory items held as at 30 June 2020 was as follows: MATERIAL/PRODUCT LITRE/UNIT Resin 5040 litres Hardener 8772 litres Kickboards 720 units Pool Buoys 330 units 10. Fixed selling and administration expenses are $25,000 per month (including $1,000 of depreciation on office equipment). Sales commissions are paid at 7% of total sales dollars. Selling and administration expenses are paid in the month incurred. 11. Rice's management has a policy of maintaining a cash balance of $9,000 at the end of each month. This amount represents a buffer that is maintained as a margin of safety against unforeseen events which might cause significant departures from budget estimates. If this requirement cannot be met,achieve the desired cash balance. If Rice has a cash balance greater than $9,000 at the end of any month and an outstanding loan balance then the cash in excess of $9,000 is repaid to the bank. 12. The interest rate applicable to the bank loan is 12% per annum to be paid on a monthly basis on the outstanding principal at the end of the previous month. 13. Rice uses the FIFO (first in first out) method to value ending inventory. PART A (65 Marks) You have been appointed to the position of senior management accountant at Rice Products. It is your responsibility to prepare a master budget for the next quarter (July, August and September 2020). The master budget documents are to consist of the following reports (the budgets should show the figures for each month and a total for the quarter where appropriate and round calculations to the nearest dollar): Sales Revenue Budget Production Budget (Units) . Direct Material Purchase Budget (Unites and $) Direct Labour Budget (Hours and $) Factory Overhead Budget Selling, Admin and Finance Budget Cash Budget Income Statement (Statement of Financial Performance) Balance Sheet (Statement of Financial Position) The budget documents are to be prepared using Excel spreadsheet and the template should consists of three sections: an index section to identify yourself and spreadsheet layout, a data section that contains relevant case study information which will be used to construct the budgets, and a budget reports section. Appendix 1 and Appendix 2 have been attached to provide a suggested format for the design of the data section and the budget reports section. It is important to note that the figures in the report section should be derived from FORMULA ONLY that relates to information in the data section. That is the budget documents should be constructed in such a manner that will enable sensitivity ("WHAT IF ) analyses to be performed. Hint: Check that your budget reports match the key checking figures provided in Appendix 2 and the balance sheet balances.Schedule 7 Cash Budget July Aug Sept Total Total Beginning Balance 9.000 9.000 9,000 27,000 Collections: Current month's sales 14,040 15,600 13,260 42,900 Previous month's sales 40,320 42,120 46,800 129,240 Second previous month's sales 33,760 35,840 37,440 107,040 Total Collections 97,120 102,560 106,500 306,180 Cash available for needs Payments: Current month's purchases 8,537 8,338 8,821 Month following purchase 5,045 5,691 5,559 Direct Materials 14,229 13,896 14,702 Direct Labour 19,400 19,900 18,760 Factory Overhead 14,754 15,149 14,156 Selling & Admin 31,552 32,318 31,232 Total disbursements 93,516 95,292 93,229 Cash Excess 3,604 7,268 13,271 Loan draw down 5,396 1,732 4,387 Amount available for Loan repayment Loan repayment 9,000 Interest 53.96 17 Balance S 9,000 S 4,271 Loan Balance 3.486MASTER BUDGET - RICE PTY LTO Schedule 1 Sales Budget July Aug Sept Total Kickboards 2,400 2,600 2.300 7,300 Price per Uni 28 28 28 28 Sales dollars 67,200 2.800 64,400 204,400 Pool Bouys 1,100 ,300 1,000 3,400 Price per Unit 24 24 24 24 Sales dollars 26,400 31,200 24,000 81,600 Total Sales Revenue Budget 13,600 104 000 38.400 286,000 Schedule 2 Rice PTY LTD Production Budget - Kickboards For July. August, September Ending 2020 July Aug Sept Total Kickboards Budget Sale: 2,400 2,600 2,300 7,300 Target Ending Inventory 780 590 870 870 Units Required 3,180 3,290 3,170 8,170 Beginning Inventor 720 780 690 720 Total Production Budget 2,460 2,510 2.480 7.450 Rice PTY LTD Production Budget - Pool Bougs For July. August, September Ending 2021 Pool Bougs Juk Aug Sept Total Budget Sales 1,100 1,300 1,000 3,400 Target Ending Inventory 390 300 270 :70 Units Required 1,490 1,600 1,270 3,670 Beginning Inventory 330 390 300 330 Total Production Budget ,160 ,210 970 3.340 Schedule 3 Rice PTY LTD Direct Materials Purchases Budget - Resin For July. August, September Ending 2020 July Aug Sept Total Resin [Litres] Resin, End inventory 5,190 4.722 5,190 5,190 Resin production I 8,400 3,650 7,870 24,920 Total Resin needed 13,590 13,372 3,060 30,110 Less: Resin, beginning inventory 5,040 5,190 ,722 5,040 Total resin to purchase 8.550 B.182 3.338 25.070 $ per Litre 0.45 0.45 0.45 0.45 Total cost of Resin to purchase 3,848 3,682 3,752 1,282 Rice PTY LTD Direct Materials Purchases Budget Hardener For July. August, September Ending 2020 July Aug Sept Total Hardener [Litres) Hardener, end inventory 8,982 8,604 9,906 9,906 Hardener Production needs 14,620 4.970 14,340 43,930 Total Hardener needed 23,602 23,574 14.246 53,836 Less: Resin, beginning inventory 8,772 3,982 8.604 8,772 Total Hardener to purchase 14.830 14.592 15.642 45.064 $ per Litres 0.70 0.70 0.70 0.70 Total cost of Resin to purchase 10,381 0,214 0,949 31,545 July August Sept Total Total OM Purchases Budget 14,229 13,896 14,702 42,826Schedule 4 Direct Labour Budget Mixing Direct Labour Budget July Aug Sep Total Mixing Department Kickboard Budgeted Units to Produce 2.460 2.510 2.480 7.450 Required hours per unit 1.3 D.3 1.3 0.3 Total hours needed 738 753 744 2,235 Cost per hour 20 20 20 20 Mixing labour cost - Kickboard 14,760 15,060 14.880 44,700 Mizing Department - Pool Buoys Budgeted Units to Produce 1,160 ,210 970 3,340 Required hours per unit 1.2 0.2 1.2 1.2 Total hours needed 232 242 94 168 Cost per hour 20 20 20 20 Mixing labour cost - Pool Buoys 1.640 1.840 3,880 13,360 Total mixing department labour cost 19,400 19,900 18,760 58,060 Moulding Jul Aug Sept Total Moulding Department Kickboard Budgeted Units to Produce 2,460 2,510 2,480 7,450 Required hours per unit 0.2 $ 0.2 $ 0.2 $ 0.2 Total hours needed 492 $ 602 $ 496 $ .490 Cost per hour 30 F $ 30 $ 30 $ 30 Mixing labour cost - Kickboard 14,760 $ 15,060 $ 14.880 | $ 44.700 Moulding Department Kickboard Budgeted Units to Produce $ 1,160 $ 1,210 $ 970 $ 3,340 Required hours per unit $ 0.2 $ 0.2 0.2 Total hours needed 232 242 194 668 Cost per hour $ 30 $ 30 $ 30 $ 30 Mixing labour cost - Kickboard $ 6.960 $ 7,260 $ 5,820 | $ 20,040 Total moulding department labour cost $ 21,720 $ 22,320 $ 20,700 $ 64,740 Total mizing and moulding labour cost 41,120 $ 42.220 $ 39,460 $ 122,800 Schedule 5 Factory Overhead Budget Jul Aug Sept Total Mizing Department Kickboards direct labour hours 738 753 744 2,235 Pool Buoys Direct labour hours 232 242 194 Total direct labor hours in mixing department 970 995 938 2,90 Overhead cost per hour 7 7 7 Total Factory overhead miking department 6,790 3,965 5,566 20,321 Moulding Department Kickboards direct labour hours 492 502 496 1,490 Pool Buoys Direct labour hours 232 242 194 568 Total direct labor hours in mixing department 724 744 690 2,158 Overhead cost per hour 11 11 11 11 Total Factory overhead mixing department 7,964 3.184 7,590 23,738 Total Factory Overhead Budget 14,754 $ 15,149 $ 14,156 $ 44,059Schedule 6 Selling, Admin & Finance Budget July Aug Sept Total Budgeted Sales 93,600 104,000 88,400 286,000 Commission 7% 7% 7% 7% Variable expense 6552 7280.00 6188 20020 Fixed cost 25000 25000 25000 75000 interest 38 44 82 Total expense 31552 32318 31232 95102