Category: Database and Spreadsheet Design & Troubleshooting

Stay Curious – The Value of Knowledge

Posted on by Russ Coltman in Data Management, Database and Spreadsheet Design & Troubleshooting, Management and Leadership, Technology

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.

.

Are your profits growing at the same pace as sales?

Posted on by Russ Coltman in Accounting, Data Management, Database and Spreadsheet Design & Troubleshooting, Management and Leadership

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.

A business without a budget

Posted on by Russ Coltman in Accounting, Data Management, Database and Spreadsheet Design & Troubleshooting, Finance, Management and Leadership

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!

Focus on the big picture

Posted on by Russ Coltman in Database and Spreadsheet Design & Troubleshooting, Management and Leadership

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.

Do you know what you are measuring?

Posted on by Russ Coltman in Data Management, Database and Spreadsheet Design & Troubleshooting, Management and Leadership

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.

Do you know what you don’t know?

Posted on by Russ Coltman in Data Management, Database and Spreadsheet Design & Troubleshooting, Management and Leadership, Personal Growth

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 stopped.

I stared.

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.