Hi I need a formula for a daily cash flow model.
1. Collection of daily sales
Sales on Monday are collected on Wednesday
Sales on Tuesday are collected on Thursday
Sales on Wednesday are collected on Friday
Thursday Friday and Saturday sales are collected on Monday
Sunday sales are collected on Tuesday
However, when there’s a federal holiday sales will be collected the following day provided that day is not a weekend day (Saturday and Sunday). I need a formula to take into account these exceptions. See attachment for an example. I would also like to be able to set collection days as a variable see cell T22 for an example. (where I could change collection days and the formula would reflect the changes) see cells W23- W29.