Utilizing Excel: Schedules and Automation

excel

 

Service and product-based companies share many similarities as well as differences. For this article, we are going to focus on one specific requirement that service-based companies need. The goal of a service company is to spend most of their time working for clients to cover costs and turn a profit while leaving enough time to develop and grow the organization. There are plenty of products available that give these companies a way to manage how much clients should be billed. It’s up to the employees to keep track of when they work on different projects; the owner decides how much to bill clients for that time. That is why it is imperative to have the ability to keep track of billable and non-billable hours.

Keeping Track of Time: Using Excel

This concept is neither new or exciting, but it is necessary. A product-based company has a concrete object that shows the productivity of the business. When you provide services, it is difficult to measure your productivity level. Once you develop a system to measure your hours, how do you measure whether those hours are billable or not? You could sit and write down every single hour and a description of what you performed. This sounds incredibly tedious and wasteful of valuable time that could be better spent serving clients and generating revenue.

The goal that I wanted to reach with this spreadsheet is automation. There are several approaches out there; scheduling templates are available from within Excel and on their online resources but none of these could provide the automation that I was looking for. A schedule that automatically calculated how long I’d spent on a project so that when I entered my time into my timesheet I wouldn’t have to go back and count each hour for each project. This wastes time, especially in cases where I worked on a project multiple times during the day. The tedious counting was not conducive to a productive day.

I decided to solve my own problem by developing an Excel spreadsheet that would calculate the different number of hours that I had spent on the various projects.

Development of an Automated Schedule

I created this sheet using different Excel functions most of which are relatively simple:

  • Basic Functions:
    • IF
    • AND
    • OR
    • ISBLANK
    • VALUE
    • ROW
    • COUNTIF
    • SUM
    • MAX
    • SUMPRODUCT
  • Conditional Formatting: Entirely for cosmetic purposes. In fact, this spreadsheet could probably do with a lot more beautifying, but presentability is not the purpose of my schedule.
  • PivotTable: To separate the projects into a list that then counts how many occurrences appear throughout my day. This is important because I have separated the day into 15-minute intervals and I used the autofill feature to show how long I worked on a project.
  • Developer mode to create two buttons that make my life slightly easier:
    • Clear List: Empties my list of projects for the day
    • Refresh PT: Updates the PivotTable since it does not automatically update when you change your data.

I have spent a lot of time on this spreadsheet, probably more than I should have: going through multiple iterations, trying different solutions, and researching what functions could perform the actions I wanted. However, it does save me a few minutes at the end of every day and it’s easier than trying to remember what I did all day.

True Benefits of Scheduling Automation

After developing this schedule, I realized that efficiency and productivity was not the real benefit. Building this spreadsheet has given me a project to work on during my downtime and is something that I can take pride in. I built this spreadsheet on my own through my own creativity and effort. No one asked me to build it, it has a useful purpose, and it serves as a demonstration of what I’m capable of making. Not to mention, this spreadsheet makes me happy. I enjoy working with Excel and seeing what I can do with it. I’ve always had an interest in computer programming despite being an Accountant. Unfortunately, I am a terrible programmer. Luckily, Excel acts as a middle ground between a clean user experience and the dirty nuts and bolts behind software.

It’s important for everyone to find a hobby or something at work they look forward to. Yes, you should like your job, but having something to do on the side allows you to take a break when you’re stressed while still being somewhat productive. In my case, I was able to use this schedule to keep my brain working during my downtime and have something that I liked doing. Even though I currently am at a point where I don’t know what else to do with it, I am sure there are hundreds of improvements I could make, even if it’s just to the appearance. That gives me a lot of hope about where this spreadsheet could lead me. It will never be finished, and nothing is ever perfect, which means I will always have something to reach for.

If you’d like to learn more about how this schedule works, please let me know and I can create a follow-up post where I get into how each function works and why I made certain decisions along the way. I challenge you, however, to find your own way to solve this problem and see what you come up with. You might just surprise yourself.

 

 

3 Lessons Learned from Restructuring a Manufacturing Company

planning-1-715x390

3 Lessons Learned from Restructuring a $30 Million Manufacturing Company

As restructuring and turnaround consultants, we’ve seen a few things when it comes to family-held companys who have found themselves in trouble and need help in turning their businesses around. For example, from 2014 to 2017, a local manufacturer  from $30 million in revenue to $9 million. Within the span of 3 years, the company had laid off over 70 employees and lost $21 million in revenue. This type of revenue loss is not uncommon with small to mid-sized family owned manufacturing companies who didn’t have a grwith plan, processes in place, or lacked innovation. As businesses are passed down from generation to generation, a lot of issues arise from lack of revenue to product diversification as the business environment changes rapidly. But there is a deeper question related to how a 30-year business can lose over $21 million of revenue within a span of 3 years. With our involvement as restructuring consultants, we’ve helped the identify three main issues related to their decline.  Here are are some lessons that every business needs to learn from this expereince.

Process Matters

As Steve Jobs mentioned, “great things in business are never done by one person. They’re done by a team of people.” For a team of people to work together and achieve results, processes must be in place.

Unfortunately, most small to mid-sized businesses believe that documenting or implementing processes is ineffective. This mentality is the case of this manufacturing firm who has been in business for over 30 years. The company didn’t have sales processes, accounting processes, or cross departmental processes which resulted in an inability to finish projects on time and within budget.

With a reputation of late delivery, the brand of the company deteriorated. Eventually, this led to a decrease of business from loyal customers. The founder and CEO, who is an engineer by training, had neglected to implement processes within his organization which has come back to haunt him 30 years later.

Pay Attention to the Numbers

We all hear the saying “Numbers don’t lie”, but very few small to mid-sized business owners pay attention to the numbers. With a 30-year business, one would think that the CEO would consult the financial statements to manage his business; from decision making to cost management. In this case, the CEO had limited his ability to manage projects and pay attention to the financial data.

Unfortunately, this story is not uncommon with small to mid-sized manufacturing companies. For any business leader who lacks of time or ability to study their financial data, it is critical for them to hire a CFO or a controller to do the job.

Financial statements are the best gears which provides vital information to the company’s overall financial health and neglecting them can lead a company to lost $21 million in Revenue within a very short period of time.

Focus on Growth Rather than Maintenance

There are two components related to running a business:

  • The growth section which focuses on strategy, organizational change and innovation
  • And the maintenance section which focuses on the support of the business such as IT, HR, Tax and Accounting etc.

About 80% of founders spend most of their time on the maintenance side of their business. This, in fact, lead to lack of discussion and focus on strategy and innovation within the organization. The lack of spending time on the growth section of running a business is one of the reason why most small to mid-sized manufacturing firms have stagnant or declining revenue.

Throughout the years, the founder of this manufacturing firm was lost in the maintenance side of his business. Unfortunately, this was a blow to his company because not enough time was spent on strategizing and innovating.

Final Thoughts

Laying off 70 people of your workforce and losing $21 millions within 3 years is every business owner’s nightmare. To stop the bleeding  it’s never too late to reevaluate your business to implement processes, pay attention to your financial statements, and spend more time on the growth section of your business.

The bottom line: It is worth the investment to bring in a expereinced team to help you evaluate the situation and put the critical pieces in place to turnaround the stituation and begin the climb back up the ladder to healthy reveneues and growth.