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
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?
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
A very brief explanation of what is in this course and how it is structured.
Why taking this course will:
This lecture explains some selected features of Google Sheets that make it an outstanding choice for use in forecasting.
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.
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.
Opening up a Google spreadsheet. Skip this brief lecture if you are already familiar with opening up a new spreadsheet.
Check that student has listened to the introduction lectures
This lecture explains the relationship between forecasting and extrapolation and understand the difference.
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? ]
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.
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.
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.
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
The most recent information is probably the most salient.
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.
Overview of the 3 different trendline types
The simplest trendline type and how to forecast from it.
Adding non-linear trendlines to charts.
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.
Transforming data to logarithms to look for linear trendlines
Looks at a real world example - forecasting future quarterly dividends from historic data downloaded from a financial portal.
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.
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.
Forecasting non-seasonal data with moving averages and trend lines
Explains how to do seasonal forecasting using comparable data and trendlines
A different approach to seasonal forecasting de-seasonalizing data, creating a baseline and then forecasting against the baseline.
Compares the main chart types used for forecasting
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.\
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.
The lecture will create a custom function that uses logic to determine an output.
Demonstrating uploading an external Excel spreadsheet and downloading it again.
Demonstration of how collaboration works.
This lecture shows you how to make a spreadsheet the source of live data to a web page.
Quiz on "Other Tasks" section
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.
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.
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.
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.
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.