brucebadeau

Part 4 Why did I do this excruciating experiment? Well, I wanted to show clients how I can make Excel look like a bullet proof application with output on top of sculptured, raised backgrounds… i.e. the look and feel of make-your-own Dashboards… Bottom line – I don’t think clients will pay for this expertise… You can make you own Excel Dash Boards 9 as I have done many times) without resorting to the extremes I recently undertook… …So having confessed my inexperience about the difficulty in making non- Excel calculators, I thank my luck stars that we have Excel… without out a doubt the most flexible and beautiful numerical authoring tool on the planet offset by the extreme difficulty in detecting algorithmic mistakes. Isn’t life a trade off, anyway? Thanks for reading this … Bruce Badeau [email protected]

Part 3 A few weeks ago I wrote a simple fuel saving device feasibility calculator. I whipped off the Excel version in a couple of hours…. Then, I tried to do all the calculations inside controls using VBA code, placing output in text boxes… OMG … this “simple” experiment turned into a long nightmare… What we take for granted with Excel – i.e. dependent relationships being automatically programmed is NOT done in VBA… I had to place code in every dependent control (SpinButtons) to calculate several text boxes and to carefully format them both numerically and graphically … like Green Boxes for Positive Numbers and Pink for Negative Numbers… Now I should have call up a module and avoided duplication, bu nonetheless, this was a tough edit… Spinning numbers in and seeing if every text box that should have registered movement actually did…

Part 2 To underscore the other knowledgeable experts on this bog, finding the error before you replicate is very tough, indeed. It takes time, several sets of devoted eyes to methodically question each algorithm, which unfortunately are written in cells, which are hard to edit. One thing I learned was to use spaces in a cells code… Spaces inside a huge concatenated IF Statement (the type I love to make), have no effect on calculation, but a huge effect on legibility and understanding… Remember, Excel 2007 allows something like 7,000 characters in a cell, so spaces can work… Use Alt Tab to move code to the next line… give yourself a clear line and line up the code so that similar code components are in columns… this really helps when you come back a year later and have no comments to remind you what you wrote… also if you use VBA – to calculate and fill in a cell, be sure you clean that code and leave some narratives as to what you are doing…

Part 1: I'm a financial model builder since Excel came out on the MAC in 1987 (I’m now however a PC and 63 years old www.fser.net). Some of the inaccuracies are caught using accounting articulation points: Assets = Liabilities + Shareholders' Equity... if out of balance, something is wrong... What is wrong is a bit tricky and takes thorough understanding of how Income Statements, Balance Sheets and Cash Flow Statements interact. Unfortunately, articulation points don't catch algorithmic mistakes. My models have some very complex algorithms built over the years and the best thing to do is graph each line “quickly”, looking for breaks or unusual patterns in each line item. My models are regularly 3,000 to 5,000 lines long - not because I like complexity, but because I have lots of ways of expressing revenues and expenses. I can model most business in a few hours. Recently, I built a steady state feasibility model for growing, harvesting and extracting lipids from algae using heterotrophic and autotrophic systems. I did make a graphing tool that would allow me to capture the relationship between any input and output as I incremented an input 10 times. In most cases the output was basically linear as the model was basically a linear calculator… I would suggest any builder of engineering models to implement an incrimination graphical recording system as an error checker.