This all stems from the intended use and flexible nature of Excel. It is, after all, a spreadsheet engine. Indeed, there are other applications better suited to such tasks — because they were designed to do them. There are general-purpose database applications available, with which you can construct a bespoke solution. Alternatively, you may find that a specialist database application — one that has already been designed for the purpose you require — is cheaper, faster to implement and a better fit.
For instance, if you have a list of customers and details of all your interactions with these customers, that could be considered to be a customer relationship management CRM system.
Despite its fancy name, a CRM system is simply a specialised database. Similarly, accounts packages such as QuickBooks and Sage are specialist databases. The most common database type is a relational database. This stores its data in tables, which consist of rows and columns of data. What will the population be in ? The following Excel spreadsheet calculates Q every five years.
From this excel worksheet, you see that for the year the population will be This is an astonishing number, but there are many factors that will affect the population that are not accounted for in this formula. For example, this number does not account for the deaths of this species bewteen these years. Anyone can build. Introduction Spreadsheets are incredibly powerful tools. They may be used for much more than just calculating a total or average. The following spreadsheet problems are spreadsheet program agnostic.
That is, they can be solved using any spreadsheet program Eg. Solving interesting spreadsheet challenges is a great way to develop your spreadsheet ability and problem solving skills. It is also a lot of fun and can be very fulfilling. If you think a particular challenge is interesting but a little too difficult, or not difficult enough, then maybe you only solve part of the problem, or think of ways to take it even further.
Keep in mind too that spreadsheets can be used to automate a great many tasks, saving you time and effort. As you're working through the challenges, think about how the things you are learning could be applied to other problems you would like to solve.
In the Functions to consider under each challenge a number of useful functions have been listed. These are just suggestions and it is possible to solve them using different functions depending on your approach. Challenges Two Thirds of Average There is a game where you have to guess what two thirds of the average of all guesses will be. 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 Data Comparison. 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 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. 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 Diary schedule. Do you have a database of all of your current jobs? 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? 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 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! 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 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.
I often get called by someone telling me that they will charge us less per month for gas and electricity. 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. 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 Invoice tracking. 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. Even further, with a bit of VBA code, you can have invoices uploaded to the database at the click of a button!
Not really, but it looks impressive. Job cards 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!
How do you do this you ask? Diary schedule Diary schedule. Job done! Zoho charges per user per month, but limits the number of records you can store for that price. Did you make money, was it completed on time, what errors were made? 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 just goes to show how Excel can be used and adapted to solve just about any problem. In the Functions to consider under each challenge a number of useful functions have been listed. Quotes Quotes. To learn more, click Optimization Problem Types. Read more about:.
We have some standard solutions in our store which are specifically made for charities, so take a look at those. Expense control Expense Control. It is, after all, a spreadsheet engine. When you find a solution that you require, us the links at the bottom of the page to find your solution.
Is the average sum of the two dice close to your prediction?