Order Management for really small businesses and freelancers
Download the sample workbook
OrderManagement.zip (29 KB created in Excel 2003)
This is a small sample workbook I originally created for my daughter who does little camera jobs besides university and needed something to keep track of her jobs and for invoicing them. This is a 100% code free application. Although some stuff would have been easier and more comfortable to do with VBA (it was designed in Excel) it has to be usable with Open Office too – these young students can’t afford Microsoft.
My goal was to get as much automation as possible with limiting the design on built in functions and formulas only. As everybody has different requirements you should also be able to re-design the workbook to your needs. Therefore I try to explain everything “behind the cells” and hopefully it will also suit as a sample for learning some Excel tricks. Especially my favorite functions like INDEX sand SUMPRODUCT and LOOKUP by the dozen are used. In one case I even had to refer to a pure matrix formula. I still struggle with these adventurous constructions and only with the help of the EXCEL-L (a wonderful mailing list with incredible helpful experts all over the world) I got it working.
Let’s start with “How to use” the sample workbook.
After downloading and extracting the zip file open the workbook.
There are 4 tabs with 1 configuration sheet and 3 sheets you need to work with. The sheets are protected to avoid unintentional changes of formulas. When you want to play around and change just unprotect the sheets (there is no pass word set).
Here you need to set some general values according to your needs. The green highlighted areas are ranges which will be used later on for selection and /or calculation.
The first two just set a starting point for your customer and order numbers.
The units are hopefully self explanatory: In the example there are the typical units for consulting business. Right now there are 6 possible entries but you can easily expand the range and put in as many as you like (travel expenses, pieces whatever).
The VAT (Mehrwertsteuer) specifies the possible rates (in the example the German ones).
Here put in the master data for your customer. Start a column B – the customer number will be automatically generated. (In fact it’s already there for 49 customers only by the means of conditional formatting the unused number is made “invisible”) When you need more, unprotect the sheet and copy the formula in column A further down. But you’ll have to adjust the range “Customer” accordingly (easiest way: press Strg(Ctrl)+F3, select the range name in the pop up window and adjust the range in the “refer to” field).
This is the heart of the “application” where you’ll have to record your jobs or orders diligently.
You start with putting in an item number first. By choosing 1 you start a new order, by choosing 2 or higher you extend the order from the line above. In our example WB you can create as many as 15 items to an order (that’s the maximum for the invoice template) but you can change this easily to fit your needs when you have read to the end of this little documentation (at least that’s my hope).
The Invoice Date needs to be empty (0) at this stage. As you will see this a field to keep track of already invoiced items. When an invoice is created then you need to put in the date (This is a strict rule. When you do not do it accurately you’ll get confused and the customer angry).
Then you have 3 description fields (which will be concatenated in the invoice).
Choose your customer from the drop down list (when the customer exists, the name in the column “customer” will appear)
There probably is a purchase order number from your customer and naturally a column for it in our sheet.
Now you need to specify your job data. When you did it, how many hours or days you spent on the job.
Then select the applicable VAT rate and everything else will be calculated for you.
To manage your customer accounts the simple field “money received” will probably be sufficient. There is no automation for payment reminders
Not too much work so far? Now comes the last and crucial step (you need your money).
Simply put the order you want to invoice into cell F2 or select from the drop down list. – your invoice is ready for print:
Don’t laugh at the layout – that’s not my strong point. You can design this to your liking as long as you keep the formulas behind it all intact.
In general ….
- Customer number and address information is selected based on the customer number selected in the order. Naturally there is only one customer number per order allowed. There is no check (yet) to enforce this. Be careful!
- Invoice number is generated using the actual date and the order number. You can play around for different approaches.
- Date is the actual date
In our example the first item has already an invoice date in sheet orders. It won’t be invoiced again
The invoice part of the sheet is defined by the print range. You can print on paper to pdf or whatever.
How does it work?
a) Order number management
The cells in column B of Orders contain formulas like this:
=WENN(ISTLEER(C9);;WENN(C9=1;B8+1;B8))in English notation: =IF(ISBLANK(C9),,IF(C9=1,B8+1;B8))
This is a simple decision formula: if cell in column C is blank, leave it blank (no item no order). Otherwise if cell in column C has the value 1, add 1 to the previous order number, or else keep the previous order number. In other words: when I put in an item number 1, a new order is started, otherwise the previous order will be continued.
In Column A there is a simple concatenation of order number and item number (simply: =B9&C9). Nevertheless this is the most important column as this generates the primary key for the line item which relates the invoiced items to the order items.
There are LOOKUP functions all over. As they all work the same way just as an example the customer selection in column I:
=WENN(ISTLEER(H8);"";SVERWEIS(H8;Customer;2;FALSCH)) in English notation: =IF(ISBLANK (H8),"", VLOOKUP(H8,Customer,2,FALSE))
The first part IF(ISBLANK (H8),"",) checks whether a customer number is selected. If not, put in an empty string, otherwise the lookup will return an error value.
If there is a number, the function VLOOKUP(H8,Customer,2,FALSE) looks up the name of the customer in our named range Customer. It takes the value from H8 and compares it with the first column in the range and returns the value from the second column. The last argument defines whether it should look for the closest value (TRUE) or an exact match (FALSE).
Naturally there are a lot of lookup functions behind the template. First the order number tracks the customer number and all address details for the customer are derived by the customer number.
Behind this you’ll find the most crucial formulas for this application. Especially in column K you see a rather strange looking formula:
Or in English notation (hope I got everything right):
The solution is from Damon Longworth who posted it in 2004 on the Excel-L list. I still have trouble understanding this in detail but I try to explain it anyway:
First note the curly brace around the whole thing: it is set by Excel when you input the formula by using Strg(Ctrl)+Umschalttaste(Shift). With this it no longer deals with single cell references but with arrays. Without the curly brace the formula won’t work! There are easier to handle built in array functions like SUMPRODUCT (we’ll get to it later) but they can’t do everything and to my understanding the above mentioned method is the only workable one for this purpose.
The condition of the IF function (SUM(1*(INDEX(Orders,,2)=Order2Invoice))<ROW(A1)) may seem strange to you (at least it was a long time for me. First you need to understand the INDEX function (also an array function). Its syntax is:
Index(array, row_number, column_number )
array is a range of cells or table
row_number is the row number in the array to use to return the value
column_number is the column number in the array to use to return the value.
In our case INDEX(Orders,,2) we define the second column of the range Orders as an array. We now compare this range with our selected order number (INDEX(Orders,,2)=Order2Invoice) and get a list of TRUE and FALSE (= 1 and 0). Adding this up we get to the total count of matching items.
It then uses the row number as an internal index. I.e. ROW(A1) is one and represents the first item of the generated array. By copying down you get ROW(A2) = 2 and so forth. The first part of the IF clause now looks whether the total count of matching Items (=Order2Invoice) is less than the actual row number (item number). In that case we get to the FALSE part of the IF and it returns a blank -> nothing to show.
When the condition is TRUE, the matching value will be picked up by the Offset function. The general syntax (without the optional arguments is:
Offset( range, rows, columns )
range is the starting range from which the offset will be applied.
rows is the number of rows to apply as the offset to the range. This can be a positive or negative number.
columns is the number of columns to apply as the offset to the range. This can be a positive or negative number.
Our starting point is Orders!$A$3 (Leftmost cell under the header)
The offset row is calculated by LARGE function: it returns the nth largest value from a set of values. (syntax: Large( array, nth_position ). Now we can happily recapitulate what we have learned 4 bullet points before. The array: (Order2Invoice=INDEX(Orders,,2))*ROW(INDEX(Orders,,2) is a list of numbers with the row number for matching items and 0 for not matching.
OK, but why this strange calculation of the position: SUM(1*(INDEX(Orders,,2)=Order2Invoice))-ROW(A1)+1)? We know the first part which gives us the total count of the matching items. By subtracting our index based on the row and adding 1 we get the nth position of our list. It confused me for a while that it works backwards. Better take our simple example: for order 200 we get row 3,4 as result of (Order2Invoice=INDEX(Orders,,2))*ROW(INDEX(Orders,,2) [you can check this by highlighting this part in the formula window and press F9 – don’t forget to press escape to return to the formula] . We have a total count of 2 (result from SUM(1*(Order2Invoice=INDEX(Orders,,2)) and subtract 1 (result of ROW(A1)) and add 1 and get as a total result 2. The second largest (in our case lowest) row number of matching order items is 3. Please don’t get in despair – it took myself ages to figure this all out -:)
We are almost done und the rest is easy. Our starting point was A3 on the order sheet. We have now the row number where our item is and just need to subtract 3 to compensate for not starting in A1. In our example above the row offset would then be 0 as the value we are looking for is in A3. The column offset is always 0 as we only deal with the primary key column and nothing else.
The rest is simple LOOKUP stuff based on our PK. You just need nested Ifs on the one hand to suppress error values (does not look too good on your invoice) and on the other had to check whether an item has already been invoiced and must not be invoiced again.
c) VAT (Mehrwertsteuer)
When using landscape for the print format, you can easily add columns for VAT and get it with LOOKUP from the order sheet. But for the sake of our example and because I promised you above, the VAT calculation as shown is a nice example for the SUMPRODUCT function. In F36 e.g. you find:
Or in English notation:
Probably a lot of this looks now familiar. No curly bracket needed as SUMPRODUCT is a matrix function in itself.
Let’s first look at the help: In Excel, the SumProduct function multiplies the corresponding items in the arrays and returns the sum of the results. The syntax for the SumProduct function is: SumProduct( array1, array2, ... array_n ).
The first 3 arguments check whether criteria are met and return TRUE or FALSE (1/0) respectively. As all results will be multiplied, each criteria has to be met. With one False, everything is 0. The last array is the column we are actually adding up (column 16 in the Orders array is the net amount of the line item).
I always imagine this function as a little guy going through my table, checking each criteria and punching the numbers into a calculator.
In this case we check:
- Does the order number match: INDEX(Orders,,2)=Order2Invoice
- Does the VAT type match: INDEX(Orders;;17)=$D36
- Has the item not been invoiced yet: INDEX(Orders;;4)=0
TIP: when you work a lot with the INDEX function (as I do), it is helpful to change the reference style to R1C1. You can do this in the options menu. For me this is too cumbersome - I have for each style a little macro in personal.xls and switch by shortcut.
The syntax is:
.ReferenceStyle = xlR1C1 (respectively xlA1 for the A1 reference style)
That’s about it. Have fun playing around and don’t forget to tell me in case of better solutions or errors.
copyright 2009 - 2017 Richard Winter
Complaints, questions, comments? Contact firstname.lastname@example.org