Business Forecasting with Google Sheets

Increase your income and career prospects with business forecasting skills
3.5 (2 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
552 students enrolled
$19
$25
24% off
Take This Course
  • Lectures 37
  • Length 4 hours
  • Skill Level Beginner Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 12/2014 English

Course Description

In this Business Forecasting with Google Sheets video course, I introduce you to Google's online spreadsheet application, Sheets and how to use it for forecasting.

Learn to forecast the future with your business data

  • Why forecast?
  • Smoothing noisy data
  • Analyzing for trendlines
  • Transforming data
  • Forecasting future values

A powerful skill to increase your value and earnings.

Millions of people use spreadsheets today, usually at the the office as a data entry form. Very few actually build spreadsheets unless that is their job. The dominant spreadsheet, Excel is often bought by students but rarely used, and most working adults only have access to it at work.

Until now. Google's online spreadsheet "Sheets" has become a powerful tool through rapid iteration. While still not as powerful as Excel it does have a number of very useful, unique features. But most impotrtant. it is free to use. You can build spreadsheets at home, while on a cellphone, tablet or laptop while traveling, indeed anywhere with internet access.

This course focuses on using Google Sheets to do one of the most important business functions - forecasting.


It is a powerful, full featured, cloud based spreadsheet that can be harnessed for business forecasting in a collaborative environment, and the results directly embedded in live web pages for internal and external communication.

Content and Overview

The course uses Google sheets, a number of which are publicly available to the student. In addition there are links to web content and documents with more explanation of the concepts used.
I have broken down the content into easy to manage chunks so that you can focus on a concept and experiment and test yourself. You can complete this course in an evening or weekend and be ready to do work on Monday, although I recommend working with your own data and the data I provide to gain confidence in your new skills.

I start with some evidence of why the skills this course teaches are career drivers. Then we discuss what forecasting is. We start with the basics of smoothing data to remove noise, and then move on to techniques of implementing trendlines to forecast with. Throughout the course, each point is made using data that you see being analyzed in a live spreadsheet demonstration, that you can replicate on your own computer to nail down and internalize the technique.

Competency doing real work relevant to business is a career enhancing skill in today's job market. This course will give you a strong start in applying those skills to your business and employment.

What am I going to get from this course?

  • 32 lectures and over 3 3/4 hours of content
  • Understand how to analyze historic data to predict the likely future
  • Learn how to use built-in and custom functions to extrapolate future values
  • Able to work at home on your computer at your own pace
  • Visual training method, offering users increased retention and accelerated learning.
  • Lifetime access to me to help with your own data.
  • Unconditional Udemy 30 day money-back guarantee - that's my personal promise of your success!

Update 11/5/2015

2 new lectures added on errors to avoid with trend analysis. These are common mistakes amateurs make that you can avoid in your own analyses and look more professional when examining analyses from your colleagues.


So start learning today. Because being a technology dinosaur isn’t going to get you a promotion or raise!

- Alex Tolley

What are the requirements?

  • The student will need access to a computer, preferably a desktop or laptop, to listen and follow the lectures. The student will also need a Google account, perhaps acquired for email, in order to use the spreadsheet application to answer questions. An account is not needed to just follow the lectures. However it is recommended that the student be able to use Google Sheets to try out the techniques on different data and to become familiar with Sheets' functionality by "doing".

What am I going to get from this course?

  • Make decisions based on business data
  • You will learn how to use Google Sheets
  • You will understand how and why to smooth data and pick the best approach to apply
  • You will learn easy to understand, powerful forecasting techniques than can be easily implemented in a spreadsheet
  • You will learn how to transform data to make forecasting easier and more accurate
  • You will understand how to use charts to analyze data quickly and guide your analyses.
  • You will enhance your business skills and make more money!

What is the target audience?

  • This course is primarily focused on young, profesionals who want to apply forecasting methods to the business they are in, in order to improve business performance and enhance their careers by becoming the "go to" person for using a spreadsheet. This course is appropriate for professionals who already know what a spreadsheet is and are somewhat familiar with what they can do, but have not yet acquired the skills to use it for forecasting. Because this course uses the free, browser based Google Sheets, the student can learn the techniques at home, with only an investment in time. The techniques can be applied to other spreadsheets, e.g. Microsoft Office Excel or OpenOffice Spreadsheet, although this course is expected to be reconstructed for these platforms. This course is not suitable for students who are completely unfamiliar with spreadsheets, or have no interest in enhancing their career prospects by helping their company solve planning problems.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Preamble
02:18

A very brief explanation of what is in this course and how it is structured.

Section 2: Introdution
12:02

Why taking this course will:

  1. make you indispensable to your boss
  2. increase your salary
  3. improve your career prospects
07:21

This lecture explains some selected features of Google Sheets that make it an outstanding choice for use in forecasting.

  • Collaboration
  • Easy to learn
  • Browser UI
  • Embeddable in web pages
  • Zero cost
04:41

Use Google Trends to analyze what people are searching for shows that demand for learning spreadsheets is increasing. Google Sheets has exploded recently as new, powerful features have been added. catch this rising wave and surf it to financial success.

00:51

Skip this lecture if you already have a Google account. This section is to get students without an account started so that they can follow along with a spreadsheet.

01:44

Opening up a Google spreadsheet. Skip this brief lecture if you are already familiar with opening up a new spreadsheet.

2 questions

Check that student has listened to the introduction lectures

Section 3: What is Forecasting?
02:21

This lecture explains the relationship between forecasting and extrapolation and understand the difference.

  • Forecasting is a subset of extrapolation
  • Forecasting uses time data
  • Forecasting always looks forward to the future
04:59

Simple forecasting with sample slopes using the built-in SLOPE and INTERCEPT functions. Forecasting the data with a linear extrapolation Y = slope*X + intercept. [ Add resource on slopes? ]

Slopes, intercepts and Forecasting
2 questions
04:09

Simple forecasting using the built-in FORECAST function.

FORECAST function lakes argument of x and t range of data and the x value of time that you want to forecast a y value.

Forecasting volatility
1 question
Section 4: Smoothing Data
04:46

The simplest form of smoothing - averaging.

Averaging takes several values which when repeated using a window, reduces the noise to make trends easier to discern.

04:23

Avoiding the using "data from the future" when averaging.

Averaged values are placed at the last time period so that the average value never uses data from a future period.

03:41

Selecting the best moving average for periodic data,for example seasonal sales data

Use the periodicity of data as the window for averaging to maximize smoothing

08:19

The most recent information is probably the most salient.

  • Use a range of values
  • Weight each value with highest weights for most recent data
  • All weights must sum to 1.0
Averages Quiz
3 questions
05:11

Understand the idea behind exponential smoothing and how to apply it.

A simple method that takes the previous estimate and actual data to make a new estimate of the current data.

Exponentially smoothed forecast
1 question
04:21

Why smooth data before forecasting?

  • Provides user with better estimation of trendline type
  • Reduces time experimenting for trendline selection
  • Indicates likely data transformation steps
Section 5: Extracting Trends
09:53

Overview of the 3 different trendline types

  • Linear : y = a*X + b
  • Exponential : y = a*e^b
  • Polynomial : y = a(n) x^n + a(n-1) X^(n-1)..... b
06:32

The simplest trendline type and how to forecast from it.

  • y = a*X + b
  • easy to calculate slope and intercept
  • functions in spreadsheet do 'best fit' calculation for slope and intercept
02:33

Adding non-linear trendlines to charts.

  • Use scatter chart
  • select non-linear type - e.g. exponential
  • view best fit with R^2 value
  • Does the trendline look like a good fit? - tests
05:56

A brief overview of how to determine a non linear trend in data. Shows how to recognize a good trend line and how to use log plots to get linear trends from more complex data.

  • Types of trendlines
  • Tests for good fit of trendline
  • Transforming data with logarithms to make better trendline fitting
11:51

Transforming data to logarithms to look for linear trendlines

  • Logarithms single or double can help with trendline selection
  • Double log plots often allow linear trendlines to be fitted
14:38

Looks at a real world example - forecasting future quarterly dividends from historic data downloaded from a financial portal.

  • General Electric dividend data used as example to forecast future dividend stream
17:05

Forecasting CO2 concentration. Shows a real world example using published data and illustrates the limitation of models and the need for checking model parameters due to sensitivity.

  • Use of real CO2 data from Hawaii observatory
  • Fitting a trendline to data to forecast future CO2 levels
  • Discussion of limitations of fitted terndline
09:04

Experience effects on costs. Example for forecasting future costs based on a small data sample of time to produce widgets and how to forecast future costs as labor gains manufacturing experience.

Extracting Trends
6 questions
Section 6: Non Seasonal and Seasonal Sales Data Forecasting
09:16

Forecasting non-seasonal data with moving averages and trend lines

  • Plot data
  • Create moving averages
  • Use MAVs to select trendline
05:43

Explains how to do seasonal forecasting using comparable data and trendlines

  • Separating each season so that trendlines can be built for each season independently.
  • Least assumptions used
  • Cost of limited data
12:13

A different approach to seasonal forecasting de-seasonalizing data, creating a baseline and then forecasting against the baseline.

  • Use averages to ascertain trendline type
  • Add trendline as a baseline
  • Create seasonal factors for each season
  • Forecast using baseline and seasonal factors
Sales Forecasting
3 questions
Section 7: Charts - SHOULD THIS BE EARLIER B4 ANALYSIS?
11:33

Compares the main chart types used for forecasting

  • Trend
  • Line chart
  • Scatter plot
  • Features, advantages or each is discussed
Charts
5 questions
Section 8: Modeling Profits and Cash Flow
07:47

This lecture looks at tracking down a small company's overdraft problem. It builds on the forecasting already learned to investigate how a profitable company can have a deepening overdraft and how to fix it.\

  • Chart P&L
  • Estimate seasonal cash flow
  • Chart cash flow
  • Reduce cash outflows during low season sales
Section 9: Creating Your Own Functions
07:39

This lecture introduces scripting to create custom functions. It will show you how to code a simple exponential smoothing function that we met in lecture 12.

06:44

The lecture will create a custom function that uses logic to determine an output.

  • If..then logic coded in a simple custom functiom
  • demonstrates that it works identically to spreadsheet built in function composition
Scripting
4 questions
Section 10: Other Tasks
07:18

Demonstrating uploading an external Excel spreadsheet and downloading it again.

  • How to upload a spreadsheet
  • Compatibility issues between spreadsheet platforms
  • Download file types
04:50

Demonstration of how collaboration works.

  • How to add collaborators
  • Editing the live document by collaborators in different locations
  • Spreadsheet updating response viewed in different locations
12:32

This lecture shows you how to make a spreadsheet the source of live data to a web page.

  • creating a simple spreadsheet
  • creating a web page stub to view the spreadsheet
  • setting the cell range for viewing
  • viewing teh completed document
4 questions

Quiz on "Other Tasks" section

Section 11: 12: Avoiding Analytic Mistakes
03:21

Overview of 2 common issues in analysis.

05:31

This lecture will explain why data can imply high correlations when that is not the case. After this lecture, you will understand what to do to remove trends in data due to other factors before testing the correlation between data sets.

06:26

After lecture the student should be able to recognize common issues with data that cause spurious correlations and avoid errors.

Section 12: Summing Up
03:10

A brief summary of what was covered, and a note about new lecture content that will be added.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Alex Tolley, University Lecturer

Alex Tolley: Analysis and Coding Expert

I love analyzing data and finding informative patterns! In business, forecasting is a very important tool for planning. Companies can plan for growth, reduce unnecessary losses, estimate production costs, schedule staff efficiently, arrange for financing. and a host of other tasks that can be done. Forecasting helps increase profits for your business. Because it is so important, employees who can help with forecasting are a valuable asset, so learning how to forecast will lead to salary increases and promotions.

Spreadsheets

I grew up when computers were big machines in air-conditioned glass rooms attended by a priesthood of computer staff. I wrote my first programs on punch cards. By the late 1970's I was writing programs on an Apple II and was captured by the power of early spreadsheets, like Visicalc and Lotus 1-2-3 to do calculations, test business models and profits. By mid-1980's I was designing spreadsheets to analyze stock market data, analyzing company performance as an analyst and for trading in stock options.

Software Engineering

By the late 1990's, I had turned my interests back to biology, writing enterprise sized applications to do bioinformatics on the newly emerging science of genomics. Despite the power of such systems, spreadsheets remained a useful tool to generate ideas which then could be written in software.

Analysis

Whether I was supporting businesses by analyzing data, or supporting scientists trying to understand their data, there has been an explosion in data analysis. This has been driven by more capable computers, many more sources of electronic data suitable for analyzing, and many more areas that have data that can be usefully analyzed. I've developed tools and models in a number of my areas of interest.

Teaching

I have taught at the University of California, offering my insights to both biology and business majors. I love teaching, especially honing my skills in conveying information and knowledge to the next generation. I hope that this enthusiasm shows in this and other courses.

Ready to start learning?
Take This Course