Cash Flow Forecast for a Project

Cash Flow Forecast for a Project

A company is undertaking a project over a period of 18 months and needs to prepare a cash flow forecast. Below is a breakdown of the expected customer payments and costs associated with the project. The company will also face some risks and financial challenges during the process, which will be addressed in the cash flow analysis.

Customer Payments:

• Month 0: Deposit of £100k
• Month 5: £50k payment for design agreement
• Month 16: £300k payment after factory trials
• Month 18: £220k upon project completion

All payments are made on a 30-day term.

Project Costs:

• Month 6: £80k for design contractor and £5k monthly overhead costs
• Month 8: £120k for materials and £5k for overhead costs
• Months 8-15: £20k per month for factory labor and £5k per month for overheads
• Months 16-18: £30k per month for installation costs and £5k per month for overheads
• Troubleshooting costs of £13k per month will be incurred from Month 19 to Month 22 due to issues during the factory trials.

1. Month-by-Month Cash Flow and Expected Profits:

The month-by-month cash flow breakdown is as follows:

Month	Cash In (£k)	Reason for Cash In	Cash Out (£k)	Reason for Cash Out	Monthly Net Cash (£k)	Cumulative Cash (£k)
0	100	Deposit (start of project)	0	No costs yet	+100	+100
1	0	No cash inflow	5	Overhead costs (£5k)	-5	+95
2	0	No cash inflow	5	Overhead costs (£5k)	-5	+90
3	0	No cash inflow	5	Overhead costs (£5k)	-5	+85
4	0	No cash inflow	5	Overhead costs (£5k)	-5	+80
5	50	Design agreed (30 days after agreement)	5	Overhead costs (£5k)	+45	+125
6	0	No cash inflow	85	Design contractor (£80k) + Overhead costs (£5k)	-85	+40
7	0	No cash inflow	5	Overhead costs (£5k)	-5	+35
8	0	No cash inflow	145	Materials (£120k) + Overhead costs (£5k) + Factory labor (£20k)	-145	-110
9	0	No cash inflow	25	Factory labor (£20k) + Overhead costs (£5k)	-25	-135
10	0	No cash inflow	25	Factory labor (£20k) + Overhead costs (£5k)	-25	-160
11	0	No cash inflow	25	Factory labor (£20k) + Overhead costs (£5k)	-25	-185
12	0	No cash inflow	25	Factory labor (£20k) + Overhead costs (£5k)	-25	-210
13	0	No cash inflow	25	Factory labor (£20k) + Overhead costs (£5k)	-25	-235
14	0	No cash inflow	25	Factory labor (£20k) + Overhead costs (£5k)	-25	-260
15	0	No cash inflow	25	Factory labor (£20k) + Overhead costs (£5k)	-25	-285
16	300	Factory trials payment (delayed)	35	Installation and overhead costs (£30k + £5k)	+265	-320
17	0	No cash inflow	35	Installation and overhead costs (£30k + £5k)	-35	-355
18	300	Completion payment (delayed)	35	Installation costs (£30k) + Overhead costs (£5k)	+265	-90
19	0	No cash inflow	13	Troubleshooting costs (£13k)	-13	-103
20	0	No cash inflow	13	Troubleshooting costs (£13k)	-13	-116
21	0	No cash inflow	13	Troubleshooting costs (£13k)	-13	-129
22	220	Final payment (delayed)	13	Troubleshooting costs (£13k)	+207	+78

2. Borrowing Required to Fund the Project:

Based on the cash flow forecast, borrowing is required between months 8 and 17 inclusive. The peak borrowing occurs in Month 15, where the company needs to borrow £285k to cover costs during a period of negative cash flow.

3. Impact of Borrowing Costs:

If the cost of borrowing is 1% per month, we can calculate the impact on the project cost. By summing the negative cash flow amounts, we determine that the company borrows a total of £1660k over the project period.

• Cost of borrowing: £1660k * 1% = £16.6k
• This additional cost reduces the overall profit from the project, which initially was projected to be £130k. After accounting for the borrowing costs, the profit is reduced to approximately £114k.

Alternatively, you can calculate the cost of borrowing month by month by adding 1% of the negative balance each month and carrying it forward to the next.

4. Impact of Delayed Customer Payments and Troubleshooting Costs on Profit:

The project faces a delay in customer payments:

• Factory trials payment is delayed until Month 18.
• Final payment is delayed until Month 22.

In addition to the delay in payments, the company faces £13k per month troubleshooting costs from Month 19 to Month 22.

• As a result, the cumulative cash flow impact is considerable, and the final profit is reduced.
• With the additional troubleshooting costs, the project profit reduces to approximately £78k.
• After further adjustments due to delayed payments and troubleshooting, the overall impact on profit brings the final profit to around £51k, after adding up the cost of borrowing from earlier months.

---

Conclusion:

Managing the cash flow of a project is essential to understanding its financial health. In this case, despite the delayed customer payments and troubleshooting costs, the company still manages to complete the project with a positive cash balance and a reasonable profit, but the project’s financial challenges were not insignificant.

This exercise highlights the importance of accurate forecasting, early risk management, and contingency planning to mitigate potential financial setbacks throughout the project lifecycle.

Pooja Mattapalli