.. Vodacom VoWiFi 18:34 Done HMAC 330-1-Jul-Dec2021-S... QUESTION 1 (30 marks) Read the following scenario and answer the question that follows: Duzzle Dorff is in the process of starting a small business by making educational wooden puzzles for kids. He asked you to assist him with some financial planning for his new venture. Duzzle plans to sell his puzzles for R100 per unit and his normal production capacity will be 750 units per month. His estimated monthly cost breakdown based on the normal production capacity is: Amounts used per unit Price 200 ml Product Material - Glue Material - Paint Material - Plywood 400 ml 800 mm R20.00 per litre R15.00 per litre R10.00 per metre R8.00 per hour R3.00 per hour R5.35 per hour Labour 2h 2h Overhead - Variable Overhead - Fixed 2h Additional Costs Sales Commission 10% of sales R18 975 Sales and Administration cost - Fixed Duzzle also calculated the break-even point as 540 units. Duzzle is concerned that he will need a bank overdraft to finance his working capital requirements during the start-up of his business and supplied you with the following notes on factors most likely to determine his business' cash flow: 2 HMAC330-1-Jul-Dec 2021-SA2-TM-V3-16042021 1. Production and sales volumes for his first four months of business: . Vodacom VoWiFi 18:34 Done HMAC330-1-Jul-Dec2021-S... 1. Production and sales volumes for his first four months of business: Jun 2021: 50% of normal production capacity Break-even units Jul 2021: Aug 2021 Sept 2021 650 units 100% of normal production capacity He estimates that only 25% of sales will be on a cash basis since all customers will be wholesalers. Seventy-five percent of his proposed customers indicated that they would like to utilise a one month credit line. Bad debts, if any, would be negligible. 2. Material purchases Glue will be purchased in the month prior to required use. His suppliers granted him one month credit for payments. Duzzle will utilise this credit to facilitate his cash flow. Paint is in short supply and therefore Duzzle plans to buy his entire paint requirement for the first four months in June. Supply only takes place on a cash-order basis. Plywood will be a stocked item with a minimum of 300 meters in stock at all times. Due to the supplier's delivery charges being high, Plywood would be bought on a two-month cycle: requirements for June and July would therefore be acquired at the beginning of June and for August and September acquired at the beginning of August. 3. All other costs are paid immediately when incurred, except for Sales Commission which is paid one month later. 4. A refundable rental deposit equal to one month's rent of R2 000 is payable at the beginning of June 2021. HMAC330-1-Jul-Dec2021-SA2-TM-V3-16042021 ..1 Vodacom VoWiFi 18:34 Done HMAC 330-1-Jul-Dec2021-S... 5. Production equipment: wood-working and packing machines were ordered already and will be delivered and installed at the beginning of June. The cost of the equipment amounts to R65 000 of which R10 000 was payable when ordered. The balance is due when the equipment is delivered. An installation cost of R5 000 is payable when the equipment is delivered and installed. 6. Duzzle has R140 000 capital (cash) that he will provide the entity with at the beginning of June 2021. This (as well as the R10 000 deposit for production equipment) will be regarded as a personal loan to the company, earning interest of 12% per annum, payable to Duzzle on a monthly basis (from June 2021 onwards). Capital will be paid back to Duzzle from excess cash available in the company's current account. 7. A current account will be opened in the entity's name to operate from. Duzzle decided that a minimum balance of R50 000 should be built up and maintained in this account before any capital payments towards his personal loan could be made. Required: Using the Excel Template provided on ColCampus, prepare a cash budget for Duzzle Dorff for the period June - September 2021, broken down per month. In your spreadsheet, ensure that you: Only enter formulas / amounts in cells marked in yellow. Column H indicates whether you need to fill in an amount or a formula. Column I indicates the mark allocation for the amount or a formula entered. Use formulas as far as possible to assist you with calculations. In addition, you must use the following specific formulas: > Use Excel's 'sum' function to calculate the totals. Use Excel's 'cell reference' function to calculate the cash flow statement. Perform research to gain an understanding of the 'sum', 'cell reference and IF functions in Excel if you are not familiar with these. (30 marks) HMAC330-1-Jul-Dec 2021-SA2-TM-V3-16042021