Creating an Analytics Tool
During the next few weeks, there will probably be a slow down in posting and artwork because of 2 "must do" work projects. One is, of course, the ACA electronic submission and the other is a new project to develop a kind of analytical dashboard/reporting system.
In regards to the ACA, I received feedback that there were some errors: 1) they wanted the scenarios to be in scenario number order rather than organized by first name as per the instructions I read (maybe the wording of the instructions was talking about something else); 2) the dollars amounts need to be in the format of $105.00, not $105 - I had forgotten that I was doing the format of $105 as a test; 3) I misspelled Wimberley as Wimberly. As far as I'm concerned, these are all minor, even #2 because our monthly amount are in cents. Anyway, I resubmitted and hopefully that will get us to the production stage.
The new project I'm working on is kind of complicated because we will have Student, Multifamily, Development and Consolidated components. Within these components, we'll have US, International (mainly for students), and Consolidated. There will also be various departments. One of the departments, Accounting, already has data by the person, but the other departments do not (yet). BUT, I want to keep the screen simple and not have a multitude of tables/charts laid out throughout the worksheet. Right now, I start off with the Overview, showing annual forecasts or trends. You can pick headcount or cost and compare the trending against the revenue or property counts. We want to make sure that as we add in more properties, our headcount or costs do not spiral out of proportion with our growth. At least, that's what I'm hoping to show.
The next section will show data by teams. The intent is to show some statistics for # of properties, the revenue, the headcount (if available), etc. Right now there is a coloring system to show who is above average and who is below average. I'm thinking of developing a ranking system based on the counts of above and/or below average rankings. This section required the most work because the departments have different number of teams. One department has 3 teams, another has 11 and yet another has 34 teams. Since I wanted to stay within the same screen without any horizontal scrolling and 34 teams won't fit comfortably in one screen, I used a scroll bar to help maneuver the information.
[ap_divider color="#CCCCCC" style="solid" thickness="5px" width="100%" mar_top="20px" mar_bot="20px"]
To insert a scroll bar, you will need to be able to access the Developer tab. In the Developer tab, there is a tool called "Insert Controls" sitting in "Controls" part of the ribbon. You will want to pick the "Scroll Bar Form Control" (don't use the ActiveX Controls - that doesn't seem to work - at least not directly). Next, draw out the scroll bar and then right click it to pick Format Control to get further options. Set your miminum to 1 and the maximum to be wherever you want the scroll bar to stop. You may have to play around a little with the ranges. One thing I learned is that you can't use formulas in the minimum and maximum settings. The next big thing is to pick your Cell Link. This Cell Link will determine where your scroll bar will start. It will probably start off as one when you first set up your Cell Link but when you move the scroll bar, this cell will change.
How do I use the 34 teams with the scroll bar? Next to each of the team (this list of teams will reside somewhere else on the spreadsheet), I have assigned a number 1 through 34. Let's pretend the cell link is located in cell Z1, the list of teams found under column AA, and the analytics/report section starts around A1. I want to have a list of teams running horizontally across row 2. In row 1, I will have numbers running from the starting point on through as much as I can show on the screen. In my case, because I wanted to have spaces between teams, my maximum number of teams on the screen was set to 12. This row, by the way, will be invisible. In cell A1, I will have it reference cell Z1 with =Z1. Remember Z1 is the Cell Link denoting the starting position of the scroll bar. The formula in B1 will then be =if(A1="","",A1+1). I copy this formula out to where I have 12 numbers. In my case, since I have spaces, I have to adjust the formula for the spaces. In row 2 will be the team names. In cell B1, I have a formula like =if(A1="","",VLOOKUP($A1, $AA$1:$AB$34,2,false)) where AA contains the numbers 1 through 34 and AB contains the team names. Copy across that formula until you pull in 12 team names and you are ready to play with your scroll bar.
[ap_divider color="#CCCCCC" style="solid" thickness="5px" width="100%" mar_top="20px" mar_bot="20px"]
A really creative use of that scroll bar is in situations where you want to freeze your column headers but you still want access to information above the column headers. Let's say you have a series of tables going down the tab: table A, table B and table C. Tables A and C fit within your screen without any need of scrolling but table B has so many rows that you have to scroll down the screen to see the rest of the table. If you use View - Freeze Panes, to lock in the headers in table B, you won't be able to see table A, without undoing the Freeze Panes. And if you are creating tables for another person to view, you really can't use Freeze Panes. The scroll bar will get around this issue: you can lock your headers and scroll down the rows of table B and you can still see table A. This scroll bar will really come in handy.
And that's what I did in the 3rd section. The third section displays some statistics (which I'm thinking of taking out because it seems to repeat some of the team data) and displays each of the individuals in one of the teams. The scroll bar came in handy to lock in the column headers while scrolling but still providing access to the earlier sections.
My last post talked a little about dynamic charting so I won't go into details here, but probably in the future, I will upload a demo file showing both the dynamic charting and the scroll bars so you can see how you can use them.
