QUESTION 3 As the financial manager of Gabex Solutions, prepare the Cash flow forecast for the...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 January February March April May June Sales R 17 598 225 R 18 661 995 R 12 844 890 R 14 719 000 R 17 961 075 R 15 021 990 Cost of sales R 10 994 355 R 12 028 478 R 9 688 095 o 65% cash sales and o 35% credit sales, collected: R 11 102 800 R 18 267 728 R 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: [42 marks] 20% collected 30 days after sales The monthly cost of sales: . 10% collected 60 days after sales 4% collected 90 days after sales 1% written off as irrecoverable 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: A Gabex Solutions Cash Budget - Worksheet Area For the Period April to June April B May June 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: D Gabex Solutions Cash Budget For the Period April to June April May G H June Add a notes area to the sheets where necessary, to reference relevant formulas to. Marks will be allocated as follow: Normal view - Cash budget Normal view - Worksheet area Formula view - Cash budget Formula view - Worksheet area (hide columns C to E) 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. (11 marks) (10 marks) (11 marks) (10 marks) 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 January February March April May June Sales R 17 598 225 R 18 661 995 R 12 844 890 R 14 719 000 R 17 961 075 R 15 021 990 Cost of sales R 10 994 355 R 12 028 478 R 9 688 095 o 65% cash sales and o 35% credit sales, collected: R 11 102 800 R 18 267 728 R 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: [42 marks] 20% collected 30 days after sales The monthly cost of sales: . 10% collected 60 days after sales 4% collected 90 days after sales 1% written off as irrecoverable 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: A Gabex Solutions Cash Budget - Worksheet Area For the Period April to June April B May June 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: D Gabex Solutions Cash Budget For the Period April to June April May G H June Add a notes area to the sheets where necessary, to reference relevant formulas to. Marks will be allocated as follow: Normal view - Cash budget Normal view - Worksheet area Formula view - Cash budget Formula view - Worksheet area (hide columns C to E) 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. (11 marks) (10 marks) (11 marks) (10 marks)
Expert Answer:
Answer rating: 100% (QA)
Gabex Solutions Cash Budget April to June 2023 Cash Budget Worksheet Area Month Collections from Debtors Payments to Suppliers Net Cash Flow April SUM... View the full answer
Related Book For
Accounting Principles
ISBN: 978-1119048473
7th Canadian Edition Volume 2
Authors: Jerry J. Weygandt, Donald E. Kieso, Paul D. Kimmel, Barbara Trenholm, Valerie Warren, Lori Novak
Posted Date:
Students also viewed these accounting questions
-
Assume that the first six months of 2021 have passed, and the Board requested that their accountant prepare an income statement for the first six months of 2021 showing how actual performance...
-
Fogerty Company makes two products-titanium Hubs and Sprockets. Data regarding the two products follow: Direct Labor-Hours per Unit Annual Production Hubs Sprackets 20,000 units 48,000 units e.60...
-
An unknown compound A of molecular formula of C10H180 reacts with H2S04 and heat to form two compounds (B and C) of molecular formula C10H16. B and C both react with H2 over Pd/C to form decalin....
-
Compare and contrast intensive, selective, and exclusive distribution. Which channel design decision does this involve?
-
Build a new dashboard ( Tableau ) or page ( Power BI ) called Financial that includes the following: Create a new workbook, connect to 1 0 - 2 P 2 P Data.xlsx , and import all eight tables. Double...
-
The following table lists values measured for 60 consecutive eruptions of the geyser Old Faithful in Yellowstone National Park. They are the duration of the eruption (x1), the duration of the dormant...
-
You are considering buying stock A. If the economy grows rapidly, you may earn 30 percent on the investment, while a declining economy could result in a 20 percent loss. Slow economic growth may...
-
You just purchased a share of SPCC for $ 1 0 3 . You expect to receive a dividend of $ 6 in one year. If you expect the price after the dividend is paid to be $ 1 0 9 , what total return will you...
-
Tesla Motors, Inc. is in the business of developing, manufacturing, and selling technology for high-performance electric automotive and powertrain components. Hoping to develop a greater worldwide...
-
Parents deposit $8,000 into a savings account at the end of each year for 22 years to help their child pay for college. The savings account pays 9% interest per year, compounded monthly. The child...
-
A subset A of M is said to be nowhere dense if int(A) = 0. Prove that {x} is nowhere dense if and only if x is not an isolated point of M! Recall that a point is isolated from a set if there exists...
-
(a) Consider a uniformly charged thin-walled right circular cylindrical shell having total charge Q, radius R, and length . Determine the electric field at a point a distance d from the right side of...
-
The entire LAW 101 class agreed to a hangout at the movies. Armen fronted all of the money for the tickets. Everyone agreed to repay him via Venmo 24 hours before the day of the movie. Everyone but...
-
What is the slope of the line that passes through the points (-8,2) and (-11,3) ? Write your answer in simplest form.
-
Zboy Corp is liquidating. Upon liquidation, it will distribute the assets to its two shareholders, Lee (40% shareholder) and Kelly (60% shareholder). The assets have a FMV of $500,000 and an adjusted...
-
Two electric utilities have different marginal abatement costs(MCA) for reducing sulfur dioxide emissions. They can each reducethese emissions with the following marginal (MCA) and total...
-
All of the following assets can be depreciated, except: (a) A bulldozer (b) A copper mine (c) A surgical robot (d) A conveyor belt
-
The trial balances of two proprietorships on January 1, 2017, follow: Domic and Dasilva decide to form a partnership on January 1 and agree noncash assets that they are each contributing: All of the...
-
Randle Inc. issues $300,000 of 10-year, 8% bonds at 98. Prepare the journal entry to record the sale of these bonds on March 1, 2017.
-
During 2016, Financial Holdings, a public company, purchased equity securities for trading purposes. At December 31, 2016, the securities for Financial Holdings were as follows: The following...
-
Find the flexibility and stiffness influence coefficients of the system shown in Fig. 6.29. Also, derive the equations of motion of the system. k1 k3 k4 000 /711 000 m2 000 my 000 FIGURE 6.29...
-
Find the flexibility and stiffness influence coefficients of the torsional system shown in Fig. 6.28. Also write the equations of motion of the system. (GJ)1 01 Compressor (GJ)2 (Jan) Turbine (142)...
-
Derive the stiffness matrix of the system shown in Fig. 5.39. mo e(t) ellee k1 k2 lllll m x(t) FIGURE 5.39 Mass hanging from a pulley.
Study smarter with the SolutionInn App