Need help calculating for the DATA Section

1. Balance Sheet (Statement of Financial Position) as at 30 June 2020: Cash $ 9,000 Trade Creditors $ 8,520 Accounts Receivable 85 210 Shareholders' Equity 150,000 Flaw Materials Inventory 1,408 Retained Bamings 80,548 Finished Goods Inventory 11,450 (Kickboard $81,495; Pool buoy $2,954) Plant and Equipment [Nell 125,000 TOTAL ASSETS $230,008 TOTAL LIABILITY & EQUITY $ 239,008 2. The following schedule details the recent actual monthly unit sales achieved for each product to 30 June 2020. Additionally, the sales manager has projected sales volume forecasts for each product to November 2020: PRODUCT APR MAY JUN JUL AUG BEP OCT NOV rockboard 1,900 3000 2 700 2400 2.500 2,300 2900 3.000 Pool Buoy 1,300 1,400 1,300 1, 100 1,300 1,000 900 1,000 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 6 months and, In the absence of policy changes, do not foresee any change In the selling prices In the next 6 month perlod. 4. All sales are on credit: 15%% are collected In the month of sale, 45%% In the month following sale and the remaining 40%% 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 KICKBOARD POOL BUOYS Feon 2 litres 1 10.45/1tre Hardener S Itres 610.701bre 2 tres 240.70 be Labour Mixing Department 18 minutes #$20 hour 12 minutes #$30 hour Moulding Deparment 12 minutes @ $30 hour 12 minutes #$30/hour Factory Overhaad: Mixing Department $7100/directlabour hour $7 00/direct labour hour Moulding Deparment $1 1.00/dred 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 pollcy 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 sufficlent to support 30%% of the following month's forecast sales. 9. The number of Inventory Mems held as at 30 June 2020 was as follows: MATERIAL PRODUCT LITRE UNIT Rain 50 40 live2 Hardener 8772 lives Kickboards 720 unit Pool Buoys 330 unit 10. Flowed selling and administration expenses are $25,000 per month (Including $1,000 of depreciation on office equipment). Sales commissions are pald at 7%% of total sales dollars. Selling and administration expenses are pald 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,Rice has a standby credit arrangement In place with Its bank to borrow the exact amount needed to achieve the desired cash balance. IT 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 repald to the bank. 12. The Interest rate applicable to the bank loan Is 12%% per annum to be pald 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 dollarj: 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 allached 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 perfomed. Hint: Check that your budget reports match the key checking figures provided In Appendix 2 and the balance sheet balances.PART B (35 Marks] The directors of Rice Products are concemed about the level of profitability of the company and the need to borrow from the bank. They have discussed the production processes with the production manager and have been assured that the manufacturing departments are operating efficiently and that the equipment used Is technically superior to that of competitors In the market. The following pollcles which have an Impact on the company's operating cycle have therefore been suggested as ways to Improve the level of profitability and provide a positive cash flow to avold the need to borrow. As the senlor management accountant you have been asked to conduct a sensitivity analysis to determine the effect of each of the possible polley changes currently being considered by the directors. The changes being considered are: (3) Offer customers a cash discount of 2.5%% If payment Is made In the month of sale. It is anticipated that this would Increase the percentage of customers paying In the month of sale to 40%%, and those paying In the following two months would reduce by 15%% and 10%% respectively. (b) Increase the unit price by $2 for kickboards and $1 for pool buoys. The market for swimming alds Is very price sensitive and the marketing manager predicts that if this polley Is adopted unit Gales will decline by 10%% for kickboards and 5%% for pool buoys. Negotiate with suppliers to extend the existing credit arrangements so that 50%% of purchases are pald In the month of purchase and the balance of trade creditors to be pald In the following month. (d) Reduce the margin of safety held In the bank account to $4,500. (2) Restructure the remuneration packages of staff In the sales division. It Is proposed to Increase the sales commission pald to sales persons by 3%% which will reduce monthly fixed salariescommitment by $2,000 per month. It Is expected that the additional Incentive avallable to the Gales stall will result In an Increase In unit sales of 5%% for each product line. () Conalderation Is being given to undertaking a $1,500 per month marketing campaign to promote the superiority and excellent value of Rice's products. It Is expected that the marketing strategy will result In an Increase In unit sales of 6%% for each product line. (g) Reamange the factory floor layout and work practices to enable a reduction In the dealred level of Inventory to be held. Raw materials would be maintained at 50%% of next month's production requirements and finished goods at 20%% of next month's sales. The change In Inventory levels to be held would require a multi-6killed workforce and necessitate a re-negotiation of the current Enterprise Agreement. It Is anticipated the wage rates would Increase by $0.80 per hour for each department You are required to conduct a separate "WHAT IF analysis for each of the above proposals to 365e86 the effect of EACH policy on the level of profitability and cash flows for Rice Products for the three month perlod ending 30 September 2020 (seven different outcomes). Hint: Construct Inking mechanleme In the report section to the data section.