MA – L2 – Q13 – Cash budgets and master budgets

On 31st March 202X, the bank balance standing in the books of the Department of Commerce was GHC 900,000. The Department provides you with the information below.

Month IGF GHC000 GoG releases GHC000 Donation GHC000 Wages and salaries GHC000 Goods and services GHC000 Office equipment GHC000 Advances GHC000
Jan 4,100 2,000 1,200 1,000 50
Feb 900 500 320 300 40
March 1,300 500 400 320 400 50
April 1,200 600 200 620 320 40
May 1,000 600 550 220 60
June 1,000 600 200 660 420 500 50

Relevant notes to the data:
(i) The Internally Generated Funds (IGF) is made up of 70% cash and 30% receivables. The receivables are collectible as follows: 60% in the month following the service delivery and the remaining 40% in the second month following the service delivery. The department is entitled to retention of 80% of IGF collected and the remaining 20% is payable into the National Treasury Fund in the month in which it was collected.
(ii) The department also enjoys a budget allocation from the Government of Ghana (GoG) and the government promises to make payments according to the schedule shown.
(iii) The department anticipates that it will receive some donations as scheduled above. It is expected that 30%, 40%, and 70% of donations in March, April, and June respectively will be in cash. The remaining portions are expected to come in the form of materials.
(iv) Wages and salaries will be paid at the end of each month.
(v) Goods and services are paid for one month in arrears.
(vi) The office equipment acquired in January will be paid for in the third month following the purchase and the one to be acquired in June will be paid for immediately.
(vii) The office equipment is to be depreciated at 2.5% of cost per month.
(viii) Staff of the department are granted advances under an advance scheme approved by the government. The advances will be recovered in four equal instalments beginning from the month following the month in which the advance is granted.

Required:
Prepare a cash budget for the department for the Second Quarter of 202X showing clearly the cash forecast for individual months and the total for the quarter as a whole.

Cash Forecast for the Second Quarter of 202X

April May June Total
GHC000 GHC000 GHC000 GHC000
Budgeted Cash Receipts
IGF cash (70% of IGF total) 840 700 700 2,240
IGF collection of receivables 342 372 324 1,038
GoG payments 600 600 600 1,800
Donations 80 140 220
Recovery of advances 35 37.5 45 117.5
Total Receipts 1,897 1,709.5 1,809 5,415.5
Projected Cash Payments
IGF to National Treasury Fund 236.4 214.4 204.8 655.6
Wages and salaries 620 550 660 1,830
Goods and services 400 320 220 942
Non-financial assets 1,000 500 1,500
Advances 40 60 50 150
Total Payments 2,296.4 1,144.4 1,634.8 5,077.6
Projected cash surplus (deficit) (399.4) 565.1 174.2 337.9
Cash balance at start 900.0 500.6 1,065.7 900.0
Cash balance at end 500.6 1,065.7 1,239.9 1,237.9

Workings:

  1. IGF Cash (70% of IGF total):
    • April: 1,200 × 70% = 840
    • May: 1,000 × 70% = 700
    • June: 1,000 × 70% = 700
  2. IGF Collection of Receivables:
    • April: (March: 1,300 × 30% × 60% = 234) + (Feb: 900 × 30% × 40% = 108) = 342
    • May: (April: 1,200 × 30% × 60% = 216) + (March: 1,300 × 30% × 40% = 156) = 372
    • June: (May: 1,000 × 30% × 60% = 180) + (April: 1,200 × 30% × 40% = 144) = 324
  3. Donations (Cash Portion):
    • April: 200 × 40% = 80
    • May: 0
    • June: 200 × 70% = 140
  4. Recovery of Advances:
    • Advances are recovered in four equal instalments starting the month after granting.
    • April: (Jan: 50/4 = 12.5) + (Feb: 40/4 = 10) + (March: 50/4 = 12.5) = 35
    • May: (Feb: 40/4 = 10) + (March: 50/4 = 12.5) + (April: 40/4 = 15) = 37.5
    • June: (March: 50/4 = 12.5) + (April: 40/4 = 10) + (May: 60/4 = 15) = 45
  5. IGF to National Treasury Fund:
    • April: 20% of (840 + 342) = 236.4
    • May: 20% of (700 + 372) = 214.4
    • June: 20% of (700 + 324) = 204.8
  6. Goods and Services (Paid one month in arrears):
    • April: March 400
    • May: April 320
    • June: May 220
  7. Non-financial Assets:
    • April: January equipment (1,000, paid in third month)
    • June: June equipment (500, paid immediately)