The finance business area at the last large company I worked for was borderline ridiculous. The division I was employed at ran a billion in revenue through Excel sheets scattered across network drives. FSS had a nightmare of a week at the end of every quarter. I was given a promotion and a shift from engineering to business analysis with the goal of creating a model to predict workforce requirements for ongoing business and new contracts. After a few months of learning the ropes and the history of similar attempts, it became clear that the company already held a license for software suitable (perfect) for this goal and the finance area was already slowly working towards ditching the excel mess in favor of the sane solution.

By "slowly" I mean that they had been inching towards using the right tool for years, finding any and every excuse to abandon course. The corporate app dev unit explained to me that they were wary of working with the finance area of my division as finance had repeatedly gone 90% of the way to an improved tool only to bail out at the last minute, wasting time and money and leaving corp app managers the task of explaining why things fell through to their bosses.

The best (worst) excuse given by finance during my attempt to sort things out was after we were acquired by an even larger contractor. "We're not sure if <company> uses <software>, so we're going to need to put a complete halt on the shift." They weren't sure? It took me less than a minute to go to the acquiring corp's job req page and see that they were hiring admins/devs for the software in question. Ridiculous. Finance leadership could have made a single call to ask a single question, but rather than stepping out of the comfort zone they decided to scuttle a project which was all but guaranteed to improve their lives and the organization as a whole.

I'm close to 50/50 on whether the finance unit was doing something dishonest and wished to retain the control provided by using an opaque/incomprehensible system or if they were merely too afraid of any sort of change by virtue of their bean counting nature. Either way, they're probably still actively harming the company.

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.

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?

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.

There is an audit feature to follow calculations. My truck was to replace all equal signs with QXQ to make formulas visible and diffable

>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.

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.

>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).

Noted and agreed. I'm not well versed in finance lingo beyond what I learned by taking up a business analyst role. The distinction you mention was likely lost on me due to the fact that this division didn't really have an FP&A team and every accountant was given the title of "financial analyst".

Sure, FSS made projections, but those were all terrible. They were worth less to functional managers than the paper they had been printed on. A given year's labor forecast came in the form a single column of multimillion dollar (Ex)cells per cost center. No ability to dig in. Consistently low by ~10%. Did leadership get a bonus by expanding the business book over the course of a year? Who knows?!

FP&A was an afterthought for leadership. I was probably the only full-time FP&A employee (at an office with over 700 employees), yet I did not report to finance--I was under the program management umbrella for some reason. It didn't take long to recognize the potential value-add that our finance area could provide with a single change. Alas.

Appreciate the replies. You probably feel my pain better than I do.

I am at a point where I don't think there should be finance departments in tech companies. I also believe there shouldn't be siloed data departments for the record either. There should be teams, build around a cohesive product goal that solves customer needs. There should be someone who understands the money side of things embedded in that department doing analyses and making recommendations to the product manager, not direct to a CFO. Maybe half their job is regular market analysis work too if the role isn't that big. Those product managers should then have in their day to day functions a requirement to get that info to a CFO's hands (who is really just the executive teams internal finance person) to make their case for their product.

For clarity, when you refer to finance, I think you mean Financial Planning & Analysis (FP&A). I think you would agree that centralized Tax, Treasury, and Accounting functions are ok and should probably report to the CFO.

With respect to FP&A, I have seen several firms that simply embed them within a product line or business unit. They need to submit forecasts to a central group, but their primary mission is to support the product or business line.

Yeah basically but I've yet to work on a team (that I didn't build myself) where the FP&A is separated from Accounting/Tax and not a direct report to the CFO or CEO if the company is pre-CFO. Sometimes even seen Accounting/Tax as a direct report under HR with zero product connection. So I just hadn't come across that term before.