Doing complex modelling in Excel is not a good idea. There is no way to do proper software engineering, so bugs are common and hard to find. For examples, see
Except "proper software engineering" would mean whatever is being built simply never gets built.
In many cases it's better to get somewhere quickly with excel then work out if "proper software engineering" is required than never get off the ground because engineering something is too difficult.
Think of excel like lego. Someone can quickly build something out of lego. Now if someone tried to build a cathedral from lego, you might suggest that they find an architect and do some proper house building, but without lego they would never have tried.
Just like I wouldn't want to live in a house built from LEGO, I'm somewhat wary of Excel models as the basis for investment strategies in major banks. The problem with the quick and hacky solution is that it's never replaced, because that would cost money, and the current solution "looks like it works".
However the excel solution has a lot of things going for it :
1) applications/reports built by the people designing and understanding the underlying needs and inner workings of whatever they're offering. Whilst very good programmers can match this (given enough - that's a lot - time), average programmers won't even try.
2) excel sheets require nowhere near the manpower required for your alternative. That factors into a cost-benefit analysis that may not favor real development.
3) Speed of development. Markets change. Investments change. Sometimes literally in minutes. Anything involving more than 1-2 people who are intimately involved in the strategy itself cannot possibly keep up.
4) Excel is in fact a pure functional programming language [1]. Unless you deeply understand excel I wouldn't be so quick to call it stupid. There are entire classes of bugs that simply can't exist in excel spreadsheets that are regularly found in programs.
1) is priceless. In banks I've seen many traders build pricing and risk solutions in Excel that would take weeks or months for developers who don't understand the products and/or maths to build. In some games, time to market is everything.
"excel sheets require nowhere near the manpower required for your alternative. That factors into a cost-benefit analysis that may not favor real development."
Not to build, but to maintain and constantly clean up the errors by using the wrong tool for the job. I know form experience having wasted countless hours because my organisation insisted on using it as a tool to upload data to our database, and using it as the database before that.
The errors they invariably introduce are also very expensive. Not just fixing them, but actions on incorrect data can be very very expensive. I've seen it, and in my experience its almost universal.
I'm not saying Excel is always the wrong solution, I am saying Excel is not always the right solution but it can appear to be. That makes it very dangerous.
In the past I've made a lot of money converting organization's mammoth mess of spreadsheets into a proper solution. I've done this for more than one organization.
The problem is Excel is easy to abuse, thats the way people know how to to solve problems so it doesn't occur to them they may need proper software to do what they need to do, not that they don't have the funds. All the companies I've worked for have had the funds.
Most organizations don't audit their spreadsheets nor keep backups or have a method of keeping track of them - the spreadsheet you get emailed may not be the current version. Its par for the course for spreadsheets to be done in a completely ad hoc manner with no controls whatsoever.
From my experience fixing spreadsheet messes, they usually waste person-hours like crazy and end up costing more than paying an engineer for custom software or getting an off the self solution. This has been my experience.
Spreadsheet abuse was a factor in JPMorgan losing 2 billion dollars in 2012, for example.
There's actually an interesting gap between 'building some model in Excel' and 'moving on into a programming model where code and data are separated'. Like generating model code in some normal programming language from a spreadsheet.
Does VBA fill that gap? I once worked on a system where one of the more important parts of the business was a system of VBA modules in an access file.
One of my more important contributions as a fresh graduate entering the workplace was to get people to copy the file from the network share to a local drive before running. Run-times went from "leave overnight" to ~15 minutes.
Right, but why VBA? Because VBA is the only language with first class support in Excel, not because VBA is good at all. If not fon its "privilege", you'd use something like Python or JavaScript (yah it sucks, but not as bad a VB) or a purpose-built sane language, not something Joel Spolsky dashed off a spec for one Tuesday afternoon.
Many great projects start out as excel spreadsheets: someone has a problem, solves it with excel, then goes on to solve other problems with that spreadsheet. People use it and become dependent on it, but over time the thing becomes intractable.
At that point in time, the functionality of the spreadsheet gets implemented by actual developers into some "engineered application."
If you are suggesting to NOT start with excel, then propose another solution-- but good luck getting anyone to use it. The key thing is, no one wants to engage "software engineers $$$" to solve a problem that lands on their desk which they handle by themselves.
Shh! Your logic and practical experience is not welcomed. We like to scream with self-righteousness from our software engineering high horses here. Software engineers know best and everybody else is wrong because he doesn't know any better, since he has a limited perspective.
In many cases it's better to get somewhere quickly with excel then work out if "proper software engineering" is required than never get off the ground because engineering something is too difficult.
Think of excel like lego. Someone can quickly build something out of lego. Now if someone tried to build a cathedral from lego, you might suggest that they find an architect and do some proper house building, but without lego they would never have tried.
And chances are they're not building a cathedral.