Currently I have an excel problem that I have not been able to solve. Would you be able to help me solve this problem
Attached is a file.
Desired state:
The objective is to able to have the revenue forecast by client once a month.
The FFS will be factored based upon the initial start date
A new FFS Start date will override the initial start date and replace the using the new FFS
Quarterly revenue summary by client
Annual revenue summary by Client
Weekly cash forecast tab
- Column A Clients
- Column B FFS Start date (=IF(Factors!F36=0,Factors!C36,Factors!F36))
- 1st, 2nd , 3rd 2018 quarters are complete hard coded.
- 4th 2018 quarter Column At actual
- All others are forecast this formula assumes three week at zero then next week would be FFS
- Problem with this formula is that we have built in Quarterly revenue and that throws off the formula
- =IF(SUM(AQ19:AR19)+SUM(AT19)>0,IF(Factors!$F48=0,IF(Factors!$F48<=$C2,Factors!$B48,Factors!E48),0),Factors!$B4
-
- Second problem does not account for 5th week in quarter
Forecast tab
- Column A Clients
- Column B FFS
- Start Date
- Column D blank
- Column E New FFS
- New start Date
Hi Paul,
Have you considered to use a Pivot Table instead?
Just took a glimpse of your file and I do think you should consider to rearrange your data so that you can build up your wanted views with Pivot Tables.
Br,
Anders
Hi Paul,
I think Anders is right. Rearrange your data so it's in a tabular format. Then you can leverage Power Query and Power Pivot to write the formulas automatically. Where you need calculated columns, use Power Query, where you need calculations based on totals/aggregations, use Power Pivot.
Have you finished the Power Query and Power Pivot courses? If not, I recommend you do as this will give you an understanding of what is available to you.
If you get stuck implementing something from the courses into your forecast file, then we're here to help. At the moment I don't think there's much point in fixing the quarterly revenue formula as it's probably best done in Power Query or Power Pivot.
Mynda
