In the previous part of this tutorial, I went through how to design the layout a professional summary report page, being careful to making it easy to read while packing lots of information into just a single page. At this point it is entirely possible to use this as a report template; you could enter all of the data into each field one at a time, calculate profits by hand, and then save a new excel file for each day. Unless your tracking a lemonade stand, you are going to need a much more efficient method of report generation. One of my cardinal rules for creating summary reports is that the main report page shall only require the current date to be entered into it; all of the rest of the report should generate itself. All information should be pulled from raw data stored in the other sheets of the same workbook.
Keeping your raw data separate from the main report offers two main advantages. Your raw data stays raw, that is it never gets directly modified. If a formula error or other mistake is made, your old data will always be there unchanged and is still safe for re-analysis. Secondly, this way of storing and referencing data allows you to regenerate a report from a past date instantly, all from the same report file. So if you ever need a copy of a report from a previous week, comments and all, you only need to change the date at the top of the page and then everything relevant to it will be displayed for printing.
Step 1 - Setting-Up New Sheets For Data
The first step is fairly easy. We need to create a new sheet for raw data. You can use as many different sheets as you see fit. I personally like to make one for each major type of data, and so in the case of our ongoing example of Joe Transport, I am going to use two. First I want a sheet that holds the comments and other miscellaneous information such as the weather and workforce, and then I want another sheet where raw shift data can be entered, one line for each employee’s completed work for that day.
Starting with the miscellaneous data sheet, I choose to design it in a vertical sense. What I mean by this is that it will be a table that gets updated daily, with a new column holding the information for each day:
As you can see from above, the first column should be the labels for each entry field. Go ahead and set up a column for each day, I usually prepare a month or so at a time. Notice that the rows that hold text have been stretched out, this is to make it easier to display while you’re filling it in. You can set this up in any way you wish, with whatever information you deem important for your report.
Bonus Tip: To make the first (label) column stay put when scrolling with the rest of the (days) columns, click on the view tab, and select Freeze Panes à Freeze First Column. Now when you approach the later days of the month, you will still be able to see the label column on your screen when you scroll over.
Step 2 - Setting-Up Shift Report Data Entry
The next data tab we need to set up is the most important, as it will be used to calculate and track our financial information. It will compute the total revenue based on vehicle operating hours (using the generous simplification that a given vehicle generates a certain revenue per hour), as well as the total expenses based on total labor hours that must be paid out to employees on payday.
This data table is to work differently then the miscellaneous one. I find it easiest to track this type of information vertically, with a new row for each employee’s production for the day. Make sure you use the first column is reserved to enter the current date, but besides that it’s up to you to determine what information you need to collect. You can see my choices below, with a couple lines of example data included:
As you can see I really like to keep my daily raw data page layout as simple as possible. You may be wondering what all of the arrows next to my data labels are. These arrows are for a very useful feature called “Auto-Filter”. Once you have this sheet ready for data entry, highlight all of your column labels (row #1), then look up to your ribbon and go-to the Data tab, then select Filter. You will now see the arrows. You can click these arrows anytime to sort the data below alphabetically, numerically, or select it to display only the rows that contain certain values in a chosen column. You can read more about this elsewhere if you really need too, but once you try experimenting with it you will very quickly figure out how it works; it’s quite simple.
This brings us to the end of this part of the tutorial. In the third part we will start to set up the report to organize and reference data from these entry sheets we have created using formulas as well as basic calculations.
No comments:
Post a Comment