This example shows how to set up a spreadsheet to record sales in a small café. The methods could be applied to many similar situations and scenarios.
The basic spreadsheet is as follows:
Columns A to F record the sale of each item, columns H to J hold the details of the items sold and columns K and L contain some analysis of the sales. More detailed analysis of the sales is done in a pivot table.
The user will enter the code for an item in the next empty cell in column A and the spreadsheet will do more or less everything else.
The item in column B is not entered by the user but is looked up in the table of item details in columns H to J., which reduces the need for user input. The VLOOKUP function is defined as follows:
=VLOOKUP(A3,$H$2:$J$10,2).
A similar instance of this function is used in the E column to look up the price of the item entered:
=VLOOKUP(A3,$H$2:$J$10,3)
The functions are almost identical except in the last parameter. The first parameter is the cell where the lookup item is found; the second parameter is the area of the spreadsheet where the item's values will be found (the lookup table); the third parameter is the number of the column in the lookup table where the required data item will be found. Thus for an item with code 1 the B column contains 'Coffee' and the E column contains '150'.
Note that the item to be looked up should be in the first column of the data range. In this case the lookup value is in the A column (=VLOOKUP(A3,$H$2:$J$10,2) and the corresponding value is in the H column. If the corresponding value is in the second or subsequent columns the function will not find it.
The date in column C can be entered by a user in a single cell at the start of each day's trading and copied and pasted into the cells below as the items are sold. (It is not possible to use =NOW as this will change the dates each new day that the spreadsheet is accessed. Simple drag-copy cannot be used because this will produce a sequence of dates.
The quanity of each item sold must also be entered by the user. The total value of each sale is calculated from a simple formula:
=D3*E3/100
The price of each item is given in pence so the total cost is divided by 100 to convert it to pounds. This column is formatted to currency.
This is a useful point to introduce validation in Excel spreadsheets. Validation can be used to check that the value entered in a cell is within the range required. In this cafe example there are just 9 codes for items on sale so we should make sure that the user only enters numbers in this range. To do this select the cells where you want validation to apply (the A column data cells) and then choose the Data tab on the Ribbon and Data Validation. Complete the dialogue box:
Choose from the various data types and complete the data ranges as required. The Input Message tab allows you to set up a message to direct the user before data are entered - this appears as a tool tip.
The Error Alert tab allows you to set up a message for when the user enters incorrect data.
To prevent users altering cells that do not need to be changed manually you can use the cell protection facilities. Take the following steps:
Now the user can only enter data in the A column, which is fine because the spreadsheet does the rest. This makes the spreadsheet safe from tampering or accidental changes. The owner of the spreadsheet can remove protection to make adjustments.
The SUMIF function is used to find the total value of items sold in the sales part of the spreadsheet:
=SUMIF(A3:A1000,1,F3:F1000)
The function looks in the first range, the A column, for the criteria of the function, 1 (coffee) and then reads and sums the values from the second range, the F column. This is one way to find the sum of various items, another, possibly better, way would be to use a pivot table.
The data in the sales columns is ideal for analysis by a pivot table. Select all the items in columns A-F from row 2 to the last one that contains data and use Data/PivotTable and Pivot Chart Report to create a pivot table. The example below shows a typical report:
This shows the total value of sales of each item for each date in the data.
It is not possible to make a pivot table in a protected spreadsheet, protection must be turned off first.
This spreadsheet is developed further in the section on Visual Basic.
Use the Back Button to Return to the previous page