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)

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:

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:

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:

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:

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