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
-
How do you envision the use of semi-private information for targeted marketing evolving over the next decade? What does the future hold?
-
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....
-
The 420 freshmen at an engineering college take either calculus or discrete mathematics (but not both). Further, both courses are offered providing either an introduction to a CAS (computer algebra...
-
Suppose on February 1, 2023, Tesla paid $41 million for a 40% investment in Spark Ltd., an auto parts manufacturer. Assume Spark Ltd. earned net income of $6 million and paid cash dividends of $2...
-
The Miller Company purchased a new headquarters building on January 1 at a comme cost of \($40\) million. The building is expected to last 20 years, at which time its residual value is expected to be...
-
Draw a communication diagram for a student taking a course from a teacher, who is part of the faculty.
-
U.S. Generally Accepted Accounting Principles require the reporting of plant assets at on the balance sheet. at on the balance sheet. International Financial Reporting Standards allow the reporting...
-
14. A companys policy is to charge depreciation on plant and machinery at 20% per year on cost, with proportional depreciation for items purchased or sold during a year. The companys plant and...
-
4. The period of Jupiters moon lo is 1.5 x 10's and has a radius of orbit of 4.2 x 108 m calculate the mass of Jupiter using this information (1.9 x 1027 kg) 5. A lunar lander is to be placed in...
-
Based on historical information, you have developed the following information. Gross Profit Margin 0.40 SG&A / Sales 0.15 Depreciation/Net Fixed Assets 0.10 Interest Rate on Debt 0.04 Income Tax Rate...
-
Describe the mechanism of how changes in fiscal policy affect output and prices in the economy. In your response, discuss the importance of the positioning of the aggregate demand and aggregate...
-
Saturated fat is commonly found in animals while unsaturated fatis usually in plants. However, fish living in deep sea such assalmon are rich in unsaturated fat (fish oil) and tropical plantsare...
-
Divide and simplify: 2 4 8x x+4 x2+2x-8
-
Market demand is given by D(p) long-run cost function C(q) = q +9. 100 p, all firms in the market have the following a) Find the firm's supply function, qi(p). b) Find the equilibrium price, p*. c)...
-
A 6.0-kg block is released from rest 80 m above the ground. When it has fallen 60 m, what is its kinetic energy ?
-
Find the image of x = k = const under w = 1/z. Use formulas similar to those in Example 1. y| y = 0 -21 -2 -1 -1, /1 12 T -1 -1 y= -2 x =0
-
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...
-
Explain why mass transfer correlations for co-flow cannot be used for countercurrent flow.
-
Why are mass transfer coefficients from clean drops higher than mass transfer coefficients in dirty systems? What is the practical significance of this?
-
How do we determine the height of packing required for a concentrated absorber or stripper if \(\mathrm{H}_{\mathrm{G}}\) is not constant?
Study smarter with the SolutionInn App