40 PROBLEMS SOLVED
People are not really concerned as to what we do with spreadsheets, they are interested in how spreadsheets can help them and their businesses. Over the last few years, we have documented some of the success that we have had when using spreadsheets to solve problems. This list started with 20 problems solved, but has now grown to 40.
Take a look at the list below, but first, sign up to receive our monthly email newsletter. When you find a solution that you require, us the links at the bottom of the page to find your solution.
Administration processes are far more complicated than they need to be. People spend hours altering old quotes, invoices, job cards and other templates, and then saving them as a new document. They then have various files saved in various folders and nothing is linked. Most of the time I find that people are too scared to use Excel, so all of these templates are done mostly in Word, which is fine but could be better. This method also means that you are entering information like the client details, job number, job details or whatever else you require, numerous times a day for however long the project lasts. You may be reading this and saying, “Yes, but that is something that you just have to do”. Well it’s not. An Excel document can have various tags, which means that you could have any documents that you require all in one file. This means that there is one file to save per client or project and that is it. No more copying, pasting and saving as. The other advantage of this method is that once information is entered, it will filter throughout the file, which means that information you enter will automatically fill all the relevant cells in all documents.
Appointment Management. So you have a few consultants, but who is available tomorrow at 10:00am to see Mrs Smith? Who has had the most appointments over the last month? If this is important to you, you may have this organised in your staff diary, but Excel has a solution. We have already created a multiple staff diary (which is in our ‘Prime Range‘ section), but we could take this process even further, we could use Excel to not only track the appointments, but then to provide us with relevant statistics afterwards. This is why we create custom spreadsheets as well as ‘standard’ ones, all of us have different needs and requirements, and we don’t want to ‘pigeon-hole’ your business.
Attendance roster. As networking groups and events are on the rise, we are getting more requests to monitor people’s attendance. We create a solution where you can list your members, set criteria for their required attendance, and then plan your events. Then it is just a case of ticking people off on the register as they attend each meeting, and the spreadsheet does the rest. It monitors attendance rates, personal attendance requirements, and produces various reports so that you are kept in the know. If this sounds like something that interests you, take a look at our store product example.
Budgeting seems to be something that is done at the end of a job and not during it. I have worked for so many companies over the years that look at the job afterwards to determine how much money we made, some even had to wait for the accountant’s report which could take months. I find this very strange when Excel has the capabilities of doing this on the go. Budget spreadsheets can be made to help you determine what to charge in the first place. They can then be tracked throughout the project, even keep you up to date as you go along. You will be able to pre-determine categories and then watch your budget for each category. Don’t you think it would be handy to keep an eye on the budget as you progress? I can tell you that it is extremely helpful.
Charities often don’t have a large budget for software, and we are good at creating solutions which are good value for money, so we often create solutions for charities. Not only financial spreadsheets, but for all sorts of applications. If you run a charity and need some sort of software, get in touch and let us see if we can help. We have some standard solutions in our store which are specifically made for charities, so take a look at those. If you need anything bespoke, we’d love to hear from you.
Charts and graphs
People and companies love charts and graphs. They always help people see exactly what the situation is. Whether it be financial or other, a project or client, graphs can tell you what you need to know very quickly. I have heard people say that they would love to produce a graph or pie chart for a meeting, but they have not had the time or expertise to create one. Either that or someone has spent hours gathering information and drawing up charts, just for a meeting. Excel loves charts and graphs. It is easy to take the data and transform it into a visual display which I have used many a time to dazzle clients! If you know which graphs you will need, they can be pre-programmed so that they are updated automatically as you go along. Perfect.
Client database. Excel is good for using as a database, maybe not as good as Access, but more useful if you need to use that data in another way. Having a client database is very handy, and having one in Excel means that not only will you have hyper-links to the websites and email addresses, but you can use that data elsewhere. This means that if you select the client name in another document, you could have all their details pop up automatically. How useful is that?!
Client updates. Seeing as you have read all nine points now, and doing all of your administration work in Excel like a champ, you will have all of your data available. This means that you can select certain information which you wish for the client to see. You can even select further information (from a table) based on the clients details. In other words, if the invoice says paid, you can convert that to an entire sentence or paragraph instead. Once you have this page created with information from the client’s file, it can easily be saved as a stand alone PDF sheet, which means that they will only see what you require them to see. This can be sent to the client as a simple email attachment! Saves you writing up a whole email to keep clients up to date, as you update you own information, the client’s information is updated automatically. When you want to send the client an update, just save as a PDF and send it off, simple.
We often get asked to create calculators to work out commission. Do you have various consultants, all on different commission for different products? Do you spend ages working out commission and then still risk making mistakes? If so, get in touch. Spreadsheets are good with financial figures and mathematical formulas, and these types of solutions are usually fairly easy, and very efficient in what they do. If you’d like to see a standard version of such a solution, take a look at our store.
Data Analysis. is the common theme here. Whatever statistics you require, whatever data you are entering, it can be analysed. Please don’t read these examples and think that this is all that can be accomplished by us using Excel, we can do so much more. Whatever you require, whether a large project management type project or a simple spreadsheet, we would love to help you. When it comes to data analysis, we at Spreadsheet Solutions are in our element when using Excel. We love a challenge and we very seldom do the same job twice, so please contact us to find out what bespoke solutions we can create for you.
Data Comparison. We have done various solutions which fall into this category, but one that springs to mind was a client who had people using ‘hot desks’ in an office. He had a CSV document of all of the entries and exits of all of his clients, but each client was on a different contract and were allowed access on different days. Comparing who was there when, and does that visit need to be charged as an extra, was a nightmare. One spreadsheet later, it takes him seconds to compare, and see exactly who he needs to invoice for extra days. Do you have sets of data which needs to be compared?
Data Extraction from a table of data can be very useful. What do we mean by this? If you have a list of clients along with various columns of data, including when their next visit is due, you can access that information easily. We can create a separate sheet, where you can insert the start and end date of a period, and it will show you all clients whose visitations are due during that period. This is ideal with other spreadsheets, if you want to find the relevant entries in amongst a whole spreadsheet full of data.
Deadlines and dates
Deadlines and dates. There are so many companies who have to keep track of dates, whether it be project deadlines, or delivery or installation dates. People make notes in diaries or even have dates written up on the ‘board’, but I have seen dates missed because they were focussing on a more important project and a smaller one ‘fell through the cracks’. Most people don’t know this, but Excel is very useful with dates. You can keep track of your projects, even have a countdown on the job page if you like. Excel can then display all of your projects in one location and even colour code it so that you never miss another deadline again!
Diary schedule. Do you have a database of all of your current jobs? No? Excel can create one for you, provided all of your jobs are on individual job cards created in Excel, but this is not what this point is about (I have covered a similar problem in another point). How convenient would it be to have all of the project dates in a calendar? You can get Excel to search all of the dates in the database and plot them on a calendar on the respective date. Even if the project goes over more than one day! How do you do this you ask? Well, that is my secret!
Did you make money?
Did you make money? Once your project is finished, it is usually a good idea to have someone go through the project to find out if it went according to plan. Did you make money, was it completed on time, what errors were made? All of this information is valuable to a company owner. Most companies employ people to do this analysis, but if all of this was done in Excel, the analysis can be done automatically. Using Excel can eliminate the extra man-hours to perform the project analysis, and it can produce statistics that are invaluable in determining the direction of your business.
Documents. Yes, even documents which have no (or few) numbers in them can be done in Excel. If you spend ages copying and pasting paragraphs and clauses from old documents to a new client, and then replacing all of the old details with the new, we can help you! We can have all of the clauses and paragraphs stored away somewhere, and then all you’ll have to do is fill in the client’s information and make the necessary selections. Excel will then select the appropriate paragraphs and clauses based on your selection and replace the sensitive information with the client’s particulars. Instead of hours of copying and pasting (and probably forgetting some changes) you can merely fill in the client-specific information and have the rest done for you. As long as there are strict rules and a standard lay-out, this will work like a dream!
Expense Control. Every month you receive a bill, every month you pay that bill, would it be cheaper anywhere else? Excel can be used to gather information about what you have spent on what, and can then be compared with other prices to see who is cheaper. Let’s use an example of one that we all scratch our heads over. Energy. I often get called by someone telling me that they will charge us less per month for gas and electricity. The problem with this is that I am not too concerned with how much I am paying, but I’d like to know what my usage is costing. You see we get billed a monthly figure and then we either fall behind, or store up credit, based on our usage. I personally used Excel to create a template to show me what I am actually using, and how much that costs. I am able to compare my actual usage to see what other suppliers would actually cost me. Comparing apples with apples is vital, as companies will try to confuse us into moving to them. Other expenses may be easier to control, but Excel can keep you on top of them. The spreadsheet that I created for the utilities is now available for free download from our facebook page.
Income and expenses
Income and Expenses. I know most people hand a huge pile of papers over to their accountant at the end of the financial year and make it their problem, but wouldn’t you like to know certain information as you go along. Most large companies will use an accounting software, but smaller companies usually don’t, so this can be a good alternative. You could have a simple debit and credit with some details and categories, which you fill in as you spend or earn. This means that at any stage you can see each individual category (eg. Sales income , marketing, office supplies etc) and how much you have spent or earned. This could also translate into monthly or annually reports and statements which will be very useful to you. It is also possible to keep track of a (rough) tax amount, so there are no surprises when you come to the end of the quarter or year and get the VAT or Income tax bill. These documents can also be passed onto your accountant, who may even appreciate the organisation!
Invoice tracking. This is usually a tricky thing if you don’t have a complete software package that track these sort of things. I have seen everything from writing on invoice copies and putting them in individual trays to trying to rely on memory. This is a fairly simple issue to solve with Excel, a database of invoices, amounts and what has been paid could provide you with valuable information like how much is outstanding and how much has been paid. If you have more Excel knowledge and you have the invoices generated in Excel, you could link the two so that if an invoice is marked as paid on the database, it will show up that it is paid on the actual invoice. That’ll impress clients no end. Even further, with a bit of VBA code, you can have invoices uploaded to the database at the click of a button! Magic! (Not really, but it looks impressive).
Job Cards. If you have a business where you require job cards, Excel can prove to be more than useful. Job cards are ideal for storing all of the information for that client. This also means that you can link various other documents to the job card, just like the other ones that we have discussed in this series, which means that you can get so much out by capturing so much less! This is a very good way of tying up all of the possibilities discussed in one place, meaning that you only have to enter each piece of data once, and it will feed all of the documents! Now that will save you time, money and effort!
Job success and forecasting
Job success and forecasting. We love these kinds of jobs, because they can really tell you so much about the success of your projects, and help you to forecast more accurately. One particular job which comes to mind, was a consultant who inputted all of his contracts, broke them down into contract type, and how long it took to be paid after the contract start date. We then analysed all of the data, so it not only showed him how successful he had been per project type, but then also predicted how his current new projects would pan out. These kinds of reports needn’t be overwhelming to give you some very valuable information.
Material lists. I have spent a good few years as a project manager in the cabinet industry, that meant working out so many cutting lists of boards required. I eventually created templates for each cabinet type in Excel. This meant that inserting the cabinet (overall) sizes, my templates would give me a complete cutting list for that cabinet! No human errors or silly mistakes. If you require a material list, and there is some kind of maths formula to work out what you require, Excel can do an amazing job with this! It saved me loads of time and effort, and therefore money. Whatever industry you are in, keeping track of what materials you require for a project, is vital. We have created a spreadsheet to keep track of materials, if you are making gift baskets or hampers, which can be found in our ‘Prime Range‘ section.
Plotting on maps
Plotting on maps. For those of you you use Excel will be thinking that the only way to get maps is to buy a plug-in (Except for Excel 2013 which has US maps). This is not the case. I used to work as a field sales representative, and I was in a city which I hadn’t been in for long so I wasn’t sure where anywhere was. I was also unsure as to which areas were closer to others in order to plot my route. It took hours in Google Maps and copying and pasting information to try and get instructions for myself for the following week. Waste of time. I got hold of a database of post codes with longitude and latitude co-ordinations. Mixing this with the cunning use of point graphs meant that I had an interactive client base. Select the client name, all the details pop up and the location is plotted on the map. How easy is that? That saved me hours every week! Please note that these are fixed maps and are used for location plotting only, you can’t zoom in to see exact directions.
Price lists. People love to do price lists in Excel as it conforms to what most people use Excel for, a few columns with details and prices. Some people even manage to format the price column in order to have it as £ or $ or whatever they require. Excel can do so much more than this, we are able to create various price options which can be altered at the click of a button. So if you have your cost price and you pre-determine various mark up percentages, you are able to see the retail price, trade price and special bulk price (if those are the categories that you want). You are also able to change the mark up for each one as you wish, which in turn will alter all of the prices. You could even take this one step further and link it to your quotation template (also in Excel), so you could select the items on the quote and state which price category that client is in, and the quote will generate! When it comes to prices, the sky is the limit, it is right up Excel’s alley.
If we had to pick our favourite spreadsheets to make, it would be a process tracking spreadsheet. All businesses have processes (or at least they should have), and those processes really need to be followed. You first need to define the process, then enforce it, then monitor and track it. Afterwards you need to analyse and report on it, so that you know how successful it was and how it may need to change. Often, all of that can be achieved from one spreadsheet. Think about what processes you may need to follow, and then get in touch. Most people think spreadsheets are best for financial reports, we thin they’re best for process management.
Product Validation. So you have many products, and they all have various material and labour costs attached. Some are more expensive and some cheaper, and you’re selling more of some and less of others. Sound familiar? Which product is best for you to sell? Which one is bringing in more money? If you would like to validate which of your products are better to have, then you are not alone. We have had many request recently for this kind of project, so we have made a generic store product which may be useful. If you need something bespoke, please get in touch.
Project Dashboards are very important for most businesses, the larger the business, the more crucial it is to have a project dashboard. What is a project dashboard, and why is it so important? If you have large projects on the go, it is very difficult to assess all of the data, to establish how the project is progressing. If you wish to stay in control of your business, you need to know whether or not you are on target with your projects. A typical project dashboard has sheets where you can input specific data relating to the project, as well as a dashboard sheet, where it collects all of the data and displays it in an easy-to-read format. This sheet usually uses all of the data to produce charts, percentages and bottom-line statistics, which will give you a clear indication of the progress (and the current status) of any project. If you are managing your own projects, this kind of system will help you keep in control. If you have others running the project, and you wish to see these reports to monitor their process, then this will be invaluable.
There are many cases where you need to ask some questions, gather the answers, and then report on those answers. Think of questionnaires to determine if someone needs your services, or maybe a 360 staff appraisal, or some kind of training manual. We have been asked to create a few solutions which have some questions, and then allow people to select an answer for each from a drop down list. The spreadsheet then looks at the answers, converts them to scores, and produces a report which explains what has been answered and what it means. The spreadsheets did all the work, they just needed the answers entered, and the criteria set. If you spend ages collecting answers, analysing them, and then producing reports, you can spend your time doing other things instead. Get hold of us and let us know how we can help.
Quotes. People spend so much time doing quotes and some of it is unnecessary. If your business is bespoke and each quote is completely different to the others, then it is a bit more tricky, but if you are working from a price list, it can be very easy. Let’s say you are selling carpets by the square meter, you could type in how many square meters, select the carpet type, and the price will work out automatically from the price list (See point eleven) which you have done already! There will be no more silly errors where you work it out incorrectly and undercharge. If you have labour charges or anything else like that, these can also be programmed in and be accounted for, it is all up to you.
Response Analysis from web forms is another valuable Excel use. If you would like the visitors to your website to answer some multiple choice questions, you can get the online forms to email you their answers. So you may get an email stating A, B, C, B, A (or whatever answers they give). We can create a spreadsheet that can read and analyse these answers. It can be made so that all you need to do is enter in the answers provided and it will create a sheet with charts and statistics relating to their answers. This can then be sent to your potential clients with specific information based on their needs. It will look like you have spent ages processing their information, where as in truth it will be almost automatic.
Sales figures. This is another popular function as it also plays to Excel’s strengths. Inserting all of the sales figures in as you go along, possibly even capturing the expenses too for smaller companies, but let’s stick to sales figures here because we like them more! Once they are captured, you could have various other information like, which salesman was responsible or what category of product or what mark up, the options are up to you. Once you have this data, Excel can do just about anything with it, who has sold what, what average mark up, which month was the best, with graphs and charts to show whatever you wish to know! This can be taken one step further and each salesperson’s figures can be extracted and the sales commission can be worked out, no more fiddling with a calculator!
Social media management
Social Media Management. Yes, we all use social media. You don’t have to look too far to find an article telling you how important social media marketing is. It seems like there are so many social media management companies out there, and they are all in business because all businesses need to have a presence in social media sites. If you have social media accounts, how do you manage them? Do you just send out tweets, status updates and comments and hope for the best? Do you actually know what is going on with your accounts? Now most social media sites provide statistics for your page, but I personally find that they don’t always give me the data that I require. We have already created spreadsheets to help manage your twitter account (available in our ‘Prime Range‘ section) and to help individuals get the most out of LinkedIn (below). We can even analyse your .csv downloads from your social media, take a look at our free download series of spreadsheets to analyse your social media platforms. What information do you wish to get from your social media marketing?
Staff Management is a necessary evil in most businesses. If your business requires staff, but not large enough to have an HR department, then you will have to manage those staff. Who was sick when, who has taken leave already, how are they performing, who is particularly important to your business, who can be promoted, what do your staff think of their colleagues? This is a mission with an HR department, without one, this could break your business. We have teamed up with an HR company and created spreadsheets to tackle all of these issues and more. If you have an HR department, or you are the HR department, take a look at some of the staff management spreadsheets in our ‘Prime Range‘ section. Simply entering data as it happens, and then having Excel collect all of that data and give you the statistics that matter, will prove an invaluable asset to you and your business.
Stickers. Stickers? Do you have jobs on the go where you think, I really could do with some labels for these products with the relevant job/product information on? Well, if you have the job/product information in Excel already, you can send that information to another page. Some simple adjustments to the formatting of that page, can line them up with available sticker/label sheets! Job done! This just goes to show how Excel can be used and adapted to solve just about any problem. You may have a particular hurdle to get over, let’s see how we can address it with a fresh approach.
Stock control. I started working for a company when I was younger, and assumed the role as assistant manager. I went into the stock room and was greeted with a pile of papers that the staff filled in with the details of what left the stock store room, but they were not captured anywhere. I created a simple spreadsheet that showed the stock, supplier, product codes, price, current quantity, minimum quantity, order quantity and upcoming jobs. With some clever formatting, formulas and functions, I had a comprehensive stock database that would warn me (by changing colour) when we came close to minimum quantity, and then again when we went under it. When we had a job coming up, I would fill the details into the upcoming jobs column. This means that when the manager came in and asked about a specific stock item, I was able to tell him how many we had, how many were planned to go out, and how many we had left (in theory). This was incredibly useful. Also, at a glance, I could see exactly what needed to be ordered to replenish the stock. So whether we wanted to do a large order (all stock approaching minimum order level), or a smaller order (only critically low items), we could see exactly what to order without actually entering the stock store room.
Teaching aids. Yes, it is possible to list out questions for people, and upon answering the question (or once all questions have been answered), Excel will inform them whether or not the answer is correct. It could even give them the correct answer if that is required. This is very useful for training companies or if you wish to set tests of any kind. I have done a few games which are available for free download to illustrate this function, please feel free to download them in our download section.
Time Control is of utmost importance if you work for various companies and charge per hour. We have created various spreadsheets for clients where you can capture time spent on a project, and you are rewarded with statistics, graphs and information relating to your data. How much time you have spent per client, how much you should have spent, how much time spent per day, week or month, approximate time still to spend, all statistics that can be easily formed from the data entered. Perfect for self employed people who charge by the hour and have various jobs on the go at once. We have a standard version of this in our ‘Prime Range‘ section.
Vehicle statistics. Do you have company vehicles? Keeping track of mileage, fuel costs and usage and when tyres, road tax and insurance is due, can be a nightmare. A carefully created Excel spreadsheet can keep track of all of those needs. Whether it is a single vehicle or a fleet, Excel can keep you on top of what is going on with your vehicles! In fact, we have already created a vehicle logbook (for individual use), which is available to download for free in our download section.
Venue productivity. If you have a building of sorts, where you use various rooms for events throughout the year, do you know which room has been used more and to what level of success? A client of ours asked us to create a spreadsheet where he could list all of his events, with a breakdown of costs and sales, and assign each one to a room. This meant that he could clearly see which rooms were being used more, and which were used as much as he hoped they would be. We could also create reports showing which events were more profitable, and which rooms they were in, thus showing the financial success of each room. It has helped him to see which events he should do more of, and which ones to maybe stop. Valuable information when you’re running a business.
This sounds like a strange request, but think about it. If you supply your clients with paper vouchers to redeem in exchange for services or products, how do you know what you have issued, what ha been used, and what it still outstanding? Do you know if vouchers are being copied? We have had a few clients looking for such a solution, and then others who want to issue vouchers as ‘taster’ sessions, who also had the same issue. We ended up creating a standard solution for this in our store, which is called the Voucher Code Database.
There are many other problems that Excel can solve, this is merely the beginning. We hope that this has inspired you to contact us! Alternatively, take a look at some of the solutions on offer below.