Notes S-01/Excel 1: Basics, formulas, charts, conditionals


Time Topic/activity Materials
0–15 min Introduction and organization Introductory slides (separate)
15–35 min Excel basics, formulas, and cell references Slides 1–6
35–55 min Future-value calculation Slides 7–10
Slides 7 and 10: Exercise Future value
55–70 min Data handling and chart selection Slides 11–14
Slide 14: Exercise Coffee-shop charts
70–90 min Functions and descriptive statistics Slides 15–19
Slide 19: Exercise Sales statistics
Break
0–15 min Explain the moving-average task and expected output Slide 20
Homework DAX moving averages
15–35 min Conditional logic and customer classification Slides 21–23
Slide 23: Exercise VIP status
35–50 min Conditional calculations Slide 24: Exercise VIP club credits
50–70 min Work through the installment-loan calculation on the board Slide 25: Homework Installment loan
70–82 min Annuity loans and the PMT function Slide 26: Exercise Annuity loan
82–90 min Exam-result classification and wrap-up Slides 27–28
Slide 27: Exercise Exam result

Slide 1 - Excel basics

In-class demo ../materials/introduction.xlsx – Sheet: Start

  • Entering formulas in the cell or the bar
  • Selecting cells by typing “=A1+A2” vs. by clicking on cells
  • Some cells are used for entering data, others for formulas (we need to understand which type of cell is needed)
  • Illustrate data types: Add “€” to the numbers and show that the SUM/EXP no longer work.
  • Anticipate changes (based on discussions/feedback), extensions of Excel sheets (need for proper design). Use clear labels (e.g., “Price of coffee”, “Price of tea” instead of Number 1, Number 2). Make sure that formulas continue to work correctly if we add items (like “Price of water”). For this, we would need to adapt the SUM formula. Some may already know how to do this, and we will come back to it later today.
TipPro tips

Expect that Excel spreadsheets will contain errors. Always check and debug: for the exercises, but also in your job, when you build an Excel spreadsheet or when you get one from a colleague.

  • Show formula mode
  • Shortcut: ctrl+shift+`
  • Show predecessor/successor visually
  • Show calculations (stopped?)

Slide 7: Future Value (first exercise)

In-class exercise ../materials/Excel_in_class_exercises.xlsx – Sheet: Future_Value

Task: Create a first spreadsheet and create the formula for future value.

Solution:

TipPro tips

Use keyboard instead of clicking (prepare/mention shortcuts)

Slide 8: Referencing cells (example: cost revenue calculation)

In-class demo ../materials/introduction.xlsx – Sheet: Cost_Revenue

Solution:

Slide 10 - Future Value (second exercise)

In-class exercise ../materials/Excel_in_class_exercises.xlsx – Sheet: Future_Value2

Solution:

Slide 13 - Charts illustration

In-class demo ../materials/introduction.xlsx – Sheet: with_plot

Slide 14 - Exercise Coffee-shop charts

In-class exercise ../materials/Excel_in_class_exercises.xlsx – Sheet: CoffeeShop

Slide 16 — Formula syntax and functions

  • Show graphical Formulas selection (areas: financial, logical, text, …)
  • Demonstrate the function-argument separator.
  • Examples (at the bottom): how many parameters are there in each function?
  • Explicitly distinguish between a formula and a function.
ImportantTest Excel configuration

Test requirement: For SUM(3,5): everyone should have 8 as the result. This ensures that language is set to English and parameters are separated by ,.

  • English: Make sure Excel is in English (File -> Options -> Language; restart)

  • Separator:

    1. Close Excel.
    2. Press Win + R.
    3. Enter intl.cpl and press Enter.
    4. Click Additional settings… / Weitere Einstellungen…
    5. Under Numbers / Zahlen, set:
    • Decimal symbol / Dezimaltrennzeichen: .
    • Digit grouping symbol: ,
    • List separator / Listentrennzeichen: ,
    1. Confirm with OK and restart Excel.

    In Excel, ensure File → Options → Advanced → Use system separators remains enabled.

Slide 18 - Formula illustration

In-class demo ../materials/introduction.xlsx – Sheet: simple

Add the =SUM() at the end, using a range of cells:

Slide 19 - Sales statistics

In-class exercise ../materials/Excel_in_class_exercises.xlsx – Sheet: Sales

Slide 20 — DAX moving averages (first homework)

Homework ../materials/Excel_in_class_exercises.xlsx – Sheet: Moving Average

Before or after the break.

  • Explain the figure, then the formula, and then ask students how the spreadsheet should look like (draft column-names, maybe where the formulas start, but not the specific formula solutions)
  • Draft structure on the blackboard
  • Last step: create the visualization…

☕ Break — 10 minutes

Slide 22 - IF Dialogue

Opens when selecting ribbon Formulas, Logical, IF

In-class demo ../materials/introduction.xlsx – Sheet: conditional_cost

Task: introduce conditional fixed cost and calculate annual profit (based on conditional fixed costs).

Solution:

Slides 23–24 — VIP classification and credits

In-class exercise ../materials/Excel_in_class_exercises.xlsx – Sheet: VIP

Prefer nested IF functions for the VIP-status exercise (in part II).

Slide 25/26 — Installment/annuity loans (Homework)

Homework ../materials/Excel_in_class_exercises.xlsx – Sheet: Installment | Annuity

  • Illustrate the repayment logic using the visualization: materials/loans.html. We could imagine that a loan adviser uses a similar interface to discuss this with her clients. We also note that there are some parameters that we can adjust (principal, interest, payment periods) and others that are calculated (repayments). It’s the same principle in this interface and in your Excel sheet.

  • Work through the repayment logic on the board.
  • Students then transfer the calculation to Excel as a homework.
  • From period 2 onward, repayment requires an IF condition.
  • Students should identify the need for the condition themselves.
  • Allow sufficient explanation because this exercise has caused difficulties in previous cohorts.
Important

The repayment equals the fixed repayment amount or, when the remaining debt is lower, the remaining debt.

  • Insert formulas to make sure that when users modify the amount, interest rate, or redemption, the payments table is updated automatically.
  • In the annuity loan task, the annuity should also be updated automatically.
  • Students should have a clear understanding of what the data cells (manually entered/modified parameters) and the function cells are. (similar to Hillier and Hillier: data/changing/output/objective cells)
  • Ask students to understand the PMT(interest rate, term, loan amount) function (payment).
NoteUnderstanding the difference between fixed installments and annuity loans

With a fixed-principal installment loan, the principal is reduced more quickly at the beginning, which lowers the total interest paid. However, this also means that the initial payments are higher.

Slide 27 Exam

In-class exercise ../materials/Excel_in_class_exercises.xlsx – Sheet: Exam

Slide 28: Special IF functions

In-class exercise ../materials/Excel_in_class_exercises.xlsx – Sheet: Departments

Solve together (before revealing slide 28, which contains the solutions).




Summary and announcements (next session)

TipNotes for improvement

Take notes (improvements, common questions) on a separate paper and add to feedback.qmd after the session.