Assignment Task

Question 1

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

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

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.

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:

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