QUESTION 1 [22 marks]

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

YearMonthDayCollectionsPayments
2023131R120 813 085R321 131 805
2023228R449 571 150R288 501 673
2023331R393 449 807R335 909 538
2023430R413 540 145R357 991 439
2023531R507 412 980R451 919 259
2023630R374 370 585R322 583 748
2023731R469 935 000R283 777 728
2023831R593 840 016R510 453 840
2023930R580 560 000R536 070 519
20231031R589 473 932R364 982 850
20231130R551 388 311R391 646 962
20231231R500 733 000R444 766 850

Required:

• Copy the above data and create a Cash Collection and Payment Analysis for 2023, in Excel.
• 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 12.
• Format the heading of each column by making it bold and fill the cells with light grey. 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 month.
• Give column H the heading “Above average months”. Use a logical IF function to calculate which months had above average cash surpluses. 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 legend.
• Present your “Cash Collections and Payments Analysis for 2023” in both normal and formula view.

As the financial manager of Narex Ltd., calculate an updated accepted Rate of Return for the company. (9 marks)
The following is information received from the Finance Department:

SourceValueBefore-tax cost
Preference sharesR 10 443 00014.46%
Common sharesR 23 496 75015.52%
Mortgage LoanR 61 966 11810.00%

The marginal tax rate is 28%.

Required:

In Excel, create a table using the provided information and calculate the WACC for Narex Ltd.
Present your answer in:

• Normal view (4 marks)
• Formula view (5 marks)

QUESTION 2 [36 marks]

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

Details2023 (R)
Total revenue2 077 095
Cost of sales1 958 063
Selling expenses219 511
Depreciation expense23 497
Interest expense39 162
Outstanding shares (50 000 @ R 2.50each)125 000
Cash31 329
Account receivable321 255
Inventory344 619
Plant & equipment1 801 347
Accumulated depreciation391 465
Average tax rate28%

Additional information:

Accounts payable, notes payable, long-term debt, common stock, additional paid-in- capital, and retained earnings represented 12%, 2.5%. 18%, 40.5%, 9% and 18% of total assets, respectively.

Year 2024, Forecast Information:

– Jotec (Pty) Ltd. expects a 21% increase in total revenue, while cost of goods sold and selling and expenses are expected to remain at the same proportion of total revenue as in 2023.

– Both total plant and equipment and depreciation expense will increase by 18%. Similarly, long-term debt and interest expense is projected to increase by 15%, but the tax rate and number of shares outstanding will remain constant.

– Additionally accounts receivable, inventory, accounts payable, and notes payable are expected to increase 15%, while common stock and paid-in-capital will increase by 18%.

– The dividend policy in 2024 will be based on a dividend payout ratio of 40%.

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.

Present your answer in:
• Normal view (13 marks)
• Formula view (23 marks)

QUESTION 3 [42 marks]

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:

MonthSalesCost of sales
JanuaryR 17 598 225R 10 994 355
FebruaryR 18 661 995R 12 028 478
MarchR 12 844 890R 9 688 095
AprilR 14 719 000R 11 102 800
MayR 17 961 075R 18 267 728
JuneR 15 021 990R 11 756 340

Additional information:

• The closing bank balance as of 31 March 2023 amounted to R7 049 025.
• The monthly minimum cash balance requirement is set at R3 132 900.
• The monthly sales are:
o 65% cash sales and
o 35% credit sales, collected:
 20% collected 30 days after sales
 10% collected 60 days after sales
 4% collected 90 days after sales
 1% written off as irrecoverable
• The monthly cost of sales:
o 40% wages which gets paid every month on the last day of the month and
o 60% relate to inventory purchases, paid:
 70% paid 30 days after purchase

 30% paid 60 days after purchase
• Sales commission is paid at 13% of monthly sales; this is paid the month after the sales occurred.
• Other fixed operating and administration expenses get paid in cash monthly and is estimated to be R3 132 900 month.
• A provisional tax payment of R1 044 300 will be due at the end of June.
• The company’s dividend policy is to pay a dividend in the second month of each quarter. A dividend will only be paid if the cash balance at the end of the month prior to the month of payout is in excess of double the monthly minimum cash balance requirement. In such a case, 25% of the available cash at the end of the month prior to the payout, after considering the current month’s costs, will be paid out as a dividend.

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 sheet. 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 Budget. Make use of the following format:

• Add a notes area to the sheets where necessary, to reference relevant formulas to.
• Make use of an IF function to Calculate the potential Dividend payment in the dividend payment month.
• Make use of an IF function to Calculate the potential Borrowing for each month.

Marks will be allocated as follow:

• Normal view – Cash budget (11 marks)
• Normal view – Worksheet area (10 marks)
• Formula view – Cash budget (11 marks)
• Formula view – Worksheet area (hide columns C to E) (10 marks)

Get answers on finance for corporate action questions

Answer 1: The creation of Cash Collection and Payment Analysis for 2023 is performed as follows:

answer

Get completed answers on questions from corporate finance from the best accounting assignment help South Africa experts of Student Life Saviour.