I wrote a finite capacity planner for a pie factory in Excel v6. It made my Pentium 60 run a bit warm for a while. It took about 18 months - I was the only IT bod in the place. I also learned basic networking and installed a pair of hubs (yes hubs) with a 10BASE2 "backbone" and 10BASET to the desktop. I learned the new Novell 4 stuff, NDS etc and wrote login scripts etc. When I started, Planning were using Lotus 1-2-3 for DOS and some amazing feats of keyboard jockeying. When I finished with them, they cranked the machine and then used their experience to tweak the plan a bit. Run it past the Production meetings, feedback and twiddle a bit and send it out to the Team Leaders, Quality and the rest. If things started to look different, then run the plan again etc. I borrowed my Dad's logistic textbooks to learn about forecasting (v).
Getting from infinite capacity planner to the finite version took quite a while!
I don't think you can fault the tool. VBA in a spreadsheet gives you a lot of power but it needs discipline to wield correctly. I used to have a row at the bottom of all my tables with the word "End" in tiny text in each column, always formatted white on red. All my routines that ran down the table to look up and do something would always look for that signal that the end had been found. Nearly all formulae were entered by VBA. I had auditing routines that would test the various sheets for errors - I suppose I "discovered" unit tests. One of them looked for a row of text with specific formatting ... Another obvious check is having row and column sums cross checking each other. I (re)discovered loads of little things like that.
With care a spreadsheet can be quite handy for all sorts of tasks but please don't equate the ill advised monstrosities you (and I) might have come across in the past with a fault in the tool itself.
Anyway, there is a lot more to this story than that and back then I had a IBM System/36 running the show as well to worry about. Twinax is a right old laugh to deal with. I remember going to Eng and asking to borrow a spanner and a soldering iron - "but you're Planning, what do you need those for".
Sorry, started waffling 8)
(v) How to forecast demand from the multiples in the UK for pasties, sausage rolls etc, back in the day. There are two cycles one is weekly and the other is roughly annual, with peaks and sometimes spikes at Easter and Christmas and some upticks at bank holidays. The weekly one literally looks like a sine wave, the annual one is a bit more involved. As a first go, take the last three orders by day of week for a product and calculate an exponentially smoothed forecast for next week. For example take the last three Mondays to get next Mondays's forecast. Bear in mind that you need to prep, make, bake, chill and wrap the product and ship to depot with about seven to 11 days shelf life and it takes something like one to three days to do that. You are always making to forecast, which is quite tricky. This was about 25 years ago but Asda, Nisa, Lidl etc used to take our forecast and fax/EDI it back as an order without changes.