Hi,

I have the following information provided and need to prepare an master budget in excel. I am having trouble specifically with the Cash Budget (with collections, with regards to working in Accounts Receivable), and also with Selling, Admin and Finance Budget, as the answer I get is $95,020 and is meant to be $95,102.

Here is the information:

MASTER BUDGET CASE STUD"IIr RICE PRODUCTS PT'I' LIMITED Ri_ce 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 (Statern ent of Financial Position} as at 30 June 2020: Cash $ 0,000 Trade Creditors Accounts Receivable 05,210 Shareholders' Equity Raw Materials Inventory 0,400 Retained Earnings Finished Goods Inventory 11,450 [Kickboard $3,490; Pool buoy $2,954] Plant and Equipment {Net} 125,000 TOTAL ASSETS S 239,050 TOTAL LIABILITY S Et'IilIlT'tr S 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: mmmmmm. _---WW \"mmmmmmm 3. Kickboards sell for $20 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 5 months and, in the absence of policy changes, do not foresee any change in the selling prices in the next 0 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 40% is collected in the second month following sale. 2104AFE Management Accounting_T2 2020 Master Budget Case Study 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 $1 1.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, 2104AFE Management Accounting_T2 2020 Master Budget Case Study Rice has a standby credit arrangement in place with its bank to borrow the exact amount needed to 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.Schedule 1 Sales Budget DATA SECTION July Aug Sept Total Kickboards 67,20 72,800 64,400 204,400 Balance Sheet 30/6/2020 Pool Bouys 26,400 31,200 24,000 81,600 Total Sales Revenue Budget 93,600 104,000 38,400 $ 286,000 Cash 9,000 Trade Creditors 8,520 Accounts Rble 85,210 Shareholder's Equity 150,000 Schedule 2 Production Budget (Units) RM Resin Inventory 5040 2,268 Retained Earnings 80,548 July Aug Sept Total Oct* RM Hardener Inventory 3772 Kickboards G Kickboard Inventory 720 8.496 Budget Sales 2,400 2.600 2,300 7,300 2,900 FG Pool Bouy Inventory 330 2954 Target Ending Inventory 780 690 870 2.340 900 Plant & Equipment 125,000 3,290 3,17 3,800 $ 239,068 $ 239,068 Units Required 3,180 9,640 Beginning Inventory 720 780 690 870 Total Production Budget 2,460 2,510 2,480 7,450 2,930 Sales Ap May June July Aug Sept Oct Nov SP $ % Change Kickboards 1,900 2.000 2,700 2.400 2,600 2.300 2,900 3,000 $28.00 Pool Bouys Pool Bouys 1,300 1,400 1,300 1,100 1,300 1,000 900 1,000 $24.00 Budget Sales 1,100 1,300 1,000 3,400 900 Target Ending Inventory 390 300 270 960 300 Kickboards Units Required 1,490 1,600 1,270 4,360 1,200 Pool Bouys Beginning Inventory 330 390 300 270 Total Production Budget 1,160 1,210 970 3,340 930 Manufacturing Costs "Required for Materials Budget Materials: Cost $ Kickboard $ Pool Bouy: $ Resin 0.90 Direct Materials Purchases Budget $0.45 3 1.35 Schedule 3 Total Hardener $0.70 3.50 2 July Aug Sept 1.40 Resin (Litres) Labour: Budget Use in Production 8,400 8,650 7,870 24,920 Mixing $20 0.3 6.00 0.2 4.00 5,190 4,722 5,190 15.102 Moulding $30 0.2 6.00 0.2 6.00 Target Ending Inventory Units Required 13,590 3,372 13,060 40,022 Factory O/H Depn Beginning Inventory 5,040 5,190 4,722 Mixing $1 p/h $7 $7 p/dlh 2.10 $7 p/dlh 1.40 Total Litres Purchased 8,550 8,182 8,338 25,070 Moulding $1 p/h $11 $11 p/dlh 2.20 $11 p/dlh 2.20 Cost Per Unit 20.70 16.35 Resin Purchase Costs 3,848 3,682 3,752 $ 11,282 Inventory Policy (% of next month) Hardener (Litres) Finished Goods 30% Budget Use in Production 14,620 14,970 14,340 43,930 Raw Materials 60% Target Ending Inventory 8,982 8,604 9,906 27,492 Units Required 3,602 23.574 24,246 71,422 Selling & Administration Beginning Inventory 8,772 8,982 8,604 Fixed (per month) 24,000 Promotion Total Litres Purchased 14,830 14,592 15.642 45,064 Depreciation (per month 1,000 Sales Commission 7% Hardener Purchase Costs 10,381 10,214 10,949 $ 31,545 Collection Policy Total DM Purchases Budget 14,229 13,896 14,702 $ 42,826 Month of Sale 15% July Aug Month after Sale 45% Schedule 4 Direct Labour Budget Second month after Sale 40% July Aug Sept Total Discount NoJuly Aug Sept Total Discount No Mixing Department Payment Direct Labour Hours 970 995 938 2.903 Month of Purchase 60% Direct Labour Costs 19400 19900 18760 $ 58,060 Month after Purchase 40% Moulding Department Direct Labour Hours 724 744 690 2.158 Finance Direct Labour Costs 21,720 22,320 20,700 $ 64,740 Margin of Safety $9,000 Interest on loan (per month) 1% Total Direct Labour Budget $ 41,120 $ 42,220 $ 39,460 $ 122,800 Schedule 5 Factory Overhead Budget Options Yes/No July Aug Sept Total a Discount Mixing Department 6,790 6,965 6,566 20,321 b Change Pric no Moulding Department 7,964 8,184 7,590 23,738 c Suppliers no d MOS no Total Factory Overhead Budget $ 14,754 $ 15,149 $ 14,156 $ 44,059 e Sales no Promotion no Schedule 6 Selling, Admin & Finance Budget g Layout no July Aug Sept Total Fixed 25,000 25,000 25,000 75,000 Commission 6,552 7,280 6,188 20,020 Discount Interest Total Budget $ 31,552 $ 32,280 $ 31,188 $ 95,020 *95,102 was answer Schedule 7 Cash Budget AR Balance* July Aug Sept Total July Aug Sept Beginning Balance 9,000 9,000 9,000 85,210 Collections: Current month's sales 14,040 15,600 13,260 42,900 Previous month's sales 48,060 42.120 46.800 136,980 Second previous month's sales 35,840 42,720 37,440 116,000 Total Collections 97,940 100,440 97,500 $ 295,880 *Needed for cash collections Cash available for needs Payments: Current month's purchases AP plus what? 8,520 Acc. Pay? 8,520 Month following purchase Direct Materials month of/after? 8,537 14,029 14,379 Direct Labour 41,120 42,220 39,460 Factory Overhead 14,754 15,149 14,156 Selling & Admin Interest Interest? Total Payments Cash Excess Loan draw down 4,387 Amount available for Loan repayment Loan repaymentCash Excess Loan draw down 4,387 Amount available for Loan repayment Loan repayment Balance Loan Balance 3,486 Schedule 8 Income Statement July Aug Sept Total Sales Less: Cost of Goods Sold Beginning inventory Cost of Goods Manufactured Cost of Goods Available for Sale Less: Ending Inventory Cost of Goods Sold Gross Profit Less: Operating Expenses Selling & Admin Finance Net Profit -$ 6,953 Schedule 9 Balance Sheet July Aug Sept Cash 9,000 9,000 9,000 Accounts Rble RM Resin Inventory RM Hardener Inventory FG Kickboard Inventory FG Pool Bouy Inventory Plant & Equipment Total Assets $ 232,962 Accounts Payable Loan Total Liabilities Capital Retained Earnings Liabilities and Owners Equity $ 232,962