Table of Contents: Advanced Spreadsheet Design

Introduction

Introduction

Get started here - we'll go over what you'll learn in this course and how to navigate through sections and lessons.

Prerequisites

Overview of prerequisites

Before diving into our course, there are a couple of important prerequisite lessons we'll need to learn. Don't worry - they're minimal!

Using Tables

Tables are a data construct built in to Excel that allow us to manipulate similar ranges of data quickly and easily.

Cell references

Excel has two types of cell references: Relative and absolute. Relative references shift as you copy and paste cells. Absolute references do not.

INDEX MATCH

INDEX MATCH, one of Excel's most useful functions, is an improved alternative to VLOOKUP. Using INDEX MATCH is easy — find out how with our tutorial!

SUMIF

Excel's SUMIF function allows you to take the sum of a column or row of data conditional upon a particular criteria row.

What is a model?

Terminology

Let's familiarize ourselves with a couple of terms that we'll be using throughout this course.

What is a 'model'?

Take a deeper dive into the term 'Excel model' with a real-world example of what a model is and why it's useful.

Why flexibility is important

Why is flexibility in our spreadsheets and models so important? Let's take a look at the advantages of proper spreadsheet design.

Structuring the spreadsheet

Outputs and inputs

Every flexible Excel spreadsheet or model has three primary components: outputs, baselines, and assumptions. Let's examine all three of these pieces and how they work together.

Spreadsheet diagrams

Before diving into Excel, it's useful to create a diagram of your spreadsheet: a basic sketch of how all of its individual pieces will interact together.

Diagramming exercise

Now that you've got a sense for how to diagram a model, let's give it a try! Can you turn these instructions from your manager into a basic model diagram?

Creating the spreadsheet

Wireframing

Once we're in Excel, the first step of creating a flexible spreadsheet is to lay down a 'wireframe': a blank outline of the structure of our data.

Wireframing exercise

Let's try our hand at wireframing our own model based on instructions given to us by our manager. This wireframe will serve as the baseline for the rest of our work!

Data cleaning

Before inserting formulas and values into our wireframe, we'll need to clean whatever source data we've been given to ensure that it's properly formatted for analysis.

Formatting exercise

Now that you've learned how to format data properly, let's try our hand at formatting a sheet of messy source data sent to us by our manager.

Wiring the sheet

Our wireframe is complete, and our source data is clean and properly formatted. Now, it's time for the good part: let's complete our model by 'wiring' up our blank cells!

Wiring exercise

Try your hand at wiring the spreadsheet we've diagrammed and wireframed. Can you choose the proper formulas and functions to fill our blank cells?

Adjustments and analysis

Goal seek

Goal seek allows us to automatically adjust the input or assumption cells of a model to achieve a desired result.

Goalseek exercise

Can you use goal seek to help your manager understand what model inputs are required to achieve a desired output?

Sensitivity analysis

Models aren't just about input and output. Since they often rely upon a set of varied assumptions, it's also helpful to know how changes in those assumptions will affect the final output.

Sensitivity exercise

Can you use sensitivity analysis tables to get a sense for how the output of our model will change when a few key assumptions are modified?

Conclusion

Conclusion

This marks the conclusion of our course! Check in here to review what we've learned.