Chapter 9 · 7 levels

Statistics and finance

Describe a column with the mean, median, mode and standard deviation, measure a relationship with CORREL, round cleanly, and price a loan or a savings plan with PMT, FV, NPER and PV.

Start

Two kinds of question land on an analyst’s desk that plain totals can’t answer. The first is “what is a typical day, and how much do days differ?” A total hides that; an average can mislead when a few big orders pull it up. The second is money over time: “what will this loan cost us a month?”, “how much will we have saved by then?”, “how big a loan can we carry?”

In this chapter you describe Harbor & Pine’s numbers the way a statistician would: the mean, the median and the mode of a day of order lines, clean rounding with ROUND, INT and ABS, the standard deviation of daily revenue in two categories with identical averages, and the correlation between web traffic, conversions and ad spend. Then you switch to finance: a monthly payment with PMT, a payoff time with NPER, a savings plan with FV, and the largest loan a payment can carry with PV, which is the answer Excel’s Goal Seek finds.

The boss level puts the finance functions together to decide whether the store can fund a second warehouse in five years.

Levels

  1. A typical order line with AVERAGE, MEDIAN and MODE.SNGL

    from Megan Collins, Head of Sales

    average, median, mode, skew, descriptive statistics

    Code

  2. Restock numbers with ROUND, INT and ABS

    from Mia Fisher, Inventory Specialist

    round, int, trunc, abs, rounding

    Code

  3. Same average, different days with STDEV.S

    from Rachel Ramirez, Merchandise Planner

    stdev, standard deviation, coefficient of variation, spread, descriptive statistics

    Code

  4. STDEV.S or STDEV.P?

    from Ashley Miller, Data Analyst

    stdev, sample vs population, standard deviation

    Quiz

  5. Does traffic move with sales? CORREL

    from Susan Diaz, Growth Marketer

    correl, correlation, causation, descriptive statistics

    Code

  6. Financing a packing line with PMT and NPER

    from Daniel Brooks, Head of Operations

    pmt, nper, loans, time value of money, sign convention

    Code

  7. Boss: can we fund a second warehouse?

    from Robert Kim, Chief Operating Officer

    fv, pv, pmt, goal seek, time value of money

    Boss · 3 steps

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.