3 ms·

It is sad how much of Finance (primarily FP&A) gets run through Excel. There are many products out there today that can make the job easier, but as you point o

It is sad how much of Finance (primarily FP&A) gets run through Excel. There are many products out there today that can make the job easier, but as you point out, not all organizations are quick to embrace them. My personal hope is financial analysts get more tech savvy over time. SQL should be a year one skill with Python/R soon after. These should complement spreadsheets which would still have an important role in ad hoc analyses. Combine this increased tech savvy with an understanding of a visualization tool such as Tableau/Power BI and I think financial analysts could be significantly more productive than they are today. There would also be far fewer spreadsheets lying around on the network drives. It is amazing how many of those spreadsheets are simply the same model with the next month's data. Better use of databases would allow one spreadsheet with a database to store the changing inputs.

galaxyLogic 5y ago
The reason I think Excel sucks is that it hides the formulas behind the cells. There is no way to get good report on the mathematical MODEL written in an Excel spreadsheet, far less for a group of them connected together. Or am I wrong? Is there an easy way to get a report that tells what a given spreadsheet calculates?
- wombatpm 5y ago
  There is an audit feature to follow calculations. My truck was to replace all equal signs with QXQ to make formulas visible and diffable
- andrewlgood 5y ago
  IMHO, you are not wrong. The advantage of spreadsheets is they are incredibly flexible. The disadvantage of spreadsheets is they are incredibly flexible. Over the years I have noticed a few conventions that make spreadsheets easier to read from a model perspective. - Inputs should have their own tab and ideally a name. Should also be a color-coded font (blue is popular). - A worksheet should work left to right, top to bottom. I am always frustrated when I try to work through someone else's spreadsheet and the formulas just drift across a tab rather than working in a pattern toward a conclusion/summary - Pretty print sections should be separate from the actual engine. Do the calculations in a manner that is easy to understand. Create an output tab to create the pretty report. - Flag with a bright background color when a formula changes (yellow is popular). This frequently happens when your boss asks you to create a scenario where you override a formula with a constant. I cannot tell you how many hours of my life have been lost tracking down an overridden formula. These types of conventions make spreadsheets easier to inherit from someone else. They do not change the fact that there can be a lot of calculations happening through a multi-tab that you simply cannot see the bigger picture.
tpuser 5y ago
>My personal hope is financial analysts get more tech savvy over time. Probably won't happen and isn't important imo. If somebody can dig SQL/Python/R then somebody can get paid more to do something other than count beans (speaking here of lowest level financial analysts [accountants]; I was probably the only person at the division trying to make a meaningful financial forecast that was more detailed than a top level number with zero transparency. I started w/ python but wanted a COTS solution. Didn't want to babysit it.). >Better use of databases would allow one spreadsheet with a database to store the changing inputs. This is the key. The number one complaint of every other business area was that forecasts came in asynchronously and it was impossible to tell whether what had been added or removed was real. Hallway conversations took the place of structured data. That's fine for many things, but not for projections which determine whether we need to hire twenty more engineers or let go of a dozen. The product that would've solved the problem (and then some) was IBM's TM1 (now "Planning Analytics powered by TM1"). My model worked well in TM1 and could've been extended to being better than a calculated model--the direct inputs from the dozens of IPTs could have been collected without the need for any math used to back out of numbers. No "model", merely the collection of demands of every program and every potential contract. The program teams already generated all of this data; finance threw most of it away while rolling it up. TM1/PA has Excel plugins which render the change invisible to the financial analysts doing the work. Their job doesn't change but the organization can hoover up more data by at least an order of magnitude, breaking the cycle of relying upon opaque, top-level numbers that no other business area actually trusts.
- andrewlgood 5y ago
  I think one of the communication challenges in this broad discussion is what is a "finance" person. Several people refer to them as people in Accounting (accountants) when I think they really mean Financial Planning & Analysis (FP&A analysts). Within most Finance organizations there is a big difference. Accountants (particularly early in their career) focus primarily on the past and properly describing it in the general ledger following accounting rules (e.g. GAAP / IFRS). FP&A analysts (should) focus on forecasting the future, then looking at what happens and describing why it differed from their forecast (variance analysis) so they can make a better forecast next time. I agree with you that it is unlikely accountants will learn SQL/Python/R. They are tied to their ERP and Excel. My hope is the FP&A analysts will develop the SQL/Python/R skills to enable the better/faster development of complex models to forecast. >The product that would've solved the problem (and then some) was IBM's TM1 I have not used TM1, but have heard many good things about it. Today I hear more people discuss Workday's Adaptive Planning than any other product in that class. Unfortunately most are still creating their primary forecast in a spreadsheet, then loading it into the TM1 or Adaptive which then has additional rules to build out the forecast. I agree that using these types of products increases transparency and speed.

galaxyLogic 5y ago

The reason I think Excel sucks is that it hides the formulas behind the cells. There is no way to get good report on the mathematical MODEL written in an Excel spreadsheet, far less for a group of them connected together. Or am I wrong? Is there an easy way to get a report that tells what a given spreadsheet calculates?

wombatpm 5y ago
There is an audit feature to follow calculations. My truck was to replace all equal signs with QXQ to make formulas visible and diffable
andrewlgood 5y ago
IMHO, you are not wrong. The advantage of spreadsheets is they are incredibly flexible. The disadvantage of spreadsheets is they are incredibly flexible. Over the years I have noticed a few conventions that make spreadsheets easier to read from a model perspective. - Inputs should have their own tab and ideally a name. Should also be a color-coded font (blue is popular). - A worksheet should work left to right, top to bottom. I am always frustrated when I try to work through someone else's spreadsheet and the formulas just drift across a tab rather than working in a pattern toward a conclusion/summary - Pretty print sections should be separate from the actual engine. Do the calculations in a manner that is easy to understand. Create an output tab to create the pretty report. - Flag with a bright background color when a formula changes (yellow is popular). This frequently happens when your boss asks you to create a scenario where you override a formula with a constant. I cannot tell you how many hours of my life have been lost tracking down an overridden formula. These types of conventions make spreadsheets easier to inherit from someone else. They do not change the fact that there can be a lot of calculations happening through a multi-tab that you simply cannot see the bigger picture.

tpuser 5y ago

>My personal hope is financial analysts get more tech savvy over time. Probably won't happen and isn't important imo. If somebody can dig SQL/Python/R then somebody can get paid more to do something other than count beans (speaking here of lowest level financial analysts [accountants]; I was probably the only person at the division trying to make a meaningful financial forecast that was more detailed than a top level number with zero transparency. I started w/ python but wanted a COTS solution. Didn't want to babysit it.). >Better use of databases would allow one spreadsheet with a database to store the changing inputs. This is the key. The number one complaint of every other business area was that forecasts came in asynchronously and it was impossible to tell whether what had been added or removed was real. Hallway conversations took the place of structured data. That's fine for many things, but not for projections which determine whether we need to hire twenty more engineers or let go of a dozen. The product that would've solved the problem (and then some) was IBM's TM1 (now "Planning Analytics powered by TM1"). My model worked well in TM1 and could've been extended to being better than a calculated model--the direct inputs from the dozens of IPTs could have been collected without the need for any math used to back out of numbers. No "model", merely the collection of demands of every program and every potential contract. The program teams already generated all of this data; finance threw most of it away while rolling it up. TM1/PA has Excel plugins which render the change invisible to the financial analysts doing the work. Their job doesn't change but the organization can hoover up more data by at least an order of magnitude, breaking the cycle of relying upon opaque, top-level numbers that no other business area actually trusts.

andrewlgood 5y ago
I think one of the communication challenges in this broad discussion is what is a "finance" person. Several people refer to them as people in Accounting (accountants) when I think they really mean Financial Planning & Analysis (FP&A analysts). Within most Finance organizations there is a big difference. Accountants (particularly early in their career) focus primarily on the past and properly describing it in the general ledger following accounting rules (e.g. GAAP / IFRS). FP&A analysts (should) focus on forecasting the future, then looking at what happens and describing why it differed from their forecast (variance analysis) so they can make a better forecast next time. I agree with you that it is unlikely accountants will learn SQL/Python/R. They are tied to their ERP and Excel. My hope is the FP&A analysts will develop the SQL/Python/R skills to enable the better/faster development of complex models to forecast. >The product that would've solved the problem (and then some) was IBM's TM1 I have not used TM1, but have heard many good things about it. Today I hear more people discuss Workday's Adaptive Planning than any other product in that class. Unfortunately most are still creating their primary forecast in a spreadsheet, then loading it into the TM1 or Adaptive which then has additional rules to build out the forecast. I agree that using these types of products increases transparency and speed.