Vodacom VoWiFi 18:34 Done HMAC330-1-Jul-Dec2021-S... QUESTION 1 Read the following scenario and answer the question that follows: 2 (30 marks) 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: Product Material - Glue Amounts used per unit Price 200 ml R20.00 per litre Material - Paint 400 ml R15.00 per litre Material - Plywood Labour 800 mm R10.00 per metre 2h R8.00 per hour Overhead - Variable 2h R3.00 per hour Overhead - Fixed 2h R5.35 per hour Additional Costs Sales Commission 10% of sales Sales and Administration cost - Fixed Duzzle also calculated the break-even point as 540 units. R18 975 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: HMAC330-1-Jul-Dec2021-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... m 3 1. Production and sales volumes for his first four months of business: Jun 2021: Jul 2021: Aug 2021: Sept 2021: 50% of normal production capacity Break-even units 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 Vodacom VoWiFi 18:34 Done HMAC330-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) 4 HMAC330-1-Jul-Dec2021-SA2-TM-V3-16042021 Units sold Cash receipts Cash received from debtors Cash sales Total cash receipts Cash payments Payment to Creditors Labour costs Overhead cost - Variable Overhead cost - Fixed Sales & Admin - Variable commision Sales & Admin - Fixed Rent deposit Production equipment Installation costs Interest paid (nr 3) Total cash payments Net cash flow Opening balance Closing balance Payment to Creditors Creditors for Glue Creditors/Cash from Paint Creditors/Cash from Plywood June July August September June July August September Schedule for Purchases of Glue June July August September (Creditors for Glue) Production units Used per unit Total used (ml) Total used (1) Price per litre Purchases Schedule for Purchases of Paint June July August September TOTAL (Creditors / Cash for Paint) Production units Used per unit Total used (ml) Total used (1) Price per litre Purchases Schedule for Purchases of Plywood (Creditors / Cash for Plywood) Production units Used per unit Total used (minimum per mm) Total used (minimum per m) Inventory (minimum per m) Inventory (minimum per m) Price per metre Purchases Payments June July August September