Tanzanian Troubles (Part I)

January 2019. Spent two weeks in Dar es Salaam, courtesy of CESO, giving training sessions on financial management and strategic marketing for the National College of Tourism. Great place, lovely people, very pleased to have been invited.

So it's Sunday afternoon and I run into the head chef who's on his way to the office to fire up Excel and work on his costing exercise. Since 32 degrees celcius is a bit warm for strolling around town with sun directly overhead, I followed him to see how his progress was coming along. Pretty ambitious I might say, but certainly moving in the right direction. Trying to track inputs, doing some menu costing, and struggling with the complexity of it all. Maybe you have been there.

STEP 1 - Recording the daily inputs

Challenge: The information for the inputs costs came from the daily record list of supplies obtained from central purchasing. The Excel spreadsheet was 31 calendar days as column headings and 150 items as row headings. Quite a matrix, and not even knowing how to "Freeze Panes" to make it a little bit easier. I started out that way myself, finding the right intersection cell for every entry. Painful is the word that comes to my mind. If there is one thing I've learned is that data entry cannot be made easy enough for my liking.

Solution: The data consists of three fields: date, item description and quantity. Input entry should mirror the layout of the data form. And it's way easier to type in a 3 character code than it is to type in "majani maboga" (that's Swahili for pumpkin leaves). Now if any of you have worked with data bases in Excel the VLOOKUP formula is indispensable. Create one file to use as your main data base and create another file as a template for the monthly data. Column 1 - enter the date, column 2 - enter the code (in a month's time you'll know 3/4 of them), column 3 - enter the quantity, column 4 - use VLOOKUP to check the data base and return the item description based on the code, column 5 - VLOOKUP the cost of the item, column 6 - calculate the total cost for that item obtained from central purchasing. If you get into the habit of "tab"ing across the row and the "enter" on the last value the cursor will go to the beginning of the next row for you.

STEP 2 - Setting up the data base (really step 1)

The VLOOKUP formula requires the lookup value (the code) to be in the first column, after that it doesn't matter. So column 1 - the code (in this case numbers from 1 - whatever), column 2 - item description, column 3 - type of item (several categories that match the expense accounts), column 4 - the purchasing unit cost of the item, column 5 - the purchasing unit of the item, column 6 - the measuring unit of the item, column 7 - cost of the measuring unit of the item.

So the VLOOKUP formula used in the monthly template looks up the code, goes to the data base and finds it, then goes over the desired number (entered in the formula) of columns and returns that value. The three main causes of error messages:

Error 1 - the code does not exist in the data base → double check your code number.

Error 2 - the codes are not the first column in your array (data base table) → change the start column of your array.

Error 3 - the code falls outside the array → if possible use column references rather a specific number of rows.

Result

Estimate data entry time reduced by three quarters!