ETX1100/ETX5900 Business Statistics

Week 1: From business questions to data

24 November 2026

Today’s journey

  1. Get oriented to the unit and its eight-week rhythm.
  2. Install and prepare the desktop version of Microsoft Excel.
  3. Learn the parts of Excel that we will use each week.
  4. Develop a shared statistical vocabulary for data and variables.
  5. Use mean, median, and mode to describe a typical value.

Housekeeping and unit orientation

Welcome to Business Statistics

  • Business statistics turns data into evidence for decisions.

  • The unit combines statistical reasoning, business context, and Excel.

  • Each topic will follow the same broad workflow:

    business question → method → Excel → validation → conclusion

What will we learn?

By the end of the unit, you should be able to:

  • classify and visualise business data;
  • calculate and interpret probabilities;
  • reason from samples and test business claims;
  • analyse relationships between variables;
  • build simple and multiple regression models; and
  • formulate and solve business-modelling problems in Excel.

The eight-week pathway

  1. Data stories and descriptive statistics
  2. Probability and distributions
  3. Sampling and estimation
  4. Testing business claims
  5. Relationships and simple regression
  6. Multiple regression
  7. Spreadsheet modelling and optimisation
  8. Integrated case and revision

How each three-hour class works

  • Retrieval quiz and business motivation
  • Short conceptual blocks
  • Watch me: the instructor live-codes a small, narrated example.
  • Now you try: students immediately complete a closely related task.
  • Compare, debug, and explain the result before introducing the next idea.
  • Finish with interpretation and a misconception check.

There is no separate workshop: explanation and application are integrated throughout the class. Come ready to work in Excel rather than only watch.

Assessment at a glance

Assessment Weight Timing
Weekly classroom exercises 15% In class each week
Weekly quizzes 15% End of each week
Written assignment 20% End of Week 4
Examination 50% Examination period

Week 1 priorities

By the end of this week, you should be able to:

  • navigate the unit resources and weekly workflow;
  • confirm that desktop Excel and its add-ins are ready;
  • distinguish data, observations, and variables;
  • classify common business variables;
  • distinguish populations from samples; and
  • calculate and interpret mean, median, and mode.

Where to find help

  • Use the course website for the weekly schedule and learning materials.
  • Use Moodle for official announcements, assessment, and submission details.
  • Use the discussion forum for content questions that may help the whole class.
  • Use the published consultation and contact channels for personal matters.

Always check the current ETX1100 information rather than logistics inherited from ETF or ETB slides.

Excel setup and orientation

Install desktop Excel before class

  • Install the current desktop version of Microsoft Excel through Monash Microsoft 365.
  • Sign in with your Monash account and install all available updates.
  • Do not rely only on Excel for the web: some analysis and modelling tools differ or are unavailable.
  • Bring a laptop on which you can open, edit, and save .xlsx files.

Enable the analysis add-ins

Windows

File → Options → Add-ins → Manage Excel Add-ins → Go

macOS

Tools → Excel Add-ins

Enable:

  • Analysis ToolPak
  • Solver Add-in

We will verify that Data Analysis and Solver appear on the Data ribbon.

Workbook anatomy

  • A workbook is the Excel file.
  • A worksheet is one tab within the workbook.
  • A cell is identified by its column letter and row number, such as C4.
  • The Name Box reports the active cell.
  • The Formula Bar shows or edits the active cell’s contents.
  • Cells may contain text, numbers, dates, or formulas.

Ribbons we will use

  • Home: formatting, editing, and number display
  • Insert: tables, PivotTables, and charts
  • Formulas: functions and formula auditing
  • Data: sorting, filtering, Data Analysis, What-If Analysis, and Solver

The goal is not to memorise every command; it is to know where to look.

Get the Week 1 workbook

Download the Week 1 Excel workbook, save a working copy, and open it in desktop Excel.

It contains two worksheets:

  • Stores: store identifiers, sales, profit, and 24-hour status
  • RealEstate: property prices and property characteristics

Keep the original file unchanged so you can restart an activity if needed.

Watch me: orient to the workbook

Follow as the instructor:

  1. moves between Stores and RealEstate;
  2. selects a cell and reads its address and contents;
  3. identifies headers, observations, and variables;
  4. resizes a column and formats currency; and
  5. saves a clearly named working copy.

Narrate each action and its purpose rather than clicking silently.

Now you try: find your way around

In Stores:

  1. Select the profit recorded for Store 10. What is its cell address?
  2. Use the Name Box to move directly to D51.
  3. Identify what the value in that cell means.
  4. Freeze the header row.
  5. Save your workbook as week1-yourname.xlsx.

Compare with a neighbour before we check together.

Formulas and cell references

  • Begin every formula with =.
  • Use +, -, *, /, and ^ for basic operations.
  • Refer to a cell by address: =C4*D4.
  • Copy formulas with the fill handle rather than retyping them.
  • Check that copied references point to the intended cells.

Functions and ranges

  • A function follows the pattern =FUNCTION(inputs).

  • A colon denotes a consecutive range: A2:A20.

  • A comma separates inputs where a function needs more than one.

  • Useful first examples:

    • =SUM(A2:A20)
    • =COUNT(A2:A20)
    • =AVERAGE(A2:A20)

Watch me: formula, reference, fill

In a new column of Stores, calculate profit as a percentage of sales:

=C2/B2

Then:

  1. format the result as a percentage;
  2. use the fill handle to copy the formula down; and
  3. inspect how the cell references change.

Now you try: build and check a formula

Create a column named Profit per $100 sales.

  1. Write a formula using Sales and Profit.
  2. Copy it to all stores.
  3. Check the first, middle, and final rows.
  4. Explain why checking only the first result is risky.

Watch me: look up Store 50

Use the unique Store ID to return Store 50’s sales:

=VLOOKUP(50,$A$2:$D$76,2,FALSE)

  • 50 is the value to find.
  • $A$2:$D$76 is the lookup table.
  • 2 identifies the Sales column within that table.
  • FALSE requires an exact match.

Now you try: complete the lookup

Using VLOOKUP, find for Store 50:

  1. profit;
  2. whether it opens 24 hours; and
  3. the result returned for Store 500.

Change only the argument that needs to change. Be ready to explain any error.

Watch me: a flexible lookup

Use MATCH to locate a store and INDEX to return the required value:

=INDEX(B2:B76,MATCH(50,A2:A76,0))

  • MATCH returns the position of Store 50.
  • INDEX returns the Sales value at that position.
  • 0 requests an exact match.

This separates the lookup column from the result column.

Now you try: change the question

Using INDEX and MATCH:

  1. return the profit for Store 74;
  2. return its 24-hour status; and
  3. verify both results against the original row.

Which parts of the formula changed, and which stayed fixed?

Watch me: make a business flag

Use IF to classify whether a store has at least $1 million in sales:

=IF(B2>=1000000,"Yes","No")

An IF function requires:

  1. a logical test;
  2. the value returned when true; and
  3. the value returned when false.

Now you try: classify and handle errors

  1. Create a flag for stores with profit of at least $300,000.
  2. Copy it down and check both a Yes and a No case.
  3. Wrap the Store 500 lookup in IFERROR so it displays Store not found.

=IFERROR(lookup_formula,"Store not found")

Watch me: filter the stores

On the Data ribbon, turn on Filter and display only stores that:

  • do not open 24 hours; and
  • have sales above $1 million.

Notice that filtering hides observations; it does not delete them.

Now you try: answer with filters

Use filters to answer:

  1. How many stores have profit below $200,000?
  2. Among them, how many open 24 hours?
  3. Which store in this subset has the greatest sales?

Clear every filter when finished and confirm that all 75 stores return.

Watch me: conditional summaries

Use the Stores data to calculate:

  • number of stores: =COUNTA(A2:A76);
  • number open 24 hours: =COUNTIF(D2:D76,"Yes"); and
  • their total profit: =SUMIF(D2:D76,"Yes",C2:C76).

The criterion determines which observations contribute to the result.

Now you try: change the criterion

Calculate:

  1. the number of stores that do not open 24 hours;
  2. their total sales;
  3. their total profit; and
  4. one manual check that makes your results plausible.

Write a one-sentence business interpretation, not just three numbers.

Watch me: first PivotTable

Create a PivotTable from Stores:

  • place Open 24 Hrs? in Rows;
  • count Store ID in Values;
  • sum Sales in Values; and
  • format the sales totals as currency.

A PivotTable groups observations and calculates summaries without changing the source data.

Now you try: summarise RealEstate

Create a PivotTable from RealEstate:

  1. place SchoolZone in Rows;
  2. count the properties;
  3. calculate average Price ($000s); and
  4. format and rename the value fields clearly.

Which group has the higher average price? Does this establish that school zone caused the difference?

Excel readiness check

Before moving on, can you:

  • find a worksheet and a cell from an address?
  • distinguish a typed value from a formula?
  • explain what a range such as B2:B101 represents?
  • use a lookup, IF, and IFERROR?
  • filter observations and clear the filter?
  • use COUNTIF, SUMIF, and a PivotTable?
  • locate Data Analysis and Solver?
  • save your workbook using a meaningful filename?

Statistical language and variable types

From a business question to data

Consider: What influences the selling price of a house?

  • The business question determines what we need to observe.
  • Each row can represent one house.
  • Each column records a characteristic of those houses.
  • The appropriate statistical method depends on the kinds of variables involved.

Variables, observations, and data

  • A variable is a characteristic that can differ between items or individuals.
  • An observation is one item or individual on which variables are recorded.
  • Data are the observed values of those variables.

For a house:

  • variables might include suburb, number of bedrooms, and selling price;
  • one observation is one house; and
  • the recorded suburb, bedroom count, and price are its data values.

The first classification decision

Variables are broadly classified as:

  • Categorical (qualitative): values indicate groups or labels.
  • Numerical (quantitative): values represent counts or measurements for which arithmetic is meaningful.

A number used as a label—such as a postcode—is still categorical.

Categorical variables

Nominal

  • Categories have no natural ordering.
  • Examples: payment method, suburb, product category.

Ordinal

  • Categories have a meaningful order.
  • Examples: poor/fair/good/excellent; low/medium/high risk.

The gaps between ordinal categories are not necessarily equal.

Numerical variables

Discrete

  • Produced by counting.
  • Often takes separate whole-number values.
  • Examples: number of purchases, complaints, or bedrooms.

Continuous

  • Produced by measuring.
  • Can potentially take any value within an interval.
  • Examples: waiting time, revenue, weight, or distance.

Cross-sectional and time-series data

Cross-sectional data

  • Observations on multiple entities at one time or during one period.
  • Example: sales across 50 stores in November.

Time-series data

  • Repeated observations of the same quantity through time.
  • Example: monthly sales for one store from January to December.

Descriptive and inferential statistics

  • Descriptive statistics organise, summarise, and present observed data.
  • Inferential statistics use sample evidence to learn about a wider population.

This week begins with description; later weeks develop inference.

Population, sample, parameter, statistic

  • Population: the complete group relevant to the question.
  • Sample: the subset actually observed.
  • Parameter: a numerical feature of the population.
  • Statistic: a numerical feature calculated from the sample.

A representative sample supports credible conclusions about the population.

Watch me: classify the first variables

In RealEstate, classify:

  • Price ($000s);
  • Beds;
  • SchoolZone; and
  • Auction.

For each decision, ask whether the values are labels, ordered categories, counts, or measurements.

Now you try: complete the classification

Classify the remaining RealEstate variables as nominal, ordinal, discrete, or continuous:

  • Baths;
  • Cars;
  • Area (sqm);
  • New; and
  • Subdivided.

Is the worksheet cross-sectional or time-series? Justify your answer.

Now you try: build a data dictionary

For each column in the Week 1 workbook, record:

Variable Business meaning Unit/categories Variable type
Example: Price Selling price of property AUD thousands Continuous

A data dictionary prevents avoidable mistakes before analysis begins.

Mean, median, and mode

What is a “typical” value?

Measures of central tendency answer slightly different versions of this question:

  • Mean: the arithmetic balance point or average.
  • Median: the middle ordered value.
  • Mode: the most frequently occurring value or category.

The most appropriate measure depends on the variable and its distribution.

Mean

For observations x_1, x_2, \ldots, x_n, the sample mean is

\bar{x}=\frac{x_1+x_2+\cdots+x_n}{n}.

  • Excel: =AVERAGE(range)
  • Uses every numerical observation.
  • Commonly used in later statistical analysis.
  • Sensitive to unusually large or small values.

Median

The median is the middle value after sorting observations from smallest to largest.

  • Excel: =MEDIAN(range)
  • Half the observations are at or below it and half are at or above it.
  • Resistant to extreme values.
  • Often informative for skewed variables such as income or house prices.

Mode

The mode is the value that occurs most frequently.

  • Excel for a single numerical mode: =MODE.SNGL(range)
  • Particularly meaningful for categorical or discrete data.
  • A dataset can have no mode or more than one mode.
  • For continuous data, the most populated interval may be more useful than one repeated value.

Watch me: price centre

In RealEstate, calculate for Price ($000s):

  • =AVERAGE(A2:A330);
  • =MEDIAN(A2:A330); and
  • =MODE.SNGL(A2:A330).

Then translate each result into a sentence about property prices. The formula is not the conclusion.

Now you try: compare centre in Stores

For both Sales and Profit:

  1. calculate the mean, median, and mode where applicable;
  2. compare the mean with the median;
  3. identify whether the mode is informative; and
  4. recommend the most useful measure of a typical store.

Add one temporarily extreme value to a copy of the data. Which measure changes more: mean or median?

Mean, median, or mode?

When choosing a measure, ask:

  1. Is the variable categorical or numerical?
  2. Is a natural ordering available?
  3. Are extreme values pulling the mean?
  4. Do repeated values or a dominant category matter?
  5. Which measure answers the business question most honestly?

Do not report a measure merely because Excel can calculate it.

Closing check

  • Mean: uses all numerical values but is sensitive to extremes.
  • Median: identifies the middle and is resistant to extremes.
  • Mode: identifies the most common value or category.

For the Week 1 data, calculate all applicable measures and write one sentence explaining which measure best represents a typical observation.