I am working on a cash flow forecast for a small business and want to model when invoices are sent and went those same invoices are paid on a monthly basis, but tracked weekly (will added quarterly, semi-annual and annual invoices later). I want to be able to change when the invoices are sent and how long the invoice are outstanding before paid; for sensitivity analysis. This will help forecast when bank loans will be drawn
The attached spreadsheet shows the values I want add. in Cell C35, G35, H36, M36.....
Is this possible?
Client Client A
Frequency Quarterly
Invoice Cycle Every 28
Days to Collect 35
Invoice 1 Sent Date Wednesday, January 9, 2019 Invoice Sent
Invoice 2 Sent Date Wednesday, February 6, 2019 Invoice Sent
Invoice 3 Sent Date Wednesday, March 6, 2019 Invoice Sent
Invoice 4 Sent Date Wednesday, April 3, 2019 Invoice Sent
Invoice 5 Sent Date Wednesday, May 1, 2019 Invoice Sent
Invoice 6 Sent Date Wednesday, June 5, 2019 Invoice Sent
Invoice 7 Sent Date Wednesday, July 3, 2019 Invoice Sent
Invoice 8 Sent Date Wednesday, August 7, 2019 Invoice Sent
Invoice 9 Sent Date Wednesday, September 4, 2019 Invoice Sent
Invoice 10 Sent Date Wednesday, October 2, 2019 Invoice Sent
Invoice 11 Sent Date Wednesday, November 6, 2019 Invoice Sent
Invoice 12 Sent Date Wednesday, December 4, 2019 Invoice Sent
Date Paid Invoice 1 Wednesday, February 13, 2019 3,000.00
Date Paid Invoice 2 Wednesday, March 20, 2019 3,000.00
Date Paid Invoice 3 Wednesday, April 24, 2019 3,000.00
Date Paid Invoice 4 Wednesday, May 29, 2019 3,000.00
Date Paid Invoice 5 Wednesday, July 3, 2019 3,000.00
Date Paid Invoice 6 Wednesday, August 7, 2019 3,000.00
Date Paid Invoice 7 Wednesday, September 11, 2019 3,000.00
Date Paid Invoice 8 Wednesday, October 16, 2019 3,000.00
Date Paid Invoice 9 Wednesday, November 20, 2019 3,000.00
Date Paid Invoice 10 Wednesday, December 25, 2019 3,000.00
Date Paid Invoice 11 Wednesday, January 29, 2020 3,000.00
Date Paid Invoice 12 Wednesday, March 4, 2020 3,000.00
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10
Start Tuesday, January 1, 2019 2019-01-07 2019-01-14 2019-01-21 2019-01-28 2019-02-04 2019-02-11 2019-02-18 2019-02-25 2019-03-04
End Friday, January 4, 2019 2019-01-11 2019-01-18 2019-01-25 2019-02-01 2019-02-08 2019-02-15 2019-02-22 2019-03-01 2019-03-08
Client A-Invoice Sent Date Invoice Sent Invoice Sent ????
Client A-Invoice Paid Date 3000
Bookmarks