I was going over spreadsheets with a client yesterday, a really cool and complex set of formulas and spreadsheets for overseeing 2 managers, 35 employees, 58 customers in 5 districts.
There is a series of spreadsheets with contract details regarding service levels, pricing, hours of work, and contact info for each customer. The COO had developed a master dashboard that linked all the customer spreadsheets into a companywide overview so she can quickly locate details for any customer, manager, or employee that she needs information on. Sometimes she needed more detail than what was on the dashboard and would have to find and open the file on the network drive. It only takes 2 minutes to find the right file, but she was doing it 5-10 times per day.
Until I showed her that holding the CTRL key while pressing the [ (left bracket) would take you directly to the cell being referenced in the formula, even if Excel needs to open another spreadsheet.
CTRL [ is a trivial pursuit question if I have ever heard of one, but it will save an average of 15 minutes per day for her. Do the math on 15 minutes per day, for 5 days a week, and 50 weeks per year (yes, there are people who go on vacations!) and that translates to 62.5 hours of savings. Now you can take 3.5 weeks of vacation this year. Rather have the cash over vacation? Multiply 60.5 hours times your loaded hourly rate to see how much money that adds up to.
I don’t care if you call it lifetime learning, continuous learning, or whatever current catch phase is circulating in your world right now, just STAY CURIOUS!
Need help in Excel? Check out www.ExcelProTrainer.com for a skills boost.
Sales growth is wonderful, and what most of us like to see happening. But the real indicator to keep your eye on is profit. During growth periods, there are people coming and going on your payroll, new product lines, tools and computers bought, bigger offices, more phone lines, the list goes on and on. But the question remains – are you better off with or without the sales growth?
You don’t need to wait for your taxes to get done to answer this questions. By merging sales data with your profit and loss, you can look at income and expenses on a standardized per unit basis, allowing you to compare how things were to how things are. QuickBooks has a nifty export to Excel feature that make life much easier to make the calculations.
Need help with the process? That’s what we are here for. In person or by Skype, we give you the numbers to make better decisions.
You spent all this time gathering information for your CPA to do taxes. Income, Expenses, Capital Purchases, Depreciation, and every other detail your CPA wanted you to dig up. And then you paid Uncle Sam a nice chunk of money. Most people don’t want to look at financials any more for another year, but this is exactly when you should be delving into the nuts and bolts of your business to assess your strengths and weaknesses. What do all these numbers mean and where do you start?
When I am asked to look at the health of a business, I start with this list.
- EBITA – Earnings before Interest, Taxes, and Amortization. A look at how your efficient your business is without taxes or debt
- D/A – Debt to asset Ratio – how dependent upon debt is your business.
- ROA – Return on Assets – What percentage did you make on the total assets of your business
- ROI – Return on Investment – How much did you make on YOUR investment
- Owners hourly rate – often owners pay themselves a salary, but breaking it down to an hourly rate can give you a different perspective
- Cost of production per unit, compare to previous years. Are you making improvements in cost control of critical areas.
- Budget vs Actual – look for how accurate your plans were.
- Profit and Loss, compare previous years.
- Inventory Adjustments – make sure inventory purchases and use are not artificially inflating or deflating profit.
- Accrual vs Cash Profit and Loss comparisons- in particular, I am looking for big changes in AR and AP, Inventory values
- Profit and Loss, with percent of income and expenses. Look at the big expenses and see if small changes can make a big impact on the bottom line.
- Develop or tweak budgets – now is the time the numbers are fresh. Look at your plans for growth or cutting unprofitable areas of your business. Incorporate marketing plans with financial plans. Plan your profit levels.
- Balance Sheet – Look at changes in Net Worth
Ideally, you would be looking at these indicators on a quarterly or monthly basis. Minor adjustments to spending or income timing can have big impacts on your total tax bill. The trick is to know you need to make adjustments in the same tax year, not 3 months after.
It is a long list, and it takes time and thought to go through. But the payoffs are there. Don’t be intimidated by numbers!
I have been having a running conversation with a person who wants to open up a storefront. Very good ideas, and he was ready to move fast. So when I said, “Not to be forward, but what do your budgets show for sales and expenses?” I was met with silence. Then a quiet “I don’t have a budget yet”. Sadly, that is a common response. And while the business idea may be solid, I really prefer to make mistakes on paper.
Asking about a budget did not shake his confidence in the business. It actually took a bit more than that. I shared a customized budget template with him and filled in a few numbers based upon conversations with him. When he started seeing how much sales he would need to cover cost of goods sold, rent, gas, electric, internet, and that pesky thing called owner draw (that’s your pay by the way!) he started thinking about the volume needed to carry a storefront. It was only then that he slowed down and started to see the benefits of a solid business plan.
After using this template as a framework to look at his business idea, he backed off from signing a 12 month lease. And that’s a good thing. He has not lost sight of his dream, but is now focusing on building up his web sales before investing in the overhead of a storefront. Now his focus is clearly defining his target market, building up his list of potential customers and finding out not only what they want and need, but how much they are willing to pay. Those numbers are plugged those numbers into his budget, making an even better plan.
I have often heard that a business plan rarely survives first contact with customers, and I agree wholeheartedly! But that does not mean that you ignore the basics of finance and sales. Instead, focus on the needs and wants of your customers as you develop your budgets and business plan at the same time. A business plan is much more robust when you approach the process by focusing on the customer first. Take the time to work through the numbers first – whether it is a brand new business, or an expansion of an existing business, it pays to make the mistakes on paper first.
Need help developing a financial framework to look at your business? Want to see the sample Excel budget that helped him change his path? Send me an email and I will be happy to share it with you!
I met with a department manager the other day who had very little excel experience, he was able to do simple data entry and knew that formulas existed, but not how to create them. In October we had a talk about training and how long it took to manually do state mandated reporting – about 40-50 hours of summary statistics, all done by hand sorting and calculations. Call me in January and we can set up some training and set up a spreadsheet that will automate the process, should take under 2 hours for the spreadsheet development and we can cut your total time on state reporting down to under 5 hours, probably under 2. Most of the data was already entered into MS Word, not in a spreadsheet to enable data manipulation. No biggie, I thought, copy here, paste there, couple of totals, averages and percentage calculations, and a year to date summary sheet, wave my magic wand, and ZAP! Required yearly reporting done in record time.
January rolls around and I called on the manager to see how things are going. “Not good” replied the manager, “my budget got cut by 25% and it was tight last year. This year is going to be awful.”
Perfect time to save 40-45 hours of your time, right? Not according to this salaried manager – his time was already in the budget and all he could see is more money going out of the checkbook, not the time savings that would not translate into cash. Now don’t get me wrong, I have been there where every single penny counts and there is not enough to pay all the bills. But to ignore saving 40 hours for under a $200 investment? Wow…. I am still shaking my head over that logic. Since it was a REALLY small project, I did not want to get into a long sales pitch showing the benefits of the automation, I just walked away and wondered how much longer that manager would be around. – if your labor is paid minimum wage and you don’t believe the time savings, this is a logical conclusion. At minimum wage, you must save more than 28 hours to pay off investment.
I worked with a restaurant owner on his food cost management system. He was meticulous about keeping his food cost under 30% of sales and had worked out the cost for every item on his menu in a spreadsheet and could rapidly look at the impacts of price changes to his bottom line. A very nice management system to say the least! However, he would regularly add all of his food vendor bills and divide by total sales for the week and he was coming up with a food cost of closer to 40% of sales.
I was called in to help him figure out the error in his spreadsheet and he was suspecting he had a theft problem. It didn’t take long to confirm that his calculations were correct, and he was starting to look through security tapes with a grim look on his face. While he started down that path, I looked at the other half of his double check, the total of the food vendor bills. Turns out that he was getting all of his paper products like take out containers, napkins, cleaning supplies, from the same vendors. The consumables accounted for the difference in the food cost percentages.
Sometimes it’s the small things hiding in plain sight that trip you up.
I met with a plant manager a while ago about training his line managers to become proficient at Excel. You know, that fun number crunching spreadsheet program that people love to hate? So we sat down and I started asking questions. What skills do your line managers currently have? How many people? What do you want them to know how to do? So after about 10 minutes, I have a pretty decent idea of the skill level they were at, and where he wanted them to be.
I asked if he was going to be in the classes, and he quickly said, no, I know more about Excel than all of the line managers. I responded no problem, just checking.
Then I asked him to pull up a spreadsheet that he wanted the line mangers to be able to use so I could tailor the training to something they would use every day. He loved the ideas, and quickly pulled up a spreadsheet the mangers would need to use daily and a calculator, punched some numbers on the calculator and said “We take the daily production numbers, divide by hours of operations and plug the hourly rate in this cell right here”.
I shut my mouth and tried very hard to be diplomatic. Because this self-proclaimed Excel expert that didn’t need training had missed an easy one. For those of you who don’t know anything about Excel, it is basically a giant glorified calculator with a monster memory. If you have Excel open, you don’t need a calculator, it is the calculator. On top of that, every time you reenter data, you double the chance for data entry error. I responded, “Is there a reason you don’t use Excel for the calculations?” He looked at me blankly and said “What do you mean?” So I showed him how to enter a formula in Excel to do the calculations automatically. He looked at me sheepishly, and said thank you. That was it. Never mentioned it again, never showed up for class.
Here is my question to you: Do you know what you don’t know? Because that’s what can hurt your business.
And if you don’t know what you don’t know, that’s ok. Call us. We can help fill in the gaps.
“The Only Sustainable Advantage is to Learn Faster”
“Data is Power”
“Lots of Little Things Become Big Changes”
Tons of great information packed into one article from an unsuspecting source. Enjoy the read and learn something new…
Here’s a great reminder that balance is important and nice article from Entrepreneur Magazine:
“There Are 8 Indispensable Elements of Real Wealth and 7 Aren’t Money”