Business Modeling | BobbyBluford.com

Calculated Decision-Making

May 22, 2011 by bbluford 1 Comment

Cracking the code to the safe is hard enough. You better write down the misses as you go along!

If you were trying to break into a safe that had a five-digit combination, how would you do it? Would you haphazardly guess at the sequence of numbers, hoping you’d stumble upon the correct digits in the right order? Not very efficient, right? Would you try to uncover any clues that might help you decipher what those numbers might be? A better approach, certainly, but still difficult. Knowing on what the combination was based—birthday, favorite numbers, anniversary maybe—is difficult enough. But then you’d need to figure out what the answer to those clues might be. Whatever approach taken, cracking the code of any safe is almost impossible. That’s a good thing, right? Unless you’re a thief, that is. With 100,000 combinations (10 possibilities, including ‘0’, for each digit, raised to the power of 5 for the number of digits in the combination), you have a better chance of becoming a pro athlete (a good thing: 1 in 22,000) or getting audited by the IRS (a bad thing: 1 in 175). But if you were stupid enough to try this feat, what would be the one thing you would have to do? Document all of the failed attempts. After all, if a combination is not correct, you surely don’t want to repeat it.

But that’s exactly what happens a lot of times in business. Especially in small businesses. Often, even, in successful ones. Let’s be honest; the reasons entrepreneurs start businesses are many. But one of the more common ones is to escape the rules, policies, and overall bureaucracy they have faced as employees in other companies. And as we can all attest, there are certainly reasons to dislike these rigid systems, often designed more to play ‘big brother’ and keep employees in line than to get real work done. Read more of this post

Filed under Business Stuff, Company Strategy and Culture, Deep Thoughts and Philosophy Tagged with Business Leadership, Business Modeling, Business Planning, Dashboards, ERP, postaweek2011

YOY Comparison for Most Recent Days

March 3, 2011 by bbluford 1 Comment

Comparing Same Day Sales YOY

In my last post, Showing Activity for Most Recent Days, I showed you how to find the most recent date for which you have “totals” data. We found that whether it’s support calls or sales made each day, being able to retrieve this information quickly and efficiently can be extremely helpful. By using the ROUNDDOWN AND MAX functions, as well as the very powerful SUMPRODUCT, we were able to create a quick summary of the most recent days’ activity. The obvious next question? How do those days compare with the same time last year. In other words, using the most recent data, how are things going compared to last year?

We already have the first several pieces of the puzzle, the current year’s sales information. To compare the data to last year, we simply find the totals for the same dates in the previous year. We can determine these dates these dates fairly easy by using the CONCATENATE formula, along with the popular and well known MONTH and YEAR formulas. Read more of this post

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with Business Forecasting, Business Modeling, CONCATENATE function, DAY function, Financial Models, MAX function, Microsoft Excel, postaweek2011, Rounddown Function, SumProduct Function, WEEKDAY function, YEAR function, Year over Year Comparisons

Showing Activity for Most Recent Days

February 26, 2011 by bbluford 1 Comment

I’m working on a dashboard for a company that sell tee shirts to high school sports programs. They get their data from an online database which includes day and time of order as well as order amount. But, you could apply the same methodology to any data that is updated often, whether you are copying and pasting into Excel, importing from or linking to an Access database, or connecting to an ODBC data source. (Of course I didn’t use the company’s actual data; I used a cool trick to generate random dates and times for events for my sample data.)

At any given point, management of Acme SportsTees, Inc. would like to see the last 10 days of sales activity. The trick to dashboards; and in my opinion, any process or task you are trying to automate; is removing from the process as much human involvement (thinking, typing, more thinking) as possible. So, if you are able to connect directly to the data source, or at least reduce the actions required by the administrator to just copying and pasting, you will significantly reduce the opportunity for human error and a lot of headaches. Read more of this post

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with 85, Business Forecasting, Business Modeling, Dashboard, Microsoft Excel, postaweek2011, Rounddown Function, Statistical Analysis, Sumif Function, SumProduct Function

Generating Random Times for Events

February 25, 2011 by bbluford 1 Comment

If you are ever in the need for sample data where the events (sales, support calls, etc.) occur several times throughout any given day, here is a neat little trick to accomplish that. One might think the RANDBETWEEN function, which generates a random integer between two numbers that you feed the function, would do the trick. Well, because RANDBETWEEN only returns whole numbers, the time of the day is always going to be at midnight.

To add increments that are less than one day, you would need to add numbers less than one (i.e. not whole numbers). For example adding 0.25 would take the time to 6 am; 0.5 to noon, and 0.75 6 pm. Luckily, adding a number less than one is easy. The RAND function, which generates a number between 0 and 1, does all of the work you.

To generate a list of random times, then, simply start with whatever date you wish in the first row of your data. All is needed in each cell afterward is the addition of Rand(). After adding Rand() to the previous cell, simply copy (or drag) the formula for as many entries as you wish. See attached for example. If, when you’ve finished the copying/dragging, you don’t see enough entries in each day (only 6 am, 2 pm, and 11:30 pm— 3 entries—for example), simply divide Rand() by 2 (or 3 or 4 or 5), to make the increment each time smaller. Pretty straightforward, huh?

Example: Generating Random Times and Dates

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with Business Forecasting, Business Modeling, Generate Random Dates or Times, Microsoft Excel, postaweek2011, Rand Function, RandBetween Function, Statistical Analysis

Trend and Growth Functions

January 13, 2011 by bbluford 2 Comments

Data can be analyzed in a lot of different ways. Excel has dozens of tools and functions that make high-level statistical analysis not only possible, but in many cases fairly easy to use.

Researchers of all types (medicine, anthropology, sociology, entomology are a few of several fields) use statistics extensively. Larger corporate companies also spend resources on analyzing, reporting, and making business decisions based on statistical analysis.