Excel Forum

Forecast Fee For service

Paul Case

New Member

Members

Power Query

Power Pivot

Power BI

Forum Posts: 1

Member Since:
August 15, 2018

Offline

November 13, 2018 - 6:58 am

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))
1^st, 2^nd , 3^rd 2018 quarters are complete hard coded.
4^th 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 5^th week in quarter

Forecast tab

Column A Clients
Column B FFS
Start Date
Column D blank
Column E New FFS
New start Date

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 930

Member Since:
December 7, 2016

Offline

November 14, 2018 - 6:23 am

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

Mynda Treacy

Admin

Forum Posts: 4772

Member Since:
July 16, 2010

Offline

November 14, 2018 - 8:48 am

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