INSY 2299 · Information Systems

Session 4: Modeling, Prediction & LOOKUPs

Lab Exercise #2 · Ram Retail planning model · VLOOKUP & HLOOKUP

Dominik Molitor · Fall 2026 | Session 4

Session 4

Sign-in via Blackboard

Today’s code

Click the number to set it for today.

Session 4

Agenda

Click a line to jump straight to it.

Review Lab Exercise #2

Select any cell to see what it holds in the formula bar. Show formulas (Ctrl+` in Excel, and here too) to see the model’s logic at once.

Blue numbers are typed-in inputs. Outlined cells are the ones the selected formula references.

Review Lab Exercise #2

Given numbers vs. calculated numbers

The first thing you need to do in planning this model is to figure out which numbers are given, and which numbers can be calculated. Anything that can be calculated should be: in particular, the “Required GPUs” column as well as the “Remaining GPUs”, “Status” and “Assigned Jobs”.

Given: 10 typed-in numbers Calculated: 11 formulas

Review Lab Exercise #2

Never put a number in a formula

When calculating the “Required GPUs” do not put the 4 in the formula: it violates the design principle of never putting a number in a formula, and it would be difficult to change if, say, the company decides on a different batch process. Put the 4 in its own cell and reference it.

GPUs per job (B5)

4

Change the batch size and watch which formula keeps up.

		References the cell			Hard-codes the 4
DC	Available	Formula	Required	Status	Formula	Required	Status

Review Lab Exercise #2

Build once, copy down: absolute references

Create a good set of formulas for the first DC and then copy it down for the other two DCs. When you reference the cell containing the 4, you must make it an absolute reference ($B$5).

Copy E8 down using

Cell	Formula after copying	Result	What the reference points at

In this sample solution, instead of copying the formula to all three DCs, DC3 uses a different but mathematically equivalent formula so you can see the alternative: E10 =(C10*$B$5)+(D10*$B$5), which also gives 48.

Review Lab Exercise #2

Totals with SUM

Use the SUM function to calculate the total number of research and product jobs (row 11): it makes the model more readable and flexible.

C11 =SUM(C8:C10) → research jobs D11 =SUM(D8:D10) → product jobs

Review Lab Exercise #2

When SUM does nothing

Some people use SUM where it isn’t needed, e.g. =SUM(C8+C9+C10) instead of =SUM(C8:C10) or =C8+C9+C10. The answer is correct, but the formula is not good: the + operators already did all the work, so the SUM() is superfluous.

Step through each formula the way Excel’s Evaluate Formula dialog does.

Avoid

The additions finish first, so SUM receives a single number and has nothing left to add.

Good

SUM does the adding over a range, the job it exists for.

Good

Plain addition with no function wrapped around it.

Review Lab Exercise #2

Three ways to write the IF

There are several ways to use the IF function here, so this solution shows three approaches, one per DC. Just pick one approach for DC1 and copy it to DC2 and DC3.

Test with DC1: Required GPUs 80, Available GPUs 64 Remaining: −16

Cell	Formula in the sheet	The question it asks
G8	=IF(F8<0,"Shortage","Adequate")	Is Remaining GPUs below 0? Then “Shortage”.
G9	=IF(F9>=0,"Adequate","Shortage")	Is Remaining GPUs 0 or more? Then “Adequate”.
G10	=IF(B10<E10,"Shortage","Adequate")	Is Available below Required? Then “Shortage”.

Move the slider through every value: all three give the same status. What differs is which question you ask and which answer you put first.

Review Lab Exercise #2

Test the model

Never trust your (Excel) model: validate it with reasonable, actual and extreme values.

Select a calculated cell to see its formula and the cells it references.

Session 4

The bigger picture: business analytics

The lab exercise was a model. The rest of today is about what models are for — and about the Excel functions that feed them.

Different types of business analytics
RAM Retail planning model
Three-year predictions
Good modeling practices (e.g., model testing)
Predictions and forecasting
LOOKUP functions

Business Analytics

Business analytics

Idea: driving decisions and actions through extensive use of…

Data

…sometimes Big Data*: the transactions, clicks, sensor readings and records a business already produces.

*Larger data files than our laptops can handle.

Quantitative (statistical) analysis

…turning that data into a number a decision can rest on: a total, a forecast, a recommended price.

In this course, that analysis happens in Excel.

The Ram Retail model you are about to build is the smallest possible version of this: five typed-in assumptions, four base-year figures, three years of arithmetic, one number at the bottom that somebody has to act on.

Business Analytics

Three different types of business analytics (in Excel)

Each card names what the analytics does. Reveal the Excel tools that do it.

Descriptive

Analyzes historical data to understand past business performance (e.g., describe sales from last three years).

Predictive This week’s focus

Uses (statistical) techniques to forecast future outcomes (e.g., predict next year’s sales based on historical data).

Prescriptive

Recommends actions to optimize business outcomes (e.g., find the profit-maximizing price for a new product).

Predictive Modeling

Growth rate: two ways to say the same thing

Let’s assume that the Sales Revenue in 2025 was $100 and the predicted Growth Rate per year is 50 % (0.5 in decimal terms). What is the predicted Sales Revenue in 2026?

Sales 2025

$100

Growth Rate 50 %

Both methods move together, at every rate.

Method 1

Sales 2026 = Sales 2025 + Sales 2025 × Growth Rate

Method 2 — mathematically equivalent, used in today’s class

Sales 2026 = Sales 2025 × (1 + Growth Rate)

On the slide the formula reads =B10*(1+$B$6). In this year’s workbook the same idea lands in C14 =B14*(1+$B$7) — the growth rate has its own cell, and the reference to it is absolute.

Predictive Modeling

The Ram Retail planning model

Nine blue cells are typed in; everything else is a formula. Select a cell to read it.

Predictive Modeling

Copy across the years: $B$7 has to stay put

Write the formula once in C14, then copy it right to D14 and E14. Relative references (B14) are supposed to move. The growth rate is not.

Copy C14 to the right using

Cell	Formula after copying	Sales Revenue	What the rate reference points at

Not every line grows by a percentage. Marketing rises by a flat amount — C18 =B18+$B$9, ten thousand dollars a year — while Administration compounds: C19 =B19*(1+$B$10). Both put the number in its own cell and reference it absolutely.

Good Modeling Practices

Never trust your (Excel) model

Be cautious: validate (test) the model before relying on it. Use reasonable, actual and extreme values — then check that the bottom line still makes sense.

Try:

Type into any blue cell, too — every formula recalculates.

Bottom line	2025	2026	2027	2028

Predictive Modeling

Get the workbook: Ram Retail Planning Model

Go to Blackboard and download the workbook, in case you did not save it last week.

Datasets

Excel Intro
Cell Reference & Functions Example
Ram Retail Planning Model
Sales Forecasting
Lookup Example

Open it beside the deck: five inputs at the top, then one formula per row copied across 2026, 2027 and 2028.

Forecasting

More financial predictions and forecasting in Excel

The Home Depot, quarterly sales in millions of dollars, from the Sales Forecasting workbook. The Data tab stops at 07/2022; Data_Updated carries it 16 quarters further, to 07/2026.

Forecast 8 quarters

Data tab, to 07/2022 The 16 quarters after it Growth rate Seasonal

Forecasting

Get the workbook: Sales Forecasting

Go to Blackboard and download the Sales Forecasting workbook.

Datasets

Excel Intro
Cell Reference & Functions Example
Ram Retail Planning Model
Sales Forecasting
Lookup Example

Two tabs: Data (42 quarters, to July 2022) and Data_Updated (58 quarters, to July 2026) — the series on the previous slide. Select the dates and the sales, then Data ▸ Forecast Sheet.

LOOKUP Functions

LOOKUPs — VLOOKUP & HLOOKUP

Vertical & Horizontal Lookup …are functions to retrieve a value matching a specified condition from a table by searching a row (HLOOKUP) or a column (VLOOKUP).

VLOOKUP — vertical

Searches down the first column of the table, then returns a value from a column further right.

=VLOOKUP(lookup_value, table_array,
column_index_num, [range_lookup])

HLOOKUP — horizontal

Searches across the first row of the table, then returns a value from a row further down.

=HLOOKUP(lookup_value, table_array,
row_index_num, [range_lookup])

In other words: the value you are looking for, the range containing it, which column (or row) of that range holds the answer, and approximate match (TRUE) or exact match (FALSE).

LOOKUP Functions

VLOOKUP searches down columns (vertical)

Ex. 1: look up the CustomerName for a CustomerID (e.g., 32) in B2.

Lookup value A2 column_index_num

2

LOOKUP Functions

HLOOKUP searches across rows (horizontal)

Ex. 2: the same 91 customers, turned on their side — CustomerID runs across row 1, the fields run down column G. Same question, same answer.

Lookup value A2 row_index_num

2

LOOKUP Functions

The fourth argument: TRUE or FALSE

The last argument decides what counts as a match. FALSE wants the value exactly; TRUE takes the largest value that is still less than or equal to it — which is how a score turns into a letter grade.

Score in C4 67 Drag through the boundaries — 59 to 60, 92 to 93 — and watch the band change.

Approximate match — TRUE

=VLOOKUP(C4,$H$4:$I$13,2,TRUE)

Exact match — FALSE

=VLOOKUP(C4,$H$4:$I$13,2,FALSE)

The lookup column has to be sorted ascending for TRUE to work. The grade table in the workbook is: 0, 60, 70, 73, 77, 80, 83, 87, 90, 93.

LOOKUP Functions

Get the workbook: Lookup Example

Go to Blackboard and download the Lookup_Example workbook.

Datasets

Excel Intro
Cell Reference & Functions Example
Ram Retail Planning Model
Sales Forecasting
Lookup Example

Four tabs: Vlookup, HLookup and ApproxMatch are the three we just worked through. On Index_XLookup the CustomerID sits in column N, to the right of the answer — where VLOOKUP cannot reach it.

Assignment & Logistics

Assignment & logistics

Reminder — next assignment

Excel Assignment #1 is due tonight (day of class), 11 PM.
Excel Assignment #2 is due next week (day of class), 11 PM.