IT SPREADSHEETS Essay, Research Paper
Introduction Spreadsheets are used in all aspects of financial computing. Shop owners, Company chairman and directors all need to know how their businesses are doing and the main way to do this is by using spreadsheets.
Spreadsheets are computer models that simulate a real life scenario using data that has to be input by the user. On this occasion, the data is to be for finance; profit/loss figures, so the user has to input financial figures for the spreadsheet to calculate and show what the future holds for the user and their company. There are also labels that are texts with no numerical data to separate the actual financial figures from other numbers.
What The User Needs
The user of my spreadsheet is a sport shop owner who needs to collect and distribute data to predict capital income for the future. They need to be able to see the profit/loss of their company and how, if the input into the spreadsheet is altered, the output is changed also.
My spreadsheet is a model that simulates the real thing. As a shop owner, the user should be able to record the various categories of income and expense and decide their course of action depending on how the store is doing on the spreadsheet. It should allow the user to make comparisons of profit/loss for that particular week, month or year.
However, I will be going through this product as though I were the user and make references to the actual user of real life at appropriate times. I will be presenting the worksheets over a time span of a month so as not to make it too complex to understand.
Data Needed
The data needed for my spreadsheet is:
·The Names of all my products
·The Prices of all my products and how these change depending on the VAT and wholesale price
·Profit and Loss figures made from subtracting expenses from income
·The Stock figures of each item
·The ‘Amount Sold´ figures for each item
·The amount of VAT on each item I will obtain the primary data (e.g. prices of products) by making it up but I will not make it too extravagant or extortionate by making it similar to prices that I have seen in shops similar to mine. The secondary types of data (profit etc) will be calculated by obtaining information from the primary data using various formulas (mathematical equations used to calculate) and functions that I have stated below. Plan Of project
I am going to make 5 worksheets that are to be laid out as shown on my written plan by hand. One is for income and the various categories that go with that. The others are for expense, Wages (a detailed part of expense), graphs and discounts, (which are again too detailed to be fully fitted on to the expense part of the worksheet).
Sheet1 will be the income side of my spreadsheet, Sheet2 will be the wages side, Sheet3 will be the bills (expense), sheets4 will be the discount part and 5 will be used for graphs.
I will explain each aspect of my spreadsheet and all of the functions and formulas that I used with it. The spreadsheet is designed to give data for one particular month rather than weeks or years which are too complex to present. The functions that I am going to be using are:
·=VLOOKUP
·=IF () for the stock warning level (where stock is reordered automatically), discounts and wages and publicising items.
·MATCH Function
·ABSOLUTE AND RELATIVE CELL REFERENCING
·MACRO Function-I will use this to link the worksheets together and this will be explained in detail later on.
·MIN and MAX functions to work put what products are doing well and what products are not. This will help me to find put which of my products is the best and worst over a specified range.
·AVERAGE formula to work out how well I am doing on average for the profit of my company.
Setting Up Grids Onto Excel
I now was to put all of the information that I had drawn by hand for the income aspect of my spreadsheet on to Excel. This is how I done so:
The first part of inserting my spreadsheet on to Excel was putting in all the various forms of information such as the headers and the first parts of figures for my items.
Next I would go on to add the rest of the necessary information that can be seen in the screen dump below. This was to include the total profit I have made from my income for the month and other things like Publicising that will be explained further below. Here is the second part of inputting my spreadsheet onto Excel:
The things that I typed into each cell heading containing a formula were:
·D1=B2*O.175 (wholesale price*VAT)
·E1=C2+D2 (Retail +VAT)
·H1=IF(SK<50, “REORDER”, “OK”)
·I1=E2-B2 (Selling Price – Wholesale price)
·J1=G2*I2 (Sold*Profit Per Item)
·K1=IF(SOLD<20, “PUBLICISE”, “OK”)
I clicked and dragged to the bottom of each column to all of the formula headings above. The reason they all worked when I filled them to the bottom was because of RELATIVE CELL REFERENCEING where the cell references change accordingly to whichever cell you fill to e.g. If I fill from cell A1 to A5 and the formula is =G2*E2 it will become =G7*E7 in cell A5.This is what relative cell referencing does, it changes the value relative to the cell it is being transferred into. The income part of my spreadsheet included many things. The wholesale price of each product was included using similar figures to that in real life. The retail price is included along with the selling price, which can only be made by adding VAT, which I did by multiplying the retail price, by 0.175. (17.5% is the actual VAT price).
The stock is included along with the amount of each item that I sold and it is here that I used my first IF function for the stock warning level that is commonly used for large firms. The IF Statement
The ‘IF´ statement is a function frequently used by shop owners that transfer their financial data on to worksheets. It allows them to change different data automatically by typing in a function that renames or refigures the information in the cell that the formula is being typed into. The formula in H2 is: =IF(stock<50, “reorder”, “OK”)
The IF function used above changed the data in my stock warning level by showing the relative cell (in comparison with its item) with the label “Reorder” where it needs to be reordered or the label, “OK” where the amount of stock is above the warning level and thus does not need reordering.
I included the MIN and MAX functions to let me know which of my products sold the most and the least. This is important for the user as it tells them the performance in sales of their products and what decisions have to be made about them.
To calculate my profit per item (the amount I made on each item), I subtracted the selling price from the wholesale price as this is the price bought my products from in bulk and taking this away from the selling price leaves me with my income for that particular item. To calculate the total profit that I have made for the month used the SUM formula to calculate the sum of all of my profit on each item:
The SUM Formula
The SUM function is used to calculate the total value over a set range. The range could be a column, row or even in a variety of places where commas would be used to show what particular cells you want the model to calculate. The formula in I15 is: =SUM(J2:J12)
Publicising When designing my worksheet I thought to myself, What if a product sells poorly in real life; what does the storeowner do? And, apart from put a discount on it that is only done to products that do not sell well due to environmental issues, they publicise the product or advertise the entire store.
I decided that publicising a product could not be put into the income or expense side of my spreadsheet, because it costs money to publicise a product and the long-term benefits, which are basically an increase in sales, balance out the money paid to publicise plus add a little more. One could say that this benefit means that publicising is something that increases income but publicising does not always work and when it does, you can never be sure about how well it will do and whether or not your sale productivity will increase by the amount that was planned following the decision to publicise.
The product that has been publicised would then be made into posters; there would be things like radio broadcasts and T.V commercials-just like real life publicity for a certain product or brand name.
Therefore, I decided that if any of my products sold below 15 for the month, then I would publicise them and I did so by again using the IF statement to get the public interested in buying them and thus increasing sales of that particular item. This was also the reason that I included Publicising in the Income side of my spreadsheet because although publicising costs a fait amount of money, the money that would be made back would more often than not level the profit/loss out. The formula in D2 is: =IF(sold<20, “Publicise”, “OK”)
So the final worksheet for my income looked like this:
The total income I made for this particular month was £14,280,08pThe expense side of my spreadsheet was quite small. This was because I am only covering one month of progress and so transferring data such as bills and rental costs is a small part of the worksheet. The wages and discounts are much larger and thus have been done on separate worksheets, which will be explained later.
The screen dump below displays the three main types of bills and the cost I am paying per month for each of them:
The bills are important for any one who owns a shop. The amount of gas, electricity and rental bills are all variables and thus need to be monitored carefully. The gas and electricity bills are just like domestic bills and are paid in accordance to how much they were used during the month. The rental bill is for the actual shop where the goods are sold as this a rented space and thus money has to be paid each month the leaseholder.
Wages are an imperative aspect of any store. The storeowner cannot run their shop without workers and these workers will not work unless they get wages. Therefore I have included a wages aspect to my spreadsheet to make it more accurate to real life.
I have included overtime, hourly rate and the amount that the workers get per month, which the total of will be added to my other expenses and subtracted from my total income.
The wages aspect of my spreadsheet is a very detailed one. I gave the employees wages that were neither too high nor low and they were given bonuses according to their responsibilities.
The responsibilities of workers in a store are things such as the manager, assistant manager, sales person, cashier, shelf stackers, backroom staff, accountants for the financial aspect (not all stores have this though) and cleaners. The higher the responsibility (R8) is the ‘manager´ type jobs who gets a higher bonus and a higher hourly wage and R7 is the ‘assistant manager´ type jobs who gets a slightly lower bonus and hourly wage. This descent in bonus and wages continues to the bottom of the responsibilities (R1), which are the cleaners of the store more often than not.
To calculate the responsibilities and bonuses for the workers, I used the VLOOKUP function. The VLOOKUP Function
The VLOOKUP function is a function that looks up a value in a separate table (known as the ‘Lookup´ table) and returns that value to the cell of the table you are typing the formula into. It is very similar to the IF statement but is much more versatile which is why I have used it for both discounts and wages.
The VLOOKUP function that I used could not work unless I inserted a separate table called the LOOKUP TABLE. Then in cell F2 I typed in the formula:
=VLOOKUP(E2,A10:B18, 2,FALSE) The value we are
Looking up
However, when I filled this formula down to cell F8, the formula changed for the lookup table reference from A10:B18 to A16:B24. Therefore, I had to change my lookup formula using absolute cell referencing. I went to the formula bar and pressed F4 twice to the two values of the lookup table. I now filled to the bottom of the column again and this time the values were correct. My new formula now looked like this:
=VLOOKUP(E2,A$10,B$18, 2, F
This is what the new formula looks like. There are now dollar signs between the cell reference for the lookup table. The absolute cell referencing now means that the cell with the value we are looking up changes when I fill, but the reference for the LOOKUP table does not.
The advantage here is that not only can the employees wages be changed easier, the responsibilities them selves can be changed as well without having to change the table reference (e.g.R1 could become £100). This makes my spreadsheet much more efficient because now, if the input is changed, the output is changed automatically.
I gave each of the workers bonuses depending on how many items they sold for the month. This is commonly known as commission however; they only get a bonus if they sell over a certain amount of items for that particular month. I used the IF statement in order to do so and in cell I2 I typed in the formula: =IF(SOLD>150, “BONUS”, “NONE”)
To calculate the total of the wages, I again used the SUM formula but it was a much lengthier one due to me having to include all of the types of data and adding them together: =SUM(B2*C2)+(D2*B2)+F2 This formula hel
Discounts are another very important aspect of any store. There are many reasons why items are given discounts such as there being a fault with an item or more commonly, they are just not selling well. Therefore, if wide ranges of items are given discounts, the shop is literally having a sale. However, not all items are given discounts and those that are, are not given equal discounts. Often, the item that has sold the least is given the highest discount and vice versa for the item that has not sold that badly.
Thus in my discount spreadsheet, I have included the names of the customers and the items that they are buying with the discount. Again the VLOOKUP function is used so that I know which item has what type of discount:Keeping the names of the customers with complaints in a database is something often done by stores so that they can target new item at that person. This is known as market research and comes in many forms. However, this is a very complicated aspect of store which I will not be explaining until my evaluation.
The formula that I typed in cell E2 was: =VLOOKUP(E2,A$10:B$18,2,FALSE)
The VLOOKUP function calculated the discount of each product where 75% means that the customer has to pay 75% of the actual price. It does not mean 75% off! I filled down to cell E6 and because I used absolute cell referencing, the formula worked throughout
Every spreadsheet that contains financial data usually contains a graph as well. Graphs help us to visually recognise how their business is doing. The uses of line graphs are common to see any rises or fall in business. And it is these types of graphs that I have set up to show me both the amount of profit each item has made me, and to see which of my workers has been working hardest this month.I set the graph up below by comparing the amount of profit I made with each item. I highlighted the relevant data and clicked on the Graph Wizard toolbar icon to select and show my graph.
I used a line graph as it could show me any steepness or drops in the profit for an item and if I were a real storeowner, I would like to know what items were selling well, and what items were not. This enables me to put sales on the items that are not selling well (or not order too many of them) and raise the prices a little for those items that are selling well.
The graphs have been labelled accordingly on the X and Y-axis with a title being added to tell the user what the graph is actually showing.
This graphs shows me which of my workers (those who have the responsibility to sell hence the removal of A.COLE from the graph) have been working hardest this month and have sold the most items.This graphs shows me which of my workers has sold the most products for the month. This is very useful to real life storeowners because it allows them to monitor the progress of their employees and make decisions to sack them, promote them or give them a rise in their salary. It all contributes towards motivating staff, which I´m sure all storeowners and management staff want to do.
From the graph I can see that R.Giggs is the hardest worker of the month and if this workmate continues, bonuses and promotions could be in place as I explained earlier.
My third and final graph is one that compares the selling price of all of the products with the amount of sales they have had. This type of graph is extremely informative in that it allows the user (in this case me) to see if the prices of their products affect their sale and whether or not these prices can be reviewed to increase the productivity of sales. It is set out in columns as I felt that a line graph representing the same data was very complex and hard to understand at first glance.
In my graph, there are quite a few cases where the sale productivity is high especially when the selling price is low e.g. For tennis rackets and sweaters, the sales are considerably high due to their fairly cheap selling price. However, there are a couple of cases where the sales are lower than the selling price e.g. jogging bottoms, football boots and T-shirts.
Simplifying The System
To make the spreadsheet easier to use, I inserted macros with buttons that when pressed, will take the user from the worksheet they are on to the specified worksheet of the macro. But how do you record a macro?
Well, first of all, you have to select the ‘FORMS´ toolbar by going to VIEW then TOOLBAR. Next you click on the Create Button Icon and select the size using the cursor. Right click on the button to get a sub menu where you choose assign macro. Select a name for it and click OK. Right click again and assign macro again. This time, click on record and then click on the two cells you want to switch to and from. Once done, click on the top button on the sub menu that should have come up when you pressed Record. Your macro for creating a button is now complete! I selected a screen dump of the visual basic that records the steps you took to record your macro. The picture on the left is what the menu box looks like when you are to select how you wish to sort your data out and the screen dump on the right is the Visual Basic record of how you set up your macro.
What If…Queries There are many variables that can change for a storeowner. Some can be beneficial whilst others can be damaging to profit. In either case, the spreadsheet should be able to automatically re-calculate the output if the input changes. To demonstrate how, I have made some What If…Queries to explain what would happen in different cases.
What if the wholesale price of a product changes? This kind of problem faces storeowners everyday e.g. Foot and Mouth disease means a huge price increase in wholesale meat goods. In my case, I would have to raise the retail price, which in turn will automatically change the selling price due to the formula I used multiplying the wholesale by VAT and then adding this VAT to the retail price. My income would change for the month and in turn wages, profits and discounts would all change as well.
What if the rental costs or the domestic bills increased? Well for starters, the amount I am paying monthly would change automatically because I used a formula multiplying the monthly charge and the no. Of hours the gas or electricity was used. For rental charges, I would continue paying normally, but if I was in a slight financial crisis, I might consider increasing the prices of a few products to balance the money in and money out.
What if one of my employees sells below 5 products through the whole month? Well to put this on my spreadsheet I could use the IF statement which would show the word “Demote” if the employee sells less than 5 products for the whole month. This is harsh but is the reality of stores. Instead of demoting the employee you could sack them but more often than not, telling the employee that the profits are suffering due to their work works and no doubt the next month should produce a better profit for the store. To increase sales and productivity, the workers who are not up to the job must go and I believe that, in many cases, this would make the other workers work harder to avoid the same fate their co-worker faced.
Sorting Information Into Order Users of spreadsheets such as this often want to sort the information in the worksheets. They might want the Income sheet for example, to show in descending order the most profitable they sell or they might want to see which employee works the most hours to give them a raise. Therefore, I have decided to sort the information in the income worksheet to show what it would look like for a real life user.
To do this, I will highlight the table of data, then click on Data and select Sort from the menu that comes up. There will now be another sub-menu where I can choose how I want to sort the data.
As you can see in the background, I have already sorted the data and you can see that on the profits column, there is a descending order from highest to lowest from the figures.Safety and Making Backups Of Work
Safety is among the most important aspects of a well-designed spreadsheet. To make back up copies of your work is priceless and I made absolutely sure that I always has a spare copy of work by not only saving it to different formats of my PC (i.e. My computer, My Briefcase), but saving it onto two different floppy disks, one main one and one master copy. I kept a saved version of it at school in my username login area as an extra precaution.
To keep my spreadsheet safe from others (i.e. anyone messing it up or changing data) I locked the cells by going to Tools, Protection and protecting the sheet. Here I put in a password that only I knew. I then tested my protection by trying to press a letter into the first cell and a menu box came up as can be seen below.
Checking My System And Data for errors And Accurate Work To check my system and data for errors, I scanned my floppy disk containing the data every time upon use and used the program Norton Antivirus 2001 to automatically scan my system and data every time I started up my computer.
To make sure my work was accurate, I simply used a calculator to check if the data was right for the SUM formulas and other functions. I checked up on all of my formulas in the help files to ensure they were correctly written as well. Evaluation This project in my opinion has been a very successful one. I met the user´s need, which was my original and main target, and these needs were met with a sufficient amount of detail.
For the time allocated, I was satisfied with the way my spreadsheet was set up and indeed I used a variety of formulas and functions that were all relative to the work. These functions allowed me to set the spreadsheet to calculate data automatically in most cases, which is one of the main things I believed a user would need. I think that I could have made my spreadsheet better by making it more detailed in certain aspects such as the length of time I set the spreadsheet over. This could only have been achieved if I had had more time to work on the project however, so it would have been too hard to include a great amount of detail in such a short space of time. I could also have included a wider range of items on sale as the amount I had were really for a smaller type store who would more often than not leave the transfer of data on to a spreadsheet due to there not being enough data to permit doing so.
Doing my spreadsheet over a year rather than a month would have given the user a much better idea of how their business is doing. Only doing a month restricts their knowledge of the store and a yearly record would enhance this view and allow them to make future decisions under better judgement. I believe that I could have improved the discount aspect of my spreadsheet, as it is slightly out of sync with the rest of my work. The main detail for that particular worksheet was the names of the customers but again I could not have added such depth with the time allowed. If I were to do this project again, I would perhaps do a different type of store to allow me and the user to use a variety of new and different functions, as well as get a better outlook on how the productivity of sales varies with different types of stores. Overall, I have found this project a very interesting and intriguing one and am proud to say that I met the user´s needs and thus met the main target set out at the start of the work.