Effectiveness Archives

Are poorly written Excel macros slowing you down? Probably!

If your macros are taking longer than they should, you’re not alone. We’ve seen processes that should complete in seconds, but instead, take minutes – and even up to 96 hours in extreme cases! With the right optimisations, we reduced those 96-hour routines to under a minute.

Why are your macros so slow?

Culprit 1: Excessive read/write operations

Every time you read from or write to an Excel sheet, it slows things down. While tricks like disabling calculations, screen updating and events help the issue, the real issue is frequent read/write operations for every single cell. This can grind your process to a halt.

Solution: Use arrays! Load your data into an array, process it in memory, and write everything back to the sheet in one go. This speeds up your process from minutes (or hours) to milliseconds.

Culprit 2: Inefficient code design

Poorly written code often involves unnecessary loops. The most common mistake is looping through one dataset for every item in another. While this might seem fine for small datasets, it becomes unmanageable with large ones, making processes much slower than they could be.

Solution: Use dictionaries! Store values that need to be accessed quickly in a dictionary, eliminating the need for multiple loops.

For more expert help with optimising your Excel macros and VBA routines, get in touch today.

When the going gets tough, the tough rethink their business processes

You don’t need us to tell you that companies have ups and downs – we’ve seen a fair few in our time. So what does tough look like? It might be projects on hold, clients reducing budgets, or simply a struggle to hire. Over the years, we’ve learned to ride this out by utilising this unutilised time to think about our own business, and if you’re in a temporary slump, now is the time to think hard about how you can better your business.

Rethink your own business processes

You can afford to take your eye off the pipeline for a few days, however scary the concept is, and it will be hugely beneficial to use quiet periods to improve business-critical processes. This is something that we did earlier in the year, and I’m glad to say it made a difference. Here are some things we learned that you can borrow from.

Improve your time tracking and time management

Lofty statement incoming: Time tracking is one of the best things you can implement to help with revenue, forecasting and reporting, particularly in a business where labour is the main cost. Time is your biggest resource, and if you can’t see where it’s being spent, you have poor visibility of your business. Worse still, you could be losing money.

The trick is implementing thorough time tracking without your people feeling they’re being watched. In our case, that meant explaining the rationale and importance:

Ensuring hourly paid work is being categorised correctly and charged (at least 5% of our billables weren’t getting billed)
Assessing the profitability of fixed-price projects, service retainers, and client accounts
Improving the accuracy of project estimation in future

Improve your processes

With the joyful arrival of twins last year, and as a director in the business, Nic made an effort to not only improve time tracking to see where exactly his time was going but to use that data to identify ‘time sucks’ and “eliminate, automate and delegate” as many as possible (read more about this in a recent blog). In short, we made the business more efficient.

In the long run, this reduces opportunity costs, creates better working practices, and can be applied across the business and even to other members of staff. It also means that you can review and re-review where your time is going whenever you want. And then make any necessary adjustments.

Founders and business owners, don’t be afraid to track your own time. You may be shocked to discover where it’s going.

Review your finances

Cash is king. Do you have enough stashed away to weather a storm? How do you know?

Quality forecasting, monthly reviews, and developing a set of KPIs, a business dashboard or a scorecard are all essential to understanding your business and what levers you can pull to ensure long-term viability.

This doesn’t necessarily mean cutting costs and calling it profit. Short-term profits might seem tasty, but you have to keep an eye on the long-term profits too.

Understand more about your customers

You can build a company from the inside out and it might look and feel great to you and your staff. But what about your customers? It’s also beneficial to build from the outside in. Listen to what your customers or clients are saying and use it to instruct practices, pricing models, communication and marketing. They are a direct conduit for feedback, and many businesses completely overlook this.

Don’t forget your network

Networking is no longer standing around in hotel function rooms with a “My name is” badge, questionable coffee and lukewarm croissants. Networking can be WhatsApp groups, walks and talks, online communities, and so much more. It’s important to remember to network, not only for new business but for new partnerships, too. Businesses and business leaders often help each other out, whether that’s with relevant services, shared ideas or just a shoulder to lean on. Get out there!

Revisit and optimise your service agreements

We see many organisations applying a “set it and forget it” mindset to SLAs. Ie: “It’s always been this way.” Well, it doesn’t have to be. Review your SLAs periodically, and specifically, put a formal process in place that helps you calculate exactly what they’re costing you and where the money’s going.

We built an SLA retainer model that takes into account time for planned and reactive maintenance, client communications, desired response times, and small feature requests. It enables us to give transparency to clients so they can see how we’ve calculated our SLA retainers. And by plugging these budgets into our time tracking, we can identify whether we’re delivering value.

While most of our SLA retainers were about right, unsurprisingly, some were unprofitable. Others were revised to work a little more in our clients’ favour, delivering them more value for money. It’s a rare but pleasant day being able to go to a client and offer them more for less!

But, your mileage may vary. You may find that you’re overservicing your clients, or at the other end of the stick, you’re not giving them enough, and perhaps that’s why they’re taking their business elsewhere. It’s all about providing better value and having the data in place to help you understand exactly how to do that.

From there, you can make the bigger, broader decisions. Perhaps you stop doing fixed-price work. Maybe you completely overhaul your SLAs for the first time since you started the business.

Either way, this is about both long-term customer retention and business profitability.

Pay for some advice

Yes, money may be tight and you may feel like you can’t afford to. But the question is, can you afford not to? Sometimes, you can’t see the wood for the trees. We invested in great people to improve our business, and while they are all very separate in what they do and deliver, and none of them are remarkably expensive, their collective efforts across the business are significant. In our case:

Fractional CFO: Frank chat about numbers
Marketing guy Ben: For digital content and things
Ad guy: For targeting and pulling in leads
Brilliant and Human: Helping us love our clients

In summary

So, let’s recap. If you do find yourself with quiet moments, put them to good use. Turn the focus inwards, if only for a few days. Spread that over a few months, maybe. It’ll quickly add up and help you tweak what needs tweaking. It might even open your eyes to a problem that’s been right in front of you the whole time.

Whether you apply automation, integration, an improved set of tools, or an entirely new application, it’s all part of the bigger story of building a better business and making business a breeze.

Sound like something you need?
Give us a shout. We’d happily work with you to make improvements to your organisation.

Plus-addressing: Get more email addresses without adding mailboxes or aliases

Digital transformation isn’t always about the big stuff. Sometimes, there are “quick wins” that are relatively small in their execution but huge in their effectiveness. One such example is creating multiple email addresses from your existing email address simply by using the “+” symbol.

For example, you might do your day-to-day email through yourname@yourdomain.com, but you could add yourname+invoices@yourdomain.com. This creates a “virtual” address, but the emails still reach your regular inbox. Why would you want to do this, and how’s it different from an alias? Let’s take a quick look:

It’s great for tracking

In the age of spam and cold emails, sometimes it’s helpful to know who or where they’re coming from. Of course, this isn’t MI5 levels of espionage, but it provides a pretty reliable audit trail. For example, yourname+web might suggest that the email address you have on your website has been scraped. Or yourname+newsletter would suggest that something you’ve signed up to has shared your data with a third party. Naughty.

It enables multiple logins with one email

If, like us, you need access to admin consoles, back ends, portals and such like, there is often a need for several email addresses for different user types, such as admin, manager, employee and suchlike. Here, you can simply use youname+admin, yourname+manager and yourname+employee. This means you can log in with different user permissions without creating three different email addresses.

It’s quicker and easier than aliases

Aliases are brilliant, and they certainly have their place. However, not everyone knows how to create them, or people forget. We often walk clients (or each other) through the steps. On a “quickly do this” level, the “+” trick is far more efficient and straightforward.

It can save you a lot of money

Similar to the multiple logins tip, we often find a need to create multiple emails for testing purposes. Using yourname+test1, yourname+test2, yourname+test3 and so on can save you from having to create additional seats or emails within your workspace.

It’s suitable for search

Ever lost an email? Or have you found yourself frantically searching your inbox using one keyword after another? Searching for “+newsletter” or “+invoice”, for example, can quickly narrow down your search. Better still, you can set up smart rules or actions to put emails to said addresses into their own folders.

This clever little trick is widely supported, so whether you’re using Google, Microsoft, or something crazy like Lotus Notes, you’ll have no trouble implementing it.

We love little hacks like this. They’re super satisfying, and while it won’t seem like much at the time, you’ll thank yourself in the future, and you’ll quickly see how much it improves time management and organisation.

If you’re a business looking for quick wins or larger digital transformation across your organisation, let’s talk!

Making good introductions: it’s all about etiquette

We’ve made and received loads of business introductions over the years and formed some opinions about what makes a good one for both sender and receiver. Here’s our take.

Introductions are a key part of new business for many organisations. According to a survey from Smart Insights, referrals make up 65% of B2B business leads. More recently, research by Finances Online suggested that referrals as a lead source grew by a staggering 425% during the first wave of lockdowns during the pandemic.

So whatever your sector or industry, it’s highly likely that you’ll be intro’d to a new contact by email, or you may intro one of your contacts to another. All being well, it could be the start of a positive working relationship. On the flip side, some introductions can slip through the cracks.

Maybe the email wasn’t particularly clear, or you left it an embarrassingly long time to reply and ultimately brushed it under the carpet. This is where etiquette comes in, and with just a few simple steps, you can up your introduction game and turn them into referrals.

Sender etiquette

No one likes an unsolicited introduction

If you’re connecting people, you should first of all check that each party will welcome the intro. No one likes an unsolicited introduction. If one of the parties specifically requested the intro, then half your work is done – but don’t half-ass the rest of it!

Contact each party individually laying out what introduction you plan to make and ask whether it’s of interest and if they have time to take the connection. The time part of the query is key – it shows you respect them and if the intro is not for them, it gives them an easy out and everyone saves face.

Everyone on the same page?

Checked both parties are up for the intro? Great!

We’ve probably all had some unexpected connections, and the conversation gets a little awkward if it’s not right for you, or you don’t even understand why you’re receiving it.

Keep it concise

Write a short email cc’ing both contacts, and introduce them to one another with thoughtful details a la Bridget Jones. One sentence each usefully suffices. I find it helps to repeat the reason for connection and the relevance of the ‘connectees’. If you can’t think of what to say, is it a good connection at all?

Receiver etiquette

Time is ticking – follow up!

Sometimes intros can catch you off-guard. Let’s assume the sender has at least prepped you that it’s coming, as above, and maybe when it comes in you’re time poor or knee deep in something else. In any case, it’s polite to reply within a day, cc’ing the introducer and acknowledging the connection. This at least lets your sender know it’s been actioned and initiates contact with the third party. There’s nothing worse to a thoughtful introducer after making a warm intro, than wondering whether the two parties have in fact connected successfully. Want other intros in future? Best say thanks.

Booking in a chat

You can use your acknowledgement email as a buffer to buy a little time, and it’s a good opportunity to suggest a call, or promise to follow up ‘properly’ at a better time. Just don’t forget. Better still, set yourself a calendar reminder. Personally I like to offer a direct link to available slots in my diary and let the ‘connectee’ pick and choose.

I’ve encountered various cool apps for booking calls:

Microsoft Bookings comes as part of your Office 365 subscription will schedule an MS Teams call for you
Calendly does a nice job too and connects with Google, Outlook, Microsoft Office and iCal
You Can Book Me is also pretty neat

Master the art of the one-time-only ‘reply all’

Stop hitting reply-all after the first acknowledgement – no one needs to be looped in after the ‘thank you’. If you want to avoid reply all altogether (as there’s alwasy the danger of leaving the sender on CC for the next 500 emails), then simply reply directly to the sender acknowledging the email, and then send a separate message to the third party. This technique, whilst a little more direct, does save you getting into a three-way chit-chat which could slow the conversation down.

Congratulations, you’re now an introduction pro.

Honestly, it does sound very basic when you break it all down like this, but as we said at the beginning, we get busy, things can slip off our radar, or we can misjudge a message, whether reading or receiving.

What’s important is that a relationship starts on a good foot. Much of our work is optimising our clients’ customer journeys, and that initial contact is where it all begins and where first impressions are made.

Get it right off the bat, and good things will follow.

You’ll be great, you just have to go for it!

Home working and enjoying it – a practical guide.

Build custom business applications in Excel with UserForms

Person using an Excel UserForm on computer

UserForms are an incredibly powerful data capture tool.They are basically boxes which help you ensure that anyone putting data into your spreadsheets understands exactly what is needed in a simple, easy-to-use format.

This protects your data integrity by ensuring that the information that people supply is:

Put into the correct cells
In the correct format
The full set of data required for the spreadsheet.

UserForms also reduce the risk of inexperienced users accidentally damaging the functionality of the spreadsheet.

In short, they help you control, protect and manage your data. A well thought out implementation, will enable you to achieve sophisticated, database-type functionality, from within an Excel workbook.

How UserForms make Excel more user-friendly

Unless you’re an experienced user, spreadsheets can be daunting and it can take time to get to grips with how they work.

Even more experienced users can find complex workbooks confusing and this can lead to mistakes that can totally undermine the integrity of your data and lead to serious errors. UserForms provide an interface between the user and the spreadsheet which helps remove that risk.

UserForms are incredibly versatile – allowing you to create interfaces that are really easy to use. They can feature drop-down menus, responsive functions, command buttons and a host of other features to make them simpler and more pleasant to use than entering data directly onto a spreadsheet. There’s a wide range of styles and designs to choose from to improve the user experience and you can even use pictures, when necessary, to make the process even easier for the user to understand.

Designing effective userforms in Excel

a real art to doing this well. UserForms can be as simple or as sophisticated as you need them to be and some good VBA code behind the scenes will do all the heavy lifting for your users, making business administration a breeze.

Key factors to consider will be:

Data structure – your underlying data will still be sitting in a spreadsheet, so getting this well laid out will make it easier to link together and report on.
Data validation – or checking the form for input errors and giving users clear feedback on what they need to correct before the form saves data
Use cases – considering what tasks a user needs to complete will instruct the different components you need to build into the form
Optimisation for speed – if you’re working with large data, making your forms quickly searchable will pay for itself many times over

So what’s the catch?

There isn’t an obvious one. UserForms can deliver an extremely cost effective answer to the data management needs of many SMEs. They can drive efficiencies and mitigate risks. They will make you and your employees more confident about the data you are handling.

If you need multiple users to access your spreadsheet at the same time, then you might be stretching the bounds of what can reasonably be accomplished with an Excel tool and it could be time to consider alternatives, like a web application.

Excel UserForms have the power to transform the way you use spreadsheets

Well-designed UserForms can effectively turbo-charge the way you operate your spreadsheets and, ultimately, your business. As we start to exit from lockdown, digitisation and automation will be key factors in developing more efficient business practices.

Embracing them, will help you take advantage of the return to normal and deal with the associated upturn in operational demands.

We have worked with a number of clients to develop sophisticated UserForms and created software and algorithms that empower them to use the data collected to maximum effect. We’d be more than happy to chat with you about the difference UserForms could make to the way you run your business. Check out some of our case studies or get in touch for discuss your needs.