. . . 53 X QUESTION 1 [32 marks] by using your Excel skills and making use of the provided data. Once done hide 1.1) (13 marks) column B, C and D. Give column G the heading "Net cash movement" and calculate the net cash Matt is in the process of preparing a cash budget for the 2022 year. The accounting movement for each month. department provided the below data. 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 Year Month | Day Collections Payments the word "High" in the case of an above average month and remain blank if the month 2022 31 R147 333 030 R324 375 561 is not above average. 2022 2 28 R380 992 500 R351 831 308 Prepare a bar/column chart to illustrate the month-to-month collections vs payments. 2022 3 31 R333 432 040 R284 689 100 Make sure to add a heading to your chart. as well as axis titles and a legend 2022 30 R504 317 250 R436 574 925 Present your "Cash Collections and Payments Analysis for 2022" in both normal and 2022 5 31 R430 011 000 R382 982 423 formula view. 2022 30 R317 263 208 R273 376 058 Mark allocation 2022 R398 250 000 R348 070 400 Normal view (6 marks) 2022 8 R599 838 840 R432 588 000 Char 9 30 R708 000 000 R454 297 050 (3 marks) 2022 2022 10 31 R499 554 180 R309 307 500 Formula view (4 marks) 2022 11 30 R487 278 230 R331 904 205 1.2) (9 marks) 2022 12 31 R610 850 000 R378 921 059 As the Financial Manager of Edge lord, calculate an updated accepted Rate of Return for the company. as the current rate is outdated REQUIRED: The following is information received from the Finance Department: Copy the above data and create a Cash Collection and Payment Analysis for 2022, in Excel Source Value Before-tax cost 8 1 1 1 1 7 . . 1 10 Use the heading "Cash Collections and Payments Analysis for 2022" and format and Ordinary shares R 19 912 500 12.259% align the heading across all your columns. Preference shares R 8 850 000 13.159% Format text to Arial, size 12. Long term borrowings R 75 568 436 10.009% . 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. The marginal tax rate is 28%. 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 . 9 . 238 HPFM440-1-Jul-Dec2022-SA1-GC-V4-26072022 239 HPFM440-1-Jul-Dec2022-SA1-GC-V4-26072022 Type here to search O Fi X W W 18'C ^ DO 0 BA(). 9:05 AM 10/30/2022HPFM440-1-Jul-Dec2022-SA1-GC-V4-26072022 (3) [Compatibility Mode] - Word (Product Activation Failed) ? 0 - 8 3 X FILE HOME INSERT DESIGN PAGE LAYOUT REFERENCES MAILINGS REVIEW VIEW Microsoft account Outline Ruler One Page [ View Side by Side Draft Gridlines BMultiple Pages EG Synchronous Scrolling Read Print Web Zoom 100% Arrange Split Switch Macros Mode Layout Layout Navigation Pane Page Width Window All GO Reset Window Position Windows Views Show Zoom Window Macros REQUIRED: In Excel, create a table using the provided information and calculate the WACO for Edge Incl. Present your answer in: Normal view (5 marks) Formula view (4 marks) 1.3 (10 marks) The following are investment opportunities with an initial investment outlay of R380 400.00: Interest Year ( Year 1 Year 2 Year ? Year 4 rate Investment A 9.729 389 400 Investment B 11.53% 389 400 38 500 38 500 88 500 38 500 Investment C 9.239% 380 400 9 56 18 500 28 125 13 275 Investment D 8.29% 389 400 92 250 88 500 28 125 61 500 REQUIRED: Prepare in an Excel workbook, the Future Value of each of the investment opportunities by making use of Excel formulas. Give your answer in: 1.3.1 Normal view (4 marks) 1.3.2 Formula view (4 marks) 1.3.3 Based on the calculations, which will be the best investment opportunity and why? (2 marks) PAGE 7 OF 12 2101 WORDS + 64% 9:07 AM Type here to search X W W 10/30/2022