EXCEL Is A Lifesaver
How you can predict the future with EXCEL
Did you ever contemplate some of the formulas in life, and then apply them?
For example, why does 2+2 = 4 (at least in “normal” math).
Or why you have to do math in the order of PEMDAS? or BODMAS? or BODMSA? or even possibly MEDUSA?
I believe most of life is formulaic, and if you can apply some of the formulas, your life will be easier.
Ever since 1994, I have applied a fitness formula to oral health and it has saved many of my patients, my family, and me from unnecessary dental bills.
But that’s for another article.
Let’s talk about EXCEL in this article, because I think it can help you.
When I was in dental school (1982–1986), I lived on student loans. Two or three times a year I would get $20,000. So, for most of my four years in dental school, I felt like a pauper, but a few times a year I felt like a king (for only a few days). Unfortunately, this caused a lot of marital stress because my wife and I always argued about how to spend the $$. She would want to immediately buy all kinds of stuff, but I wanted to hoard it and try to ration it like a captain on a lifeboat with only a few provisions left before starvation and death.
The problem really was that neither of us had the correct viewpoint of the money.
I realized I needed a means of budgeting, but not your usual budgeting of dumb lists of bills and income etc. I needed a budgeting concept on super steroids. I needed a computer. But the kind of computer I needed didn’t exist, at least for regular people.
I remembered that my dad had always stayed up most nights using a mechanical calculator with a big lever to activate the internals. It looked a lot like a one-armed-bandit slot machine with a numberpad. It looked a lot like this:
Almost every night as a kid, I would hear the tap tap tap of the numberpad and the whirring and clatter of the mechanical arm powering the mechanical stuff inside.
So, I got myself an electric printing calculator. Not much better, but at least portable and a lot quieter.
In 1984, a friend of mine got an Apple Macintosh computer. I went to see it and was blown away, but couldn’t afford such a great device. However, I vowed that when I graduated, I would get some sort of better computer with any possible gift money or wrap it into a business loan or something.
Around 1984 my brother sent me a very simple Compaq computer with a tiny screen that could show only a few columns and at least I could budget for a week at a time. It was a lot easier to print a few of these and tape them together, and if things changed substantially, I could modify the numbers and print a new set of pages and re-tape them.
After graduation in 1986, I gifted myself a simple IBM PC clone with a 20-megabyte hard drive! With that, I could run LOTUS 123 and see about 3 months of finances at one time.
It didn’t take long for me to overwhelm LOTUS, fill up my hard drive, and slow my computer to a crawl.
Over the years I switched to EXCEL and bought better and better computers based on their ability to run larger and larger spreadsheets — kind of like how gamers buy increasingly faster computers with better and better graphics.
My current spreadsheet runs on a Microsoft SurfaceBook 2, contains 4,824,232 cells, begins in 2014, and ends in 2055 when I’ll be 100 years old.
Well, with this kind of processing power, and EXCEL, I can predict the future.
I know it sounds crazy, but it’s true.
It is said that you should have at least 6 months of cash available for emergencies. But you never know when emergencies will happen. Yet, it’s inevitable that they will happen. Sometime. In the future. Thus, they are predictable to some extent.
Therefore, budget for emergencies. You can set up your spreadsheet to put $x into savings every week so you eventually have 6 months of cash.
I know, I know, it’s hard to save money when you don’t make enough money. But that isn’t usually the problem. the problem is seeing the big picture and managing it from high above. Many times when I think I have a money problem, it’s really just a management problem or a vision problem. Or I just need to move an expense to another week. For example, if I’m short of cash in one week, I might decide to charge something and then pay it off a week or two later when I have the money that’s predicted to come in.
Most people manage their finances like driving a car down a mountain road without guard rails, in the dark, without lights, in rain and fog, without windshield wipers, with bad brakes, low on fuel, while trying to eat and smoke and talk at the same time while texting, etc….. And if you rely only on an accountant, you are trying to do all that while looking only in the rearview mirror, at a map.
Many people open their mailboxes and discover a couple-thousand-dollar property tax bill! ARGH! And they freak out. But property taxes and mostly every other expense you can think of are predictable. I have all my property taxes (and all other expenses) projected at 4% inflation till 2055(it was set at 3% inflation until I read Tim Denning’s article on inflation.)
What you need is a way to pilot your finances like flying an airplane with a flight plan, updated weather reports, clear weather, extreme visibility, radar, etc… and that’s what EXCEL can do for you.
By starting now and tracking all your finances weekly, pretty soon you’ll have enough data to project for about three months. After three months you can project for six months. After six months, project the rest of the year. After that, you can start projecting for future years based on averages form the past. Each year you keep averaging the prior years’ weekly income and outgo.
After a few years of that, you start seeing trends. Then you can start really predicting. Sure, if you predict to when you’re age100, much of that will be guesswork. But the funny thing is that what you predict for, often seems to happen. Or close to it. At least in the first three years of the future.
One of the keys to this is weekly prediction updates. In fact, right now, I update daily because my business is so crazy, with cascades of money coming in and then zero and then a trickle, and then a cascade. It would be terribly stressful for anyone else. Yet, based upon prior overlapping predictions, I have already triple or quadruple estimated my financials so nothing really bothers me. In fact, when I was put on furlough on March 18 due to the COVID crisis, instead of running around like a decapitated chicken, I simply applied for unemployment and recalculated my budget for six months of possible unemployment. I also already had 6 months of cash set aside from years of prior budgeting for emergencies that had not happened (yet). And then I sat back and enjoyed my “vacation”. In my normal job, I don’t get a paid vacation. But after being on unemployment, it seemed like a paid vacation. I had never gotten paid for not working. It was great.
Then, in July, my furlough was over, and I went back into my spreadsheet to add in my projected income. BUT, that’s not as simple as it sounds. I mean, it’s easy, but you have to think a bit. Because, I work on commission, I don't get paid until I produce. And even then, I only get paid twice a month, but I have to work a month before getting any pay. So, my income estimates had to account for a month of zero income while ramping up production, and then estimating my percentage of production and projecting that for the next few months. At first, I worked only two days a week, so I projected for that. But after a few weeks, I was needed three days a week, so redid my projections.
Actually, it’s like a strategy game for me now. It really helps when planning to buy a car. After my car had a few major repairs, my averages showed that I would have paid about $600 per month, for the year. And since my car was relatively old, I projected more repairs at that rate. Sure enough, I had another breakdown that also caused me to miss a day of work, which was another thousand dollars of paycheck loss on top of the car repair, not to mention problems rescheduling dental patients, etc. I realized then that an unreliable car was more expensive than just the repairs to keep it going. So, I simply set up a new column for buying a car. I plugged in the estimates for a new car, new insurance, possible repairs after 5 years, gasoline, and everything else I could think of, while keeping many of the same vehicle expenses from the old car. After erasing some projected major expenses from the old car column, EXCEL finally came up with a vehicle price that could fit my budget. I ended up buying a new Chevy Volt Hybrid for about $646 per month that costs me 1/2 as much for “propulsion” as a regular “ICE” car. So, I also modified my gasoline expenses WAY down (92 mpg! average) and my electricity slightly up by about $60 per month. Plus I factored in the hybrid car rebate. After 6 years of virtually trouble-free experience, my only major expense was a recent tuneup and repair for $1,400. But I had the money for the repair because when something I budget for in the future doesn’t happen in the time frame for which it’s budgeted, I simply move it down another week, month, or year. In the case of the $1,400 car repair, all the projected repairs up to that point hadn't yet happened. So I had continually just moved the money downward, adding to the future repair expenses. So, when I finally did have to spend a major amount of $$, it was as if the money was already magically there.
EXCEL is also amazingly useful for starting a new business. Recently, I developed a new preventive dental product. Before developing it, I studied the market, read books on startup businesses, and made a couple hundred columns of sales estimates, production costs, shipping, insurance, and every possible thing I could think of, and STILL, I’m adding a column a week or so, as new things pop up. The most beautiful thing about it though, is that the new business is intricately linked to my dental business and personal finances. Everything in my EXCEL workbook is linked like a huge financial organism. Of course, that would be impossible and not even advisable in standard accounting. But life and business are not like standard accounting. Accounting is, in my opinion, like trying to drive forward while looking backward.
Sure, accounting keeps you from mingling funds, screwing up your taxes, and you can run endless reports (BORING), but it’s virtually worthless for anything else because you must ACCOUNT for everything. And doing that totally ruins the ability to plan and predict. If everything must be accounted for, then the second you need to change just one prediction, everything related to that one thing must be re-allocated and re-accounted for, like endlessly re-shuffling cards. But in EXCEL, it’s just a mathematical process. In EXCEL, if you need to move some money around or you decide to charge something instead of paying for it, you simply change the numbers in the appropriate cells. “let’s see, I’ll just erase that $100 in my checking account personal expenses section of EXCEL for that pair of shoes, and I’ll enter $100 in the charge account section for personal expenses. Everything from that entire row downward till 2055 and all affected columns are instantly recalculated, and Presto! Your entire financial future is re-predicted and you can move on with your life in a few seconds.
But with accounting programs, you can only do one thing at a time, and even if you have some sort of accounting program budget, it may only be a year’s worth of information, and you would need to find every affected transaction and account for them all.
So How to Get Started?
I recommend at least 8 columns on the left side to depict the months and the days of the week. Each row would be one week.
I recommend at least a few columns to the right of that for your bank account balances.
The top 20 or so rows should depict summaries of at least 10 years.
Some of the other rows across the top will contain columns for names of expenses or vendors, etc.
Below the vendors, you can develop formulas that automatically enter values weekly, monthly, quarterly, semi-annually, yearly, etc.
If you need to add a column of expenses, simply copy an existing column of formulas and paste it where you need to and then rename the column and adjust the amount that is to be projected. In this way, you can predict the finances of a column in a few seconds and the entire workbook and all affected cells will be adjusted in a few seconds too, including your bank account estimates.
I have so many things linked that I can estimate all my taxes and virtually everything till 2055 — even if I just buy a stick of gum. To some, that might sound ridiculous, but as a dentist, most of the time I am dealing in microns of cement, and fractions of millimeters, and success or failure depends on such minute details.
I know I’ve probably overtalked here, but you know, most of us don’t need another problem. We have resources to manage tons of data and predict the future. So why not do it. It saves me heart muscle and stomach lining. And that’s priceless.