Skip to main content
ExamExplained

Spreadsheet analysis, what-if modelling and dashboards: HSC Enterprise Computing Data Science

Syllabus dot point

“Summarise data using a spreadsheet; collate information using spreadsheet analysis features, including charts, statistical analysis and what-if modelling; filter, group and sort data in a spreadsheet to process and display information; apply spreadsheet analysis features to develop a data dashboard”

HSCEnterprise ComputingData Science8 min read

Quick answer

Spreadsheets summarise data with functions and statistics, present it with charts, and support what-if modelling through goal seek, data tables and scenarios. Filtering, grouping, sorting, linking sheets and conditional formatting process and highlight data, and pivot tables, graphs and slicers turn it into an interactive dashboard.

Jump to a section
  1. What this dot point is asking
  2. The answer
  3. Practice questions

What this dot point is asking

These content points are practical. You need to be able to summarise and analyse data in a spreadsheet, use what-if modelling, filter, group and sort data, and build a dashboard with pivot tables and slicers. Exam questions often show a spreadsheet screenshot and ask you to write or interpret a formula or explain a feature.

The answer

Summarising data

Common functions:

Function Purpose
SUM, AVERAGE, MIN, MAX, COUNT Basic summaries
MEDIAN, MODE, STDEV Centre and spread
COUNTIF, SUMIF, AVERAGEIF (and the -IFS versions) Summaries that meet conditions
IF Decisions, for example =IF(D2>=50,"Pass","Fail")
XLOOKUP or VLOOKUP Find a value in another table
ROUND Control decimal places

Use absolute references (with dollar signs, such as B2 fixed as the tax rate cell) when a formula must always point to the same cell as it is copied.

Analysis features: charts, statistics and what-if modelling

  • Charts: choose the chart to suit the data (line for time, column or bar for categories, scatter for relationships, pie for parts of a whole with few categories). Add trendlines to show direction and forecast.
  • Statistical analysis: functions above, plus correlation (CORREL), frequency tables and histograms.
  • What-if modelling: a model separates inputs from formulas, so you can change inputs and watch outputs.
    • Goal seek: works backwards from a target output to the input needed.
    • Data tables: show outputs for a range of one or two inputs at once.
    • Scenarios: save and compare named sets of inputs (best case, worst case).

Filter, group and sort

  • Sort orders rows (highest revenue first, then by date).
  • Filter hides rows that do not meet criteria, so you can focus on a region or product.
  • Group combines rows into categories with subtotals (by month, by product type).
  • Linking multiple sheets (for example ='January'!E40) pulls data together to create summaries without retyping.
  • Conditional formatting highlights values that meet rules (overdue in red, top 10% in green, data bars).
  • Making data comparisons: side-by-side columns, percentage change formulas and charts.
  • Designing forms and reports: data entry forms and validation (drop-down lists, allowed ranges) reduce input errors; print-ready report layouts present results.

Building a dashboard

A dashboard shows the key measures on one screen.

  1. Store raw data in structured tables.
  2. Create pivot tables to summarise it (for example revenue by month and product).
  3. Add graphs linked to the pivot tables.
  4. Add slicers (and timelines) so users can filter all elements at once.
  5. Highlight key figures (totals, percentage change) with conditional formatting.
  6. Keep it uncluttered, labelled and designed for the audience's decisions.
Worked example

A school canteen tracks daily sales in a table with columns Date, Item, Category, Quantity and Price.

  1. Add a Revenue column: =D2*E2, copied down.
  2. Insert a pivot table with Category as rows, Month as columns and Sum of Revenue as values.
  3. Insert a column chart from the pivot table to compare categories each month.
  4. Add a slicer for Item so the manager can see a single product's sales.
  5. Use conditional formatting to shade months where revenue fell below the previous month.
  6. Use goal seek to find the price of a new wrap needed to raise weekly profit to the target.
Common traps
Hard-coding numbers into formulas
Put inputs in their own cells so what-if modelling works.
Confusing filtering with deleting
Filtering hides rows temporarily; the data remains.
Overloading dashboards
Too many charts hide the key message.

Practice questions

Original practice questions graded from foundation to exam level, each with a full worked solution. Try them before revealing the solution.

foundation3 marks
A spreadsheet lists 300 online orders with columns Date, Region, Product, Units and Revenue. Describe how you would find total revenue for the North region only, using two different spreadsheet features.
Show worked solution →
  1. Function: =SUMIF(B2:B301,"North",E2:E301) adds Revenue where Region is North.
  2. Filter: apply a filter to the Region column, show only North, and read the subtotal (or use a SUBTOTAL function that ignores hidden rows).

A pivot table with Region as rows and Sum of Revenue as values would also work.

Marking guide: 1 mark per correct method (2 marks), 1 mark for correct cell ranges or steps.

core4 marks
A student sells cupcakes. Each costs 1.20 to make, she sells them for 3.50 and pays a fixed 60 per week for a market stall. Explain how she could use what-if modelling in a spreadsheet to decide how many cupcakes to sell to make a weekly profit of 200.
Show worked solution →

Set up input cells for cost per cupcake (1.20), price (3.50), stall fee (60) and quantity sold, and a formula for profit: =Quantity*(Price-Cost)-Fee.

Use goal seek to set the profit cell to 200 by changing the quantity cell. The profit per cupcake is 3.50 minus 1.20, which is 2.30, so she needs 260 divided by 2.30, about 113.04, so 114 cupcakes (rounding up).

She can also build a data table showing profit for quantities from 50 to 150 and prices from 3.00 to 4.00, or save scenarios (low, expected and high sales) to compare outcomes.

Marking guide: 1 mark for the model with inputs and formula, 1 mark for goal seek, 1 mark for the correct quantity, 1 mark for another what-if tool.

exam6 marks
A regional sports club records attendance, membership fees and canteen sales in three separate sheets. Design a spreadsheet dashboard for the committee, describing the features you would use and justifying each.
Show worked solution →
Data preparation
Keep raw data on separate sheets formatted as tables, with consistent dates and a Month column. Link a Summary sheet to them with formulas such as =SUMIFS(Canteen!D:D,Canteen!A:A,">="&B1) so figures update automatically.
Pivot tables
Summarise attendance by team and month, and revenue by source and month. Pivot tables handle hundreds of rows quickly and can be regrouped as questions change.
Charts
A line chart of monthly revenue by source shows trends; a column chart compares attendance by team. These are linked to the pivot tables.
Slicers
Add slicers for Season and Team connected to all pivot tables, so a committee member can filter the whole dashboard with one click.
Conditional formatting and key figures
Show total members, revenue this month and change from last month in large cells at the top, with a red or green icon for up or down.
Justification
The committee are volunteers who need a quick overview at meetings; one screen with live, filterable summaries is easier to interpret than three raw sheets, and linked formulas reduce manual errors.

Marking guide: 1 mark each for linked summary data, pivot tables, charts, slicers and highlighted key figures (5 marks), 1 mark for justification linked to the audience.

Practise this

Sources & how we know this

Based on the official NESA syllabus. Not yet individually reviewed by a person; always check NESA and your school's materials for current rules.

ExamExplained