1. Notification emails are working properly again. Please check your email spam folder and if you see any emails from the Cantina there, make sure to mark them as "Not Spam". This will help a lot to whitelist the emails and to stop them going to spam.
    Dismiss Notice
  2. IMPORTANT! To be able to create new threads and rate posts, you need to have at least 30 posts in The Cantina.
    Dismiss Notice
  3. Before posting a new thread, check the list with similar threads that will appear when you start typing the thread's title.
    Dismiss Notice

Business Analytics Reporting Value Estimation Calculation - Nerd Time!

Discussion in 'Random Discussion' started by Jayson, Apr 26, 2018.

  1. Jayson

    Jayson Resident Lucasian

    Dec 24, 2015
    Likes Received:
    Trophy Points:
    +8,643 / 35 / -13
    I'm a nerd when it comes to analytics.
    So my job is the perfect playground for me. Basically, in the ultra short version, my job is to analyze current ways of doing things and look for ways to improve them through a variety of solutions.
    The solutions aren't required to be cookie-cutter solutions; I can go about it however I think is best as long as I can show the value in doing it.

    Or, in another way of putting it...

    One of the big tasks we have recently taken on is business analytics reporting. In brief, this means measuring what's going on and wrapping it up with a pretty look and spitting out a grade of some kind that helps users on varying levels make decisions. This might be a report tailored to help inform technicians decide what events to focus on, or it might be all the way up to reports that help inform executive groups decide what money to spend where and on what, or where money is being lost. And everything in between.

    One thing that happens when a company powers up an internal business analytics group, as opposed to out-sourcing, is that everyone comes running with requests because they don't need to make a check against their capital budget when requesting a report from an in-house solution.


    If you don't build some method of evaluating these requests in an orderly fashion, then you can quickly end up with a pile of requests that far exceed a reasonable level of accomplishing the requests, and worse, you can end up building reports that should probably have never really been built in the first place.

    To this last consideration, we hadn't any tool to use to aid in evaluating whether any given request was worth the work involved in building the report (keeping in mind, the reports, once built, are automated from then on).

    Over the past week, I took to solving this problem and building a calculation to employ for evaluating the value of any given report so that we could answer whether or not we should build the request.

    Today, after a work-week of seeming like a crazy person...
    I finally finished and it was such an exciting moment to finish off the calculation and run it through tests and see it correctly identify the value of various reporting requests!

    So...naturally, I'm going to now bore everyone with how I did it!

    If you're reaction is
    Then you might want to leave now. :p

    If your reaction is
    Then away we go!


    I had to use a lot of creativity and abstraction to wrangle this one in, and it was a lot of fun to build!
    The particular challenge here is that I'm trying to show a company how much something is of value to itself when it is both the supplier and consumer of what it is making.
    This tends to entirely cloud visibility of values because everything gets rather easily sucked up into just counting hourly rates of employee cost...which isn't actually the value of anything remotely close to what's at steak. If you suddenly stop using an in-house solution, you wouldn't lose the amount in man-hours that was being spent on the task.
    Further, when it's in-house, you can't really see what the value of the product is in-and-of itself because it didn't come with a price tag as a software or business solution.
    So how in the hell do you determine the value of some product no one knows the value of, and figure out the charge of service that goes with that product, when neither the product nor the service are actually going to have a real budget impact that is measurable?

    In other words...how do you measure the amount of mist versus the amount of fog inside of a cloud?

    The question comes down to two things:
    A) What is the inherent value of the report (market value - that is, what would the market charge to build that report; i.e. what the market sees as the labor cost value for the time and resources to build it)?
    B) What is the market charge for the product solution?

    You can think of (A) and (B) another way, and this is how I worked my way through it.
    (A) How much will the film cost to make?
    (B) How much will we make in ticket sales?

    But instead of (A) being the cost of the film, it's the cost of the report, and instead of (B) being the cost of a movie ticket, it's the cost of a user license for the report software so the user can see the reports.

    I basically pitted our own company against our own company as if our company was the client (which produces (A) value), and our department was an external company selling a solution to our company (which is measured as (B) revenue at (A) cost).
    Which...in the mind, can get a bit...

    But, it can be done, so here's the equation that I came up with:

    So for anyone still hanging in there and not falling asleep yet...

    Here's what this says.

    So we start from the top.
    The first equation (EQ 1) says that you firstly count how many pieces of data, metrics, and kpis you have.
    (a KPI is basically a business version of a grade - it's some way of saying "this is bad, this is OK, this is good", etc...)

    The data are assigned a weight of 0.5 because it's just data - it's not a metric. Someone's name is an example of "just data".
    Metrics are assigned a weight of 1 because they are just counts (how many of X are there? what % of X is A, B, or C?)
    And KPI's are assigned a weight of 2 because they work off of the previous two and deliver a grade which influences decision making (do I do something about this data and metrics or not?).

    So you go through the report and count all of that up; for example, this...

    Would be worth the following:
    Data: 0 (there's no raw data on this report - it's just a pile of numbers....if the table at the bottom were filled with information that wasn't counts, then it would be 1 count of data per column)
    Metrics: 18 (the table at the bottom is tallied 1 count of metric per column, the 2 pie charts would be 2 metric counts, and the bar graph is another 1 metric count)
    KPI: 1 (the dial in the middle - this informs when things are good, hazardous, or bad)

    So, using the equation above, the Score (X) would be 20.
    So 20 is then the X.

    Next (EQ 2), we run this score against the Median for data/metric/kpi pieces in the current market, which ranges from $0.20 to ~$5.00 per item currently. That makes the median currently $2.60.
    So our MET is $2.60, and our X is 20.

    $2.60 x 20 = $52.00

    Then we run that by how many days the report is to be used (or how often the data is tracked and referenced against). We'll assume every day of the year, so 365 days.

    So...our d is 365, therefore:
    $2.60 x 20 x 365 = $18,980.00

    This means that our IV for this chart is $18,980.00.

    No, not that kind of IV. IV as in Intrinsic Value - that is, the inherent value of building the report in the first place. How much is this data worth on the open market as a build (ballparking)?

    Now, this step is a bit confusing, but equation 3 (EQ 3) is basically finding the value of the audience as part of the build - that is, what's the value of making sure the report supports the audience size and demographic. This tends to be a small number by comparison, because it's just a small weight of design concerns (how much do we have to mind the UI for the size of audience, etc...)?

    To do that, we first take the size of the Audience (we'll say the audience here is 100 users) and divide that by Pi times 1,000.
    Why Pi times 1,000?
    It's just a short-cut to scaling and if you want to scale without generating big log math functions, often a variation of employing Pi times some value (usually in 10's) will suffice.
    In this case...
    100 / (Pi x 1,000) = 0.031830989

    Now we take this value and multiply it by our intrinsic value (IV) from above.
    $18,980.00 x 0.031830989 = $604.15

    So $604.15 is our AV; that is, our Audience Value.

    Now (EQ 4) we add IV + AV and get $19,584.15

    This is the final value for the report adjusted to account for the audience size (VAL).

    Now we pit-stop for a moment...
    and flip over to finding the consumer value so we (as the supplier) know what our Net Return should be for this given the audience size and license fees.

    We're at equation 5 at this point (EQ 5). Let's start with LIC.

    LIC is the median market license fee for this kind of product, which currently is at around $55.00.
    That means ~$55.00 per user is the license charge for business analytics solutions when you survey the whole span of offered solutions (not just the cheapest solutions).

    Now, since we're counting days, we have to flip that $55 per month into a day rate and we do that simply by dividing 12 months by 365 days, so we get:

    $55.00 x (12/365) = ~$1.80 charge per day per user.

    Now we take that and multiply it by the number of users (N), which is 100 in this case, and get $180.82.
    Now, we take that and multiply it by the number of days (in this case, 365), and we get $66,000.00

    Equation 5 in full looks like:

    $55.00 x (12/365) x 100 x 365 = $66,000.00

    This is, then ACW...that is, Audience Cost Worth (how much is the audience worth in licenses).

    Now in equation 6 (EQ 6) we take our ACW and subtract from it our build cost worth of VAL to find out our revenue on this project.

    ACW - VAL = NAV (Net Asset Value)

    $66,000.00 - $19,584.15 = $46,415.85

    So our NAV is $46,415.85.

    Which means...this report is a VALID report to build and not a waste of our time and resources to build. :)

    It WOULD be a waste of our time and resources to build the report if the audience was at, or lower than, 29 users (arguably, even less than 35 users isn't worth doing), as at 29 users the NAV returned is -$15.20, which means we would lose $15 a year if we were to build this as an independent company and sell it to a given company.

    So that's how this equation works. :)

    If you're still reading, YOU MADE IT!


    Jayson :D
    #1 Jayson, Apr 26, 2018
    Last edited: Apr 26, 2018

Share This Page