Apr 20, 2024

Question 1

1. Aphiwe is in the process of preparing a cash budget for the 2023 year. The accounting department provided the below data.

 Year Month Day Collections Payments 2023 1 31 R120 813 085 R321 131 805 2023 2 28 R449 571 150 R288 501 673 2023 3 31 R393 449 807 R335 909 538 2023 4 30 R413 540 145 R357 991 439 2023 5 31 R507 412 980 R451 919 259 2023 6 30 R374 370 585 R322 583 748 2023 7 31 R469 935 000 R283 777 728 2023 8 31 R593 840 016 R510 453 840 2023 9 30 R580 560 000 R536 070 519 2023 10 31 R589 473 932 R364 982 850 2023 11 30 R551 388 311 R391 646 962 2023 12 31 R500 733 000 R444 766 850

Required

• Copy the above data and create a Cash Collection and Payment Analysis for 2023, in
• Use the heading “Cash Collections and Payments Analysis for 2023” and format and align the heading across all your columns.
• Format text to Arial, size
• Format the heading of each column by making it bold and fill the cells with light Add borders to each cell containing data, except for the headings.
• Add a column in column A (move your data) and add the heading “Date”. This column should contain the date of each line in day-month-year format – this should be done by using your Excel skills and making use of the provided data. Once done hide column B, C and D.
• Give column G the heading “Net cash movement” and calculate the net cash movement for each
• Give column H the heading “Above average months”. Use a logical IF function to calculate which months had above average cash The formula should return the word “High” in the case of an above average month and remain blank if the month is not above average.
• Prepare a bar/column chart to illustrate the month-to-month collections vs payments. Make sure to add a heading to your chart, as well as axis titles and a
• Present your “Cash Collections and Payments Analysis for 2023” in both normal and formula

1.1. As the financial manager of Narex Ltd., calculate an updated accepted Rate of Return for the company.

The following is information received from the Finance Department

 Source Value Before-tax cost Preference shares R 10 443 000 14.46% Common shares R 23 496 750 15.52% Mortgage Loan R 61 966 118 10.00%

Required

In Excel, create a table using the provided information and calculate the WACC for Narex Ltd

Question 2

Jotec (Pty)Ltd. a Tech company is planning to prepare forecasted financial statements for year 2024 based on year 2023 accounting data.

 Details 2023 (R) Total revenue 2 077 095 Cost of sales 1 958 063 Selling expenses 219 511 Depreciation expense 23 497 Interest expense 39 162 Outstanding shares (50 000 @ R 2.50each) 125 000 Cash 31 329 Account receivable 321 255 Inventory 344 619 Plant & equipment 1 801 347 Accumulated depreciation 391 465 Average tax rate 28%

Required

Using these projections, prepare the forecasted 2024 Statement for comprehensive income, Statement of Financial Position, and Statement of Cashflows for Jotec (Pty) Ltd. Each statement should be on a separate worksheet.

Question 3

As the financial manager of Gabex Solutions, prepare the Cash flow forecast for the second quarter of 2023, April – July 2023.

The accounting department provided the following actual and forecasted sales and cost of sales figures for the required period:

 Month Sales Cost of sales January R 17 598 225 R 10 994 355 February R 18 661 995 R 12 028 478 March R 12 844 890 R 9 688 095 April R 14 719 000 R 11 102 800 May R 17 961 075 R 18 267 728 June R 15 021 990 R 11 756 340

Required

Using the provided information above, prepare a detailed Cash Budget in Excel for Gabex Solutions for the period April to June 2023.

The Cash Budget should include the following:

• A worksheet area on a separate This area should be used to calculate your cash collections and payment from debtors and supplies for each month. Make use of the following format:
• After completing the worksheet area, prepare the actual Cash budget on a separate Excel sheet and make use of the necessary calculations and information to complete the Cash Make use of the following format:
• Add a notes area to the sheets where necessary, to reference relevant formulas
• Make use of an IF function to Calculate the potential Dividend payment in the dividend payment
• Make use of an IF function to Calculate the potential Borrowing for each
Recent Post