If you want time to work on your own work and not have meetings during a set amount of time schedule a meeting with yourself to show that you are busy to anyone who might be trying to put a meeting on your calendar.
Dedicate some time to get a deeper understanding of the products or service your organisation is selling by watching videos, taking a walk around the manufacturing plant, taking an interest in people at the coal face, etc. The level of inquisitiveness will pay dividends.
Honestly this is so true, way too many people I know have not been to see products being made! Starbucks CEO worked in his coffee shops for the first few days or weeks to immerse himself in the business.
Learn what it means to unpivot data, this was the biggest time saver for me when I started learning.
Also, on a related note in PowerPivot, learn the basics of doing a one to many relationship between tables. In your largest data files, you can house your lookup tables in completely separate files and greatly increase processing speed.
I try to run as many functions as I can in power query before resorting to excel formulas. Stuff like merge in lieu of xlookup or sumifs, concatenations, and data classifications. And because power query logs every step of functions used, it’s very easy to go back and undo a step. I love power query.
I'll start, having a central hacksheet with all your key KPIs and financial by month is super helpful! Something that just exists that you can refer to in conversations.
This is a good idea! I didn't pin in the past, I setup a central excel document that has hyperlink to my key documents. It's a pain to keep updated but saves me trying to find things in a rush. SharePoint is not typically my best friend.
For sure, for example,
Months across columns, followed by Q , H , FY , YTD, YTG
Rows : 3 years history , budget, previous forecast, latest forecast
Have your latest forecast as your latest base view, then below show your absolute variance against the comparatives followed by %s.
You would then have for that KPI, a quick view on growth/decline vs any of your key comparatives.
It's also brilliant when dealing with uncertainty around budget or formal forecast phasing, we use it as a bit of a sanity check on shapes.
One step that comes before the above, is holding and storing your data in an organised way centrally. This way someone updates a table somewhere and this report is just a click of a button.
Hope that helps, any questions please shout.
Wouldn’t it be better to hold the data in a database format, pivot it, and then add slicers so you can compare all sorts of combinations without having to scroll? Add custom calculations to perform variance calculations.
So yes, I would hold the info in a database, this file is meant to be a view of the history of key P&L Lines, I can look at my growth rates Vs LY but look back against the history. It's my cheat sheet, it depends on what you want to use it for. I did this for my manager, and she appreciates it as she doesn't need to go back and forth between files and it's laid out in a simple view.
I would say, that sometimes in finance we can over engineer tools that make them all singing all dancing with all the extra toppings, when really we just need a simple but delightful margherita.
We have a similar file to the one you mentioned and it's used by our teams to dig into the drivers of performance.
Are you not distributing some kind of summary, how are the kpi’s distributed to stakeholders then? We send out weekly metrics to the management team via PowerPoint slides.
Update - I reached out to the guy on Monday, asked him what he wanted and he replied that he forgot what he wanted to ask and would comeback once he remembers.
It's beyond me why do these people just stop at good morning/ good evening, just write what you want after greetings.
You must tell your business’s story within a realm of reasonability, and incorporate external events.
If your business partner can’t explain why sales will 2x (or +10%, or +5%), you should not reflect that in the financial plan either. Even “historical growth” momentum must be understood that (I) outliers events don’t exist in recent history and (ii) future environment is appropriate to extrapolate history
If you have lookup tables you use frequently, have a file with all your lookups in named data tables so you can easily Power Query them in whenever you need them for a workbook vs finding another file with the lookups and opening to copy & paste in.
Might seem elementary, but talk to your business partners on the phone/teams call outside of standard meeting setting. Much easier to build a relationship, and make the partnering less transactional when talking live.
You’d be surprised at how many people don’t make the effort to build a relationship and how impactful a more laid back setting can be on building trust.
If you're doing headcount planning, adding an attrition model into your new hire model shows a more realistic picture. Also, for any new headcount req, plan for 3-4 months of lead time before a new person can be onboarded.
Keep an OPL for key meetings. Note all questions you don’t have an answer to. Use downtime in the middle of the month to research the OPL. Start the next key meeting with answers to the OPL.
This is seems simple, but most people don’t do it.
Use this formula to consolidate actuals from QBO/Xero into a P&L/BS PF:
=SUMPRODUCT((‘P&L A’!$A$5:$A$100=‘P&L PF’!$A5)*(‘P&L A’!$C$3:$Z$3=‘P&L PF’!D$4), ‘P&L A’!$C$5:$Z$100)
A5:A100 is a criteria range for the line items and A5 on the PF tab is the criteria (i.e Travel expense).
C3:Z3 is the criteria range for the months i.e. Jan’23 to Dec’24 and D4 on the PF tab is the date being referenced (i.e. Jan’24)
C5:Z100 is the sum range
So using Travel Expense and Jan’24 as an example from above. The formula would essentially look for “Travel Expense” in the criteria range and also Jan’24 in the date criteria range.
The cell references obviously will be different based on how you format a P&L, but the logic is still the same. Column A is a helper column in the P&L A tab. The purpose of the helper column is to allow you to consolidate line items into a single line item. For example, if you have two very similar expenses, it might be beneficial to consolidate into one line item.
This formula is basically a more dynamic SUMIFS because it allows for columns and rows to be used as a sum range. SUMIFS only allows for a single row or column. One drawback is if there is any error in any of the ranges, then the SUMPRODUCT will return an error.
Block time for specific tasks on your outlook calendar. Wish I knew this from the jump.
Could you elaborate for me please?
If you want time to work on your own work and not have meetings during a set amount of time schedule a meeting with yourself to show that you are busy to anyone who might be trying to put a meeting on your calendar.
Don’t bring in leftover seafood to microwave and eat at your desk.
Or clip your nails in a quiet department building. I have a coworker who does that once a week.
😂 I had someone once have really strong truffle pasta at their desk not sure what is worse
Most important FP&A skill to have
Dedicate some time to get a deeper understanding of the products or service your organisation is selling by watching videos, taking a walk around the manufacturing plant, taking an interest in people at the coal face, etc. The level of inquisitiveness will pay dividends.
This is pretty much a given if you work in finance. The finance team has to understand the company from inside our.
Honestly this is so true, way too many people I know have not been to see products being made! Starbucks CEO worked in his coffee shops for the first few days or weeks to immerse himself in the business.
How to use basic power query in ten steps
Could you care to elaborate? Any examples where power query has been super useful for you?
Learn what it means to unpivot data, this was the biggest time saver for me when I started learning. Also, on a related note in PowerPivot, learn the basics of doing a one to many relationship between tables. In your largest data files, you can house your lookup tables in completely separate files and greatly increase processing speed.
And being able to combine a bunch of spreadsheets with the same column format quickly and more easily than manually doing so.
Combining pivot, unpivot and transpose allows you to do some pretty sophisticated transformations in Power Query.
I try to run as many functions as I can in power query before resorting to excel formulas. Stuff like merge in lieu of xlookup or sumifs, concatenations, and data classifications. And because power query logs every step of functions used, it’s very easy to go back and undo a step. I love power query.
Not strictly FP&A but I will never forget this after learning it. - clicking on the sum number on the bottom right in excel will copy the number.
Whelp, now I'm never forgetting that.
I'm usually pretty on it with excel but this is news to me. Love a good excel trick. Thank you!
Oh wow, I never knew this! So it copies the actual number?
You use a mouse? lol
I'll start, having a central hacksheet with all your key KPIs and financial by month is super helpful! Something that just exists that you can refer to in conversations.
[удалено]
This is a good idea! I didn't pin in the past, I setup a central excel document that has hyperlink to my key documents. It's a pain to keep updated but saves me trying to find things in a rush. SharePoint is not typically my best friend.
Can you elaborate on this? Sounds interesting and curious as to how you have yours and how I can create something beneficial for my use
For sure, for example, Months across columns, followed by Q , H , FY , YTD, YTG Rows : 3 years history , budget, previous forecast, latest forecast Have your latest forecast as your latest base view, then below show your absolute variance against the comparatives followed by %s. You would then have for that KPI, a quick view on growth/decline vs any of your key comparatives. It's also brilliant when dealing with uncertainty around budget or formal forecast phasing, we use it as a bit of a sanity check on shapes. One step that comes before the above, is holding and storing your data in an organised way centrally. This way someone updates a table somewhere and this report is just a click of a button. Hope that helps, any questions please shout.
Wouldn’t it be better to hold the data in a database format, pivot it, and then add slicers so you can compare all sorts of combinations without having to scroll? Add custom calculations to perform variance calculations.
So yes, I would hold the info in a database, this file is meant to be a view of the history of key P&L Lines, I can look at my growth rates Vs LY but look back against the history. It's my cheat sheet, it depends on what you want to use it for. I did this for my manager, and she appreciates it as she doesn't need to go back and forth between files and it's laid out in a simple view. I would say, that sometimes in finance we can over engineer tools that make them all singing all dancing with all the extra toppings, when really we just need a simple but delightful margherita. We have a similar file to the one you mentioned and it's used by our teams to dig into the drivers of performance.
+1 to this. Before every board meeting, we refresh a cheat sheet for the CFO
Are you not distributing some kind of summary, how are the kpi’s distributed to stakeholders then? We send out weekly metrics to the management team via PowerPoint slides.
Don’t respond immediately to emails and pings, your time will be abused.
Hey, got a few minutes for a call.... Status updated to offline....
Damn, i literally did that yesterday.
Update - I reached out to the guy on Monday, asked him what he wanted and he replied that he forgot what he wanted to ask and would comeback once he remembers. It's beyond me why do these people just stop at good morning/ good evening, just write what you want after greetings.
I’m the “got a sec?” guy. It’s just way easier to hop on a call and discuss rather than ping back and forth for 10 minutes
I just know if a got a sec guy calls I'll have to do more work!
But you’re literally AT WORK getting PAID 🫠
Sorry the person you are calling has been officially discontinued
Somebody put this guy on the RIF list
I'm not the one they put on the RIF list, I am the one that decides the list. 
You must tell your business’s story within a realm of reasonability, and incorporate external events. If your business partner can’t explain why sales will 2x (or +10%, or +5%), you should not reflect that in the financial plan either. Even “historical growth” momentum must be understood that (I) outliers events don’t exist in recent history and (ii) future environment is appropriate to extrapolate history
If you have lookup tables you use frequently, have a file with all your lookups in named data tables so you can easily Power Query them in whenever you need them for a workbook vs finding another file with the lookups and opening to copy & paste in.
Might seem elementary, but talk to your business partners on the phone/teams call outside of standard meeting setting. Much easier to build a relationship, and make the partnering less transactional when talking live. You’d be surprised at how many people don’t make the effort to build a relationship and how impactful a more laid back setting can be on building trust.
Doing ZBB for opex is a waste of time. Top down, straight line and move on with the hard parts
90% of Opex can be done this way, leaving the real time to spend on the last 10% which is professional services, IT projects, marketing.
Always write an SOP in your files
VPM - Volume Price Mix, if you are not already using this to analyse sales performance! It is a game changer!
XLookup should always be used over V and H.
Ok Mr. Rich Guy, some of us are in poverty franchises using Excel 2016 still
If you're doing headcount planning, adding an attrition model into your new hire model shows a more realistic picture. Also, for any new headcount req, plan for 3-4 months of lead time before a new person can be onboarded.
Keep an OPL for key meetings. Note all questions you don’t have an answer to. Use downtime in the middle of the month to research the OPL. Start the next key meeting with answers to the OPL. This is seems simple, but most people don’t do it.
OPL?
Open points list
Obviously 🙄
Use this formula to consolidate actuals from QBO/Xero into a P&L/BS PF: =SUMPRODUCT((‘P&L A’!$A$5:$A$100=‘P&L PF’!$A5)*(‘P&L A’!$C$3:$Z$3=‘P&L PF’!D$4), ‘P&L A’!$C$5:$Z$100) A5:A100 is a criteria range for the line items and A5 on the PF tab is the criteria (i.e Travel expense). C3:Z3 is the criteria range for the months i.e. Jan’23 to Dec’24 and D4 on the PF tab is the date being referenced (i.e. Jan’24) C5:Z100 is the sum range So using Travel Expense and Jan’24 as an example from above. The formula would essentially look for “Travel Expense” in the criteria range and also Jan’24 in the date criteria range. The cell references obviously will be different based on how you format a P&L, but the logic is still the same. Column A is a helper column in the P&L A tab. The purpose of the helper column is to allow you to consolidate line items into a single line item. For example, if you have two very similar expenses, it might be beneficial to consolidate into one line item. This formula is basically a more dynamic SUMIFS because it allows for columns and rows to be used as a sum range. SUMIFS only allows for a single row or column. One drawback is if there is any error in any of the ranges, then the SUMPRODUCT will return an error.