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.