Statistics, spreadsheets and capstone: a small-business calculation pack

The earlier lessons each focused on one kind of calculation. Real business problems mix them: a decision about a new truck involves a loan, payroll, pricing, sales tax, break-even and an investment appraisal, all resting on sales data that has to be summarized and forecast. This final lesson adds the descriptive statistics and index numbers that business people use to summarize data, sets out good spreadsheet practice for building reliable calculations, and then works through a capstone case that pulls the whole course together.

Summarizing business data

A list of numbers, such as a year of monthly sales, means little until it is summarized. Measures of centre describe a typical value; measures of spread describe how much the values vary.

The sample standard deviation divides the sum of squared deviations by n − 1 rather than n, which corrects for the tendency of a sample to understate the variability of the whole population.

Moving averages and simple forecasts

Monthly figures bounce around because of seasons, weather, holidays and chance. A moving average smooths them by averaging the latest few periods, which makes the underlying trend easier to see. A 3-month moving average for March is the average of January, February and March; for April it drops January and adds April, and so on. The most recent moving average is a simple forecast for the next month, though it lags behind a strong trend and ignores seasonality.

Index numbers and inflation

An index number expresses a value as a percentage of its value in a base period, which is set to 100. The best-known example is the Consumer Price Index (CPI), which tracks the cost of a fixed basket of goods and services bought by households. The percentage change in the CPI over a year is the inflation rate.

In Canada, Statistics Canada publishes the CPI every month, with 2002 = 100 as the base. The Bank of Canada aims to keep CPI inflation at 2 %, the midpoint of a 1 % to 3 % target range. The CPI is used to index many payments, including CPP and Old Age Security benefits and federal tax brackets.

Building reliable spreadsheets

Most business calculations end up in a spreadsheet, and spreadsheet errors are common and sometimes very costly. A few habits make spreadsheets far more reliable.

• Separate inputs, calculations and outputs. Put every assumption, such as rates, prices and costs, in clearly labelled input cells, and refer to them in formulas rather than typing numbers into formulas.
• Use absolute references (such as $B$2) for inputs that every row should use, and relative references for values that change row by row.
• Round only where money changes hands, such as each payment or each tax amount, with ROUND(value, 2); keep full precision in intermediate steps.
• Add checks: totals that should agree, balances that should end at zero, percentages that should sum to 100 %. Flag any check that fails.
• Label units (dollars, per cent, months), date-stamp rates that change, such as tax rates, and note the source of each assumption.
• Test with a case you can work by hand, then try extreme inputs to see whether results stay sensible.

Capstone: a food truck business

Amira plans to start a food truck selling lunch bowls in a mid-sized Canadian city. She has $20,000 saved and wants to know whether the business is viable. All amounts below are before sales tax: the HST or GST and PST she collects on meals belongs to the government, is remitted regularly and is not revenue. Each step uses a technique from an earlier lesson.

# Capstone: food truck calculation pack
import statistics as st
sales = [42, 38, 45, 51, 58, 64, 70, 68, 60, 55, 49, 62]
print(f"mean {st.mean(sales):.2f}, median {st.median(sales)}, stdev {st.stdev(sales):.2f}")
print("3-month moving averages:", [round(sum(sales[k - 2:k + 1]) / 3, 2) for k in range(2, 12)])

i = 0.084 / 12
loan_pmt = round(75000 * i / (1 - (1 + i) ** -60), 2)
price, vc = 12.00, 4.20
cm = price - vc
fixed = loan_pmt + 450 + 1200 + 300 + 900 + 120 * 20 * 1.08 + 4000
be = fixed / cm
for q in (1400, 1600, 1800):
    profit = q * cm - fixed
    ocf = 12 * (q * cm - (fixed - loan_pmt))
    flows = [-95000] + [ocf] * 4 + [ocf + 30000]
    npv = sum(c / 1.12 ** t for t, c in enumerate(flows))
    print(f"{q} bowls: profit/month {profit:,.2f}, NPV at 12% {npv:,.2f}")
print(f"loan payment {loan_pmt}, fixed costs {fixed:,.2f}, break-even {be:,.1f} bowls")

mean 55.17, median 56.5, stdev 10.30
3-month moving averages: [41.67, 44.67, 51.33, 57.67, 64.0, 67.33, 66.0, 61.0, 54.67, 55.33]
1400 bowls: profit/month -57.13, NPV at 12% -14,042.88
1600 bowls: profit/month 1,502.87, NPV at 12% 53,438.53
1800 bowls: profit/month 3,062.87, NPV at 12% 120,919.94
loan payment 1535.13, fixed costs 10,977.13, break-even 1,407.3 bowls