Joshua Emmanuel · YouTube · 3:40
This 120-second Excel tutorial segment shows how to prepare quarterly sales data for a seasonality-only regression forecast. The presenter converts a wide year-by-quarter table into long format, plots the series to argue that it has a repeating seasonal pattern but no trend, defines quarterly indicator variables Q1-Q3 with quarter 4 as the omitted reference category, builds the dummy matrix using =IF(1,1,0), and opens the Excel Regression tool with Input Y Range 2:18 and Input X Range 2:18. The clip ends before the fitted regression output is displayed. This 100-second clip demonstrates seasonal forecasting with quarterly dummy variables in Excel. It runs a regression of Sales on Q1, Q2, and Q3, extracts the coefficients, writes the fitted equation , and computes quarterly forecasts 70, 81.5, 116.5, and 107.75. The video then verifies that these forecasts equal simple quarterly averages, converts the forecasts into ratios and seasonal indices, interprets indices minus 1 as percent deviations from average, and allocates a projected annual total of 420 across quarters.
Use the learning inspector for key ideas and moments, or open the reading tabs for the complete notes.
Generated from the video's visuals and explanation; not verbatim speech.
The segment opens by naming the forecasting problem: quarterly data that repeat by season but do not drift upward or downward over time. Visually, the worksheet already contains a 4-by-4 sales table, and the title graphics foreground the phrases seasonality without trend, Forecasts, Regression, and seasonal index. This establishes the target model class before any computation begins.
The presenter first reorganizes the data. The original wide matrix, with years as rows and quarters as columns, is copied and pasted transposed so the years can be treated as columns during the rebuild. Then the old layout is removed and replaced by a long-format table with headings Year, Quarter, and Sales. Mathematically, this changes the representation from a matrix of observations to one record per time period, which is the form required for regression input.
To populate the quarter index efficiently, the formula =SEQUENCE(4) is entered, producing the vertical array 1, 2, 3, 4. That block is then copied for the remaining years, and the Sales values are dragged from the original table into the new structure. The result is a single-column dependent variable paired with categorical time labels, making the dataset ready for both plotting and model design.
Next, the Sales series is graphed using a scatter chart with straight lines and markers. The visual pattern repeats across successive years, which the narrator interprets as seasonality. At the same time, the levels do not show a sustained rise or fall, so the narrator concludes there is no trend. This chart-based diagnosis justifies the next modeling choice: use seasonal indicators rather than a time-trend regressor.
The clip then introduces indicator variables. On screen, Q1, Q2, and Q3 are defined as binary variables that equal 1 in their respective quarters and 0 otherwise. Quarter 4 is not given its own column; instead it is identified as the reference or omitted category, represented by . In regression terms, this means the baseline level corresponds to quarter 4, and the dummy coefficients will measure deviations from that baseline.
To implement the definitions in Excel, the presenter places the quarter numbers 1, 2, 3 above the dummy columns and enters =IF(1,1,0). The mixed references are crucial: 1 keeps the header row fixed while allowing the column to change. Dragging this formula fills the whole Q1-Q3 block with the expected one-hot patterns, while quarter-4 rows become all zeros.
With the design matrix complete, the workflow moves to estimation setup. The presenter opens Data Analysis, selects Regression, and assigns Input Y Range = 2:18 for Sales and Input X Range = 2:18 for the three indicator columns. The Labels box is checked because the selected ranges include header cells. The segment ends at this point, before the fitted coefficients, seasonal indices, or forecasts are shown.
The clip opens in Excel with a quarterly sales table and dummy columns Q1, Q2, and Q3. The Regression tool is configured with Sales as the input Y range and the three dummy columns as the input X range, and the output is placed on the same worksheet.
After the regression summary appears, only the coefficient block is kept: Intercept 107.75, Q1 -37.75, Q2 -26.25, and Q3 8.75. The rest of the output is deleted so the forecasting setup can proceed from these numbers alone.
Using those coefficients, the fitted model is written as . Because Quarter 4 is the omitted category, it is represented by .
The next step substitutes each quarter's dummy pattern into the equation. Quarter 1 uses and gives 70; Quarter 2 uses and gives 81.5; Quarter 3 uses and gives 116.5; Quarter 4 uses and gives 107.75.
The speaker notes that these are forecasts for any year because the model contains no trend term. That means the seasonal pattern is treated as repeating identically from year to year.
A verification follows by computing the mean sales within each quarter directly from the raw data. The AVERAGE formula selects the Quarter 1 observations, then the method is extended to the other quarters, producing the same four forecasts.
The four quarterly forecasts are then summed to 375.75. Each forecast is divided by this total to form a ratio, and because there are four seasons, each ratio is multiplied by 4 to create the seasonal indices.
The resulting indices are 0.745176, 0.867598, 1.240186, and 1.147039, and they add to 4. Subtracting 1 from each index converts them into percentage deviations from average: -25%, -13%, 24%, and 15%.
Finally, the clip applies the seasonal shares to a projected annual total of 420. Multiplying each quarterly ratio by 420 allocates the year into 78.24351, 91.0978, 130.2196, and 129.4391, which sum back to 420.
This is the modeling context of the clip: a quarterly time series repeats a within-year pattern but does not exhibit a persistent upward or downward movement across years. The video uses a line chart to make that distinction visible and then chooses a regression with seasonal indicators only.
The original year-by-quarter matrix is converted into rows labeled Year, Quarter, and Sales. This one-observation-per-row structure is necessary because Excel regression expects a single dependent-variable column and separate predictor columns rather than a wide seasonal table.
Q1, Q2, and Q3 are binary predictors that encode quarter membership. Each equals 1 only in its own quarter and 0 otherwise, allowing the regression to estimate separate seasonal levels for quarters 1 through 3.
Quarter 4 is deliberately left out of the explicit dummy set and serves as the baseline. Observations in quarter 4 are represented by the all-zero vector, so the intercept captures the quarter-4 level and the other coefficients capture differences from it.
The formula =IF(1,1,0) compares each row's quarter in column B with the quarter label in row 1. Mixed absolute references let one formula be copied across the whole block, generating the full design matrix automatically.
The Data Analysis Regression tool is configured with Sales as the dependent variable and Q1-Q3 as the predictors. The selected ranges include headers, so Labels is checked before estimation.
The video fits a regression using quarterly dummy variables rather than a trend term. The displayed equation is built from the Excel coefficients and treats Quarter 4 as the baseline category.
Each quarter's forecast is found by inserting its dummy pattern into the regression equation. The results shown are 70 for Quarter 1, 81.5 for Quarter 2, 116.5 for Quarter 3, and 107.75 for Quarter 4.
Because the model contains only seasonal dummies and no time trend, the same quarterly forecast values repeat for any future year.
In this example, the regression forecasts match the simple arithmetic means of the historical sales within each quarter. The clip demonstrates this with an AVERAGE formula over the Quarter 1 rows and then for the remaining quarters.
Seasonal indices are formed by taking each quarterly forecast, dividing by the annual forecast total, and multiplying by 4. This normalization makes the four indices sum to 4.
To interpret the indices more directly, subtract 1 from each seasonal index. The displayed results are -25%, -13%, 24%, and 15%, meaning below or above average quarterly demand.
Given a projected annual total of 420, the clip allocates sales by quarter using the seasonal ratios. The resulting quarterly estimates are 78.24351, 91.0978, 130.2196, and 129.4391.
Explore conditions, steps and evidence. Supplementary explanations are labeled separately from content shown in the video.
The worksheet is reorganized into columns labeled Year, Quarter, and Sales.
The narrator says to type Year in the first column, Quarter in the second, and title the time series data Sales.
Year, Quarter, Sales
Column headings for the long-format time series table: year index, quarter within the year, and observed sales value.
Year = 1,2,3,4; Quarter = 1,2,3,4; Sales = numeric quarterly observations.
The formula bar shows =SEQUENCE(4).
The narrator says this returns an array of one to four in four rows.
=SEQUENCE(4)
Excel function call that generates a vertical array containing 1, 2, 3, 4.
Used here to populate the Quarter column for one year.
On-screen text defines if quarter 1; 0 otherwise, if quarter 2; 0 otherwise, if quarter 3; 0 otherwise.
The narrator says to define indicator variables Q1, Q2, and Q3.
Q1, Q2, Q3
Quarterly indicator (dummy) variables used as regression predictors for quarters 1, 2, and 3.
Each variable takes values in {0,1}.
On-screen text states Q4 -> .
The narrator calls quarter 4 the reference or omitted category where Q1, Q2, Q3 are all zeros.
Q4
Reference or omitted category for the seasonal regression; represented implicitly by rather than by its own dummy column.
Applies when the observation belongs to quarter 4.
The entered formula is =IF(1,1,0).
The narrator explains that if the quarter number in column B matches the column number in row 1, return 1; otherwise return 0.
=IF(1,1,0)
Excel conditional formula that creates a dummy variable by comparing the current row's quarter in column B with the quarter label in row 1.
1 fixes row 1 while allowing the column to change.
The Regression dialog shows Input Y Range: 2:18.
The narrator says to select the time series values for the Y range.
2:18
Selected dependent-variable range containing the Sales observations for the regression.
Rows 2 through 18 of column C, i.e. 17 cells including the header cell C2.
The Regression dialog shows Input X Range: 2:18.
The narrator says to select the indicator values for the X range.
2:18
Selected predictor range containing the three quarterly dummy-variable columns Q1, Q2, Q3.
Rows 2 through 18 of columns D through F, i.e. a 17-by-3 block including the header row.
On-screen equation shows . The audio says the regression equation is as shown.
Fitted or forecast sales value from the seasonal dummy-variable regression.
Sales values for quarters in a year
Regression output lists Intercept coefficient 107.75.
The displayed equation uses 107.75 as the constant term.
107.75
Intercept of the fitted regression model; it is the baseline forecast when all quarter dummies are zero.
Real number
Spreadsheet columns D, E, and F are labeled Q1, Q2, and Q3 with binary entries.
Equation contains the term .
For quarter one, only Q1 will be one, Q2 and Q3 will be zero.
Dummy variable equal to 1 for Quarter 1 and 0 otherwise.
Binary indicator {0,1}
Spreadsheet column E is labeled Q2 with binary entries.
Equation contains the term .
For quarter two, Q2 is 1 while Q1 and Q3 are 0.
Dummy variable equal to 1 for Quarter 2 and 0 otherwise.
Binary indicator {0,1}
Spreadsheet column F is labeled Q3 with binary entries.
Equation contains the term .
For quarter three, Q3 is 1 while Q1 and Q2 are 0.
Dummy variable equal to 1 for Quarter 3 and 0 otherwise.
Binary indicator {0,1}
The narrator introduces the topic as developing forecasts in Excel for time series data with seasonality but without trend.
A green banner reads seasonality without trend, and later overlays on the chart read seasonal pattern, no trend, and Seasonality without Trend.
This clip frames a forecasting problem in which quarterly observations repeat a seasonal pattern across years but do not show a persistent upward or downward movement over time. The chosen model class is therefore a seasonal regression using indicator variables rather than a trend-plus-seasonality model.
The data are quarterly time series observations.
The series exhibits repeating seasonal behavior.
The series does not exhibit a visible increasing or decreasing trend over time.
The narrator copies the table, pastes transposed so years can be in columns, then rebuilds a table with Year, Quarter, and Sales.
The original matrix with Year 1-Year 4 rows and Quarter 1-Quarter 4 columns is converted into a long table with repeated Year and Quarter labels and one Sales column.
The video demonstrates converting a wide quarterly table into a long-format dataset with one row per time period. This reshaping is necessary because regression tools expect a single dependent variable column and separate predictor columns rather than a year-by-quarter matrix.
Start from a wide table with years as rows and quarters as columns.
Create one record per year-quarter combination.
Keep the observed value in a single Sales column.
The narrator says, To use regression, we first define indicator variables.
On-screen definitions assign 1 to the matching quarter and 0 otherwise for Q1, Q2, and Q3.
Indicator variables, also called dummy variables, encode categorical quarter membership numerically. In this example Q1, Q2, and Q3 each equal 1 only for their respective quarter and 0 otherwise, allowing a linear regression to estimate separate seasonal levels for those quarters relative to a baseline quarter.
The categorical variable is quarter of the year.
One category is reserved as the reference category.
The remaining categories receive explicit indicators.
The narrator says quarter 4 will be the reference or omitted category where Q1, Q2, Q3 are all zeros.
The screen shows Q4 -> .
When encoding a categorical variable with dummies, one category is left out and serves as the baseline. Here quarter 4 is the omitted category, so observations in quarter 4 are represented by the vector (Q1,Q2,Q3)=(0,0,0). The intercept then corresponds to the mean level for quarter 4, and the dummy coefficients measure differences from that baseline.
There are four mutually exclusive quarterly categories.
Exactly one category is omitted from the explicit dummy set.
The omitted category is identified as the reference group.
The formula =IF(1,1,0) is typed and filled across the dummy-variable block.
The narrator explains the dollar signs restrict references to column B and row 1 so the same formula can be dragged across rows and columns.
The video builds the indicator matrix by comparing each row's quarter label in column B with each column's quarter number in row 1. Mixed absolute references keep the quarter source fixed in column B while moving down rows, and keep the comparison header fixed in row 1 while moving across columns, so one formula can populate all dummy columns.
Column B contains the quarter number for each observation.
Row 1 above the dummy block contains the quarter numbers 1, 2, 3.
The formula is copied downward and rightward to fill the Q1-Q3 matrix.
The narrator instructs to click Data, Data Analysis, select Regression, choose the time series values for Y and the indicator values for X, and check Labels.
The Regression dialog displays Input Y Range: 2:18 and Input X Range: 2:18.
After constructing the dummy variables, the clip sets up an ordinary least squares regression in Excel. The dependent variable is the Sales column, the independent variables are the three quarterly indicator columns, and the Labels option is enabled because the selected ranges include header cells.
Use the Excel Data Analysis Regression tool.
Set Input Y Range to the Sales column.
Set Input X Range to the Q1-Q3 dummy columns.
Check Labels when the selected ranges include headers.
Excel data table has Year, Quarter, Sales, and dummy columns Q1, Q2, Q3.
Regression output and displayed equation use Intercept plus coefficients on Q1, Q2, Q3.
Speaker refers to the regression equation and forecasts using these coefficients.
The video uses a multiple regression model with quarterly dummy variables to estimate seasonality without an explicit trend term. The fitted equation is written directly from the Excel regression output.
Quarterly data are represented by binary indicators Q1, Q2, and Q3.
Quarter 4 is the omitted reference category, so it corresponds to .
The model shown in this clip has no separate trend variable.
Overlays show substitution of (1,0,0), (0,1,0), (0,0,1), and (0,0,0) into the regression equation.
Forecast cells display 70, 81.5, 116.5, and 107.75.
Speaker says these will be the forecast for any year since there is no trend.
To forecast a quarter, substitute the appropriate dummy pattern into the regression equation. Quarter 1 uses , Quarter 2 uses , Quarter 3 uses , and Quarter 4 uses all zeros because it is the baseline category.
Use the same fitted coefficients as in the displayed regression equation.
Because no trend term is included, the same quarterly forecasts apply to any future year.
Ratio formula =H11/15 and S.Index formula = are visible.
Speaker says to sum the forecasts, calculate ratios by dividing each forecast by the total, then multiply the ratios by four to obtain seasonal indices.
Ratio values and S.Index values are displayed side by side.
The clip defines a seasonal index operationally as four times the share of a quarter's forecast in the total annual forecast. This rescales the quarterly proportions so that the four seasonal indices sum to 4.
There are four seasons per year.
The quarterly forecasts used here are the values 70, 81.5, 116.5, and 107.75.
The resulting indices should add up to 4.
Speaker says for easy interpretation, subtract one from each seasonal index and convert them to percentages.
Formula bar shows =J11-1.
Column header reads from ave. and values are formatted as -25%, -13%, 24%, and 15%.
After computing seasonal indices, the video converts each index into a percentage deviation from the average level by subtracting 1. A negative result means the quarter is below average, and a positive result means it is above average.
Seasonal indices are already normalized so that their average is 1.
Results are interpreted as signed percentages relative to average quarterly demand.
The narrator says the chart shows a pattern indicating seasonality but no trend since the values are not increasing or decreasing over time.
The scatter plot with straight lines and markers repeats a wave-like quarterly pattern across the horizontal axis, and overlays read seasonal pattern and no trend.
For this dataset, the plotted quarterly series shows a repeating seasonal pattern but no visible increasing or decreasing trend over time.
The plotted series is the Sales column arranged in chronological order.
The chart uses a line-and-marker display suitable for inspecting temporal shape.
The claim is about the displayed sample series in this worksheet, not about all quarterly data.
The narrator concludes that the appropriate model is one with seasonality but without trend.
The overlay Seasonality without Trend appears directly after the chart inspection.
Because the series exhibits seasonality without trend, the appropriate regression model in this clip is a seasonality-only model built from quarterly indicator variables.
The time series has repeating quarterly effects.
The time series lacks a systematic upward or downward trend component.
The statement applies to the model-selection decision for the dataset shown in the video.
Speaker states: This will be the forecast for any year since there is no trend.
Displayed regression equation contains only intercept and quarter dummies, with no time or trend variable.
If the fitted seasonal model contains only quarterly dummy variables and no trend term, then the same quarterly forecasts apply to any future year.
The model is the displayed equation .
No additional trend variable is included in the model.
For any future year and for each quarter, the forecast depends only on the quarter's dummy pattern.
Speaker says those should add up to four.
The four S.Index values shown are 0.745176, 0.867598, 1.240186, and 1.147039.
When ratios of quarterly forecasts to the annual forecast total are multiplied by 4, the four resulting seasonal indices add up to 4.
There are exactly four seasons.
Each ratio is defined as the quarter forecast divided by the sum of the four quarter forecasts.
For the four quarters ,2,3,4, .
Speaker says the forecasts can also be obtained by simply averaging the values for each quarter and later says the results are the same.
Formula bar shows =AVERAGE(C3,C7,C11,C15) for Quarter 1.
Both methods display 70, 81.5, 116.5, and 107.75.
In this example, the quarterly forecasts from the dummy-variable regression are identical to the arithmetic means of the historical sales within each quarter.
The regression uses quarterly dummy variables only.
The historical data are grouped by quarter across four years.
For each quarter q, the regression forecast equals the sample mean of the observed sales in that quarter.
The formula =IF(1,1,0) is entered once and filled across the Q1-Q3 block.
After filling, the dummy columns contain rows such as (1,0,0), (0,1,0), (0,0,1), and (0,0,0) corresponding to quarters 1, 2, 3, and 4.
The setup compares each observation's quarter number with the header number above each dummy column.
Explicitly described by the narrator when introducing the IF function.
If the row's quarter equals the column's quarter label, output 1; otherwise output 0.
This is the exact Excel formula shown in the formula bar.
Filling the formula produces one-hot rows for quarters 1, 2, and 3, and an all-zero row for quarter 4.
Visible in the completed dummy-variable block after the formula is dragged across cells.
The worksheet now contains a valid set of quarterly indicator variables with quarter 4 serving as the implicit reference category.
The narrator says to select the time series values for Y and the indicator values for X.
The Regression dialog shows 2:18 for Input Y Range and 2:18 for Input X Range.
The analysis is launched from Excel's Data Analysis toolpak.
Shown by the menu navigation and dialog opening.
The dependent variable range is the Sales column.
Visible in the Regression dialog and stated by the narrator.
The predictor range is the block of Q1, Q2, and Q3 dummy columns.
Visible in the Regression dialog and stated by the narrator.
Header cells are treated as labels rather than data.
The narrator explicitly says to check Labels because titles were selected.
The prepared long-format table and dummy variables are correctly mapped into an Excel regression specification for seasonal forecasting.
Overlays explicitly write the four substitution expressions for Q1 through Q4.
Forecast cells show the numerical results 70, 81.5, 116.5, and 107.75.
Start from the fitted regression equation shown on screen.
Observed formula from the video.
For Quarter 1, set and .
Audio explains that only Q1 will be one for quarter one.
Evaluate the Quarter 1 expression to get the forecast 70.
Arithmetic evaluation of the displayed expression.
For Quarter 2, set and the other dummies to 0, then evaluate.
Displayed overlay and resulting cell value.
For Quarter 3, set and the other dummies to 0, then evaluate.
Displayed overlay and resulting cell value.
For Quarter 4, all dummies are 0, so the forecast equals the intercept.
Displayed overlay and resulting cell value.
The quarterly forecasts are 70, 81.5, 116.5, and 107.75.
Formula bar shows =SUM(H11:H14), =H11/15, and =.
Displayed values are total 375.75, ratios 0.186294, 0.2169, 0.310047, 0.28676, and indices 0.745176, 0.867598, 1.240186, 1.147039.
Add the four quarterly forecasts to obtain the annual forecast total.
Visible SUM formula and displayed total.
Divide each quarterly forecast by the annual total to get its share.
Visible formula =H11/15 and audio explanation.
These are the displayed ratios for Quarters 1 through 4.
Values shown in the Ratio column.
Multiply each ratio by 4 because there are four seasons.
Audio states this rule and formula bar shows =.
These are the resulting seasonal indices.
Values shown in the S.Index column.
The seasonal indices are 0.745176, 0.867598, 1.240186, and 1.147039, and they sum to 4.
Speaker says the forecasts can also be obtained by simply averaging the values for each quarter.
Formula bar shows =AVERAGE(C3,C7,C11,C15).
The second forecast block reproduces 70, 81.5, 116.5, and 107.75.
Average the observed sales for Quarter 1 across the four years.
Visible AVERAGE formula selects the Quarter 1 rows.
The Quarter 1 average equals 70.
Displayed result matches the regression-based forecast.
Drag the averaging method down to the remaining quarters.
Audio says drag down for the rest, and the displayed values match the earlier forecasts.
Simple quarterly averaging gives the same forecasts as the dummy-variable regression in this example.
The worksheet begins with a 4-by-4 table of quarterly sales values for Year 1 through Year 4.
The narrator walks through copying, transposing, rebuilding, charting, creating dummies, and opening regression on this same dataset.
The final fitted regression output is not shown within this 120-second clip.
Given a small quarterly sales table spanning four years, prepare the data in Excel and set up a regression model for seasonality without trend.
Initial table rows are Year 1, Year 2, Year 3, Year 4.
Initial table columns are Quarter 1, Quarter 2, Quarter 3, Quarter 4.
Visible values include Year 1: 72, 83, 116, 113; Year 2: 69, 74, 112, 106; Year 3: 70, 89, 120, 108; Year 4: 69, 80, 118, 104.
Transform the data into regression-ready form, identify the seasonal-only pattern, construct quarterly indicator variables, and configure the Excel Regression tool.
The wide year-by-quarter matrix is first rearranged so years can be handled as columns during the rebuild.
Stated by the narrator and visible in the worksheet actions.
The data are rewritten into long format with one row per time period.
Visible in the new table structure and explained in the audio.
The quarter labels 1 through 4 are generated for the first year block and then copied for later years.
The formula is shown in the formula bar and described by the narrator.
The Sales series is plotted to inspect temporal structure.
The chart insertion path and resulting plot are shown on screen.
Three explicit dummy variables are defined for quarters 1-3.
Displayed as on-screen text and spoken by the narrator.
The dummy matrix is generated automatically by comparing each row's quarter with each column header.
The formula entry and fill operation are visible.
The regression tool is configured using Sales as Y and the three dummies as X.
Shown in the Regression dialog and narrated step by step.
By the end of the clip, the dataset has been reshaped, visually diagnosed as seasonal without trend, augmented with Q1-Q3 indicator variables, and loaded into the Excel Regression dialog ready for estimation.
Verification within this clip is visual and procedural: the chart supports the no-trend seasonal reading, the filled dummy matrix shows the expected one-hot patterns, and the Regression dialog displays the intended Y and X ranges with Labels enabled.
Overlay shows Q1 Forecast = .
Forecast cell for Quarter 1 displays 70.
Speaker explains that for quarter one only Q1 is 1 and the others are 0.
Use the fitted regression equation to forecast Quarter 1 sales.
For Quarter 1, , , .
Compute the Quarter 1 forecast.
Substitute the Quarter 1 dummy pattern into the regression equation.
Direct substitution from the displayed model.
Evaluate the expression.
Arithmetic simplification.
70
The spreadsheet forecast cell for Quarter 1 shows 70.
Visible formulas compute total 375.75, ratios, seasonal indices, deviations from average, and allocation using =.
Displayed outputs include -25%, -13%, 24%, 15% and allocated values 78.24351, 91.0978, 130.2196, 129.4391.
Speaker explains multiplying ratios by four, subtracting one for interpretation, and allocating projected annual sales of 420 using the seasonal ratios.
Given the four quarterly forecasts and a projected annual total of 420, compute seasonal indices, interpret them as percent deviations from average, and allocate the annual total by quarter.
Quarterly forecasts: 70, 81.5, 116.5, 107.75
Annual forecast total: 375.75
Projected annual sales: 420
Find seasonal indices, percentage deviations from average, and quarterly allocations of 420.
Divide each quarterly forecast by the annual total.
Visible formula =H11/15 and spoken explanation.
Multiply each ratio by 4 to obtain seasonal indices.
Visible formula = and spoken rule for four seasons.
Subtract 1 from each seasonal index and format as a percentage.
Visible formula =J11-1 and spoken interpretation step.
Allocate the projected annual total using the quarterly ratios.
Visible formula = and spoken instruction to multiply the seasonal ratios by 420.
Seasonal indices: 0.745176, 0.867598, 1.240186, 1.147039. Deviations from average: -25%, -13%, 24%, 15%. Allocations of 420: 78.24351, 91.0978, 130.2196, 129.4391.
The displayed allocated values sum to 420, matching the spoken check that they should add up to 420.
A green lower banner reads seasonality without trend while the words Forecasts, Regression, and seasonal index appear over the worksheet.
Green banner
Worksheet background
Text labels Forecasts, Regression, seasonal index
Text labels appear sequentially over the spreadsheet.
The banner remains fixed at the bottom during the introduction.
The underlying quarterly sales table stays visible behind the overlays.
The visuals establish that the lesson concerns forecasting with seasonal effects but no trend, and that regression and seasonal indices are the intended tools.
A scatter chart with straight lines and markers is inserted, then overlaid with seasonal pattern, no trend, and Seasonality without Trend.
The narrator interprets the repeated wave shape as seasonality and the lack of sustained rise or fall as absence of trend.
Line-and-marker chart
Horizontal time index
Vertical sales scale
Text overlays seasonal pattern and no trend
The raw table is replaced visually by a plotted series.
Overlay annotations appear one after another to label the observed pattern.
The same Sales data are being represented, only in graphical form.
The quarterly repetition remains evident across successive years.
The graph provides the empirical basis for choosing a seasonality-only regression model instead of a trend model.
After entering =IF(1,1,0), the formula is dragged to fill the remaining cells in the Q1-Q3 block.
The completed matrix shows rows of patterns matching quarters 1, 2, 3, and all-zero rows for quarter 4.
Formula bar
Cells D3:F18
Quarter labels in column B
Quarter numbers 1, 2, 3 in row 1
A single formula expands into a full rectangular dummy matrix.
Each row becomes encoded according to its quarter.
The comparison logic remains the same for every filled cell.
Quarter 4 continues to correspond to the all-zero pattern.
The animation demonstrates how mixed absolute references let one formula generate an entire categorical encoding scheme.
The Data Analysis dialog opens, Regression is selected, and the Regression dialog displays Input Y Range 2:18 and Input X Range 2:18.
The narrator instructs the user to choose the time series values for Y, the indicator values for X, and check Labels.
Data Analysis dialog
Regression dialog
Input Y Range field
Input X Range field
Labels checkbox
The workflow moves from data preparation to model estimation setup.
The selected ranges are inserted into the regression fields.
Sales remains the dependent variable.
Q1-Q3 remain the explanatory variables.
This visual step confirms that the prepared dummy-variable design matrix is being used in an ordinary least squares regression framework.
Excel Regression dialog is open with Input Y Range 2:18 and Input X Range 2:18.
Output options show Output Range 2 before OK is clicked.
Excel worksheet with Year, Quarter, Sales, Q1, Q2, Q3
Regression dialog box
Output range field
Output range is specified as 2.
OK is clicked to generate the regression output.
The dependent variable remains Sales in column C.
The independent variables remain the dummy columns Q1, Q2, Q3.
The visual sequence shows how the seasonal dummy regression is executed in Excel.
Regression summary appears in columns K and L with Intercept 107.75, Q1 -37.75, Q2 -26.25, Q3 8.75.
The Coefficients block is copied to columns I and J and the larger output is deleted.
SUMMARY OUTPUT table
Coefficients table
Clipboard paste area
Only the coefficient block is retained for later use.
The full regression summary is removed from the sheet.
The coefficient values themselves do not change during copying.
The workflow isolates the numerical parameters needed to build the forecasting equation.
Large overlay displays .
Successive overlays display the four quarter-specific substitution lines.
Forecast column fills with 70, 81.5, 116.5, and 107.75.
Regression equation overlay
Forecast table
Coefficients table
Each quarter's dummy pattern is substituted in turn.
Forecast values appear row by row for Quarter 1 through Quarter 4.
The coefficients 107.75, -37.75, -26.25, and 8.75 remain fixed throughout.
The animation makes explicit how dummy coding translates into quarter-by-quarter predictions.
Formula bar shows =AVERAGE(C3,C7,C11,C15).
A second forecast block is created below the first and produces the same four numbers.
Red boxes highlight matching forecast values.
Original sales column C
Second forecast block
Highlight boxes
Quarter 1 sales are selected non-contiguously and averaged.
The formula is dragged down to the other quarters.
The original regression-based forecasts remain unchanged for comparison.
The visual comparison demonstrates that, in this no-trend dummy model, regression forecasts equal simple quarterly means.
Columns are added for Ratio, S.Index, from ave., and Total = 420.
Formulas =SUM(H11:H14), =H11/15, =, =J11-1, and = appear in the formula bar.
Callout labels state 25% below average and 15% above average.
Forecast column H
Ratio column I
S.Index column J
from ave. column K
Total = 420 column L
Forecasts are summed to 375.75.
Ratios are converted to seasonal indices by multiplying by 4.
Indices are converted to percentage deviations from average.
Projected annual sales 420 are allocated by quarter using the ratios.
The underlying quarterly forecasts remain 70, 81.5, 116.5, and 107.75.
The sequence shows the full transformation from fitted quarterly forecasts to interpretable seasonal indices and a practical annual allocation.
The narrator specifically identifies quarter 4 as the reference or omitted category where Q1, Q2, Q3 are all zeros.
Only Q1, Q2, and Q3 are created as explicit columns.
A common mistake is to encode all four quarters with four dummy columns Q1, Q2, Q3, Q4.
In this regression setup, one category must be omitted. Quarter 4 is intentionally left implicit and represented by (0,0,0), preventing perfect collinearity among the dummy columns and the intercept.
The narrator distinguishes a repeating seasonal pattern from the absence of increasing or decreasing values over time.
The chart is annotated separately with seasonal pattern and no trend.
Viewers may confuse regular within-year ups and downs with a long-run trend.
The video treats seasonality as the repeating quarterly shape and trend as a sustained directional movement across time. This dataset is presented as having the former without the latter.
The displayed model uses only Q1, Q2, and Q3, while Quarter 4 is forecast by setting all three dummies to 0.
Speaker explicitly describes Quarter 4 as the case where Q1, Q2, and Q3 are all zero.
One might think each of the four quarters needs its own dummy variable in the regression.
With four seasonal categories, only three dummies are used; the omitted quarter serves as the reference category, so Quarter 4 corresponds to .
S.Index values are around 1, while from ave. values are obtained by subtracting 1.
Speaker distinguishes seasonal indices from the easier-to-interpret percentages after subtracting one.
A seasonal index such as 0.745176 may be mistaken for a direct percentage deviation from average.
The index itself is a multiplicative factor around 1. To express deviation from average, subtract 1 and then read the result as a percentage, giving -25% for Quarter 1.
After rebuilding the table, the narrator moves directly to defining indicator variables for regression.
The long-format Quarter column becomes the source used by the IF formula to generate Q1-Q3.
The long-format table supplies the per-observation quarter labels needed to construct the dummy-variable columns.
The definitions of Q1, Q2, Q3 are immediately followed by the statement Q4 -> .
The dummy-variable construction includes the additional rule that the omitted fourth quarter is represented by the all-zero vector.
The abstract definitions of Q1, Q2, Q3 are implemented concretely with =IF(1,1,0).
The Excel IF method is the computational implementation of the indicator-variable definition inside the worksheet.
The Regression dialog uses 2:18, exactly the block containing Q1-Q3.
The narrator says to select the indicator values for the X range.
The regression specification depends on having already chosen quarter 4 as the omitted baseline; otherwise the X-range design would not match the stated model.
The narrator infers from the chart that the appropriate model is seasonality without trend.
The chart annotations seasonal pattern and no trend lead into the overlay Seasonality without Trend.
Recognizing that the series is seasonal but untrended motivates the subsequent use of quarterly indicator variables rather than time-trend regressors.
The equation is immediately followed by quarter-specific substitution lines.
Speaker moves from stating the regression equation to explaining how to forecast each quarter.
The fitted dummy-variable regression equation is applied by substituting the dummy pattern for each quarter to obtain forecasts.
Ratio and S.Index formulas use the forecast column values directly.
Speaker says to sum the forecasts, calculate ratios, then multiply by four to obtain seasonal indices.
Quarterly forecasts are transformed into seasonal indices by first taking their shares of the annual total and then multiplying by 4.
Column from ave. is computed as =J11-1 from the S.Index column.
Speaker says to subtract one from each seasonal index for easy interpretation.
The percentage deviation from average is obtained directly from the seasonal index by subtracting 1.
Speaker notes that the same forecasts can be obtained by averaging values for each quarter.
Two separate forecast blocks display identical results.
The clip contrasts the regression route with a simpler direct averaging route and shows that both yield the same quarterly forecasts in this no-trend setting.
The displayed equation contains only intercept and quarter dummies.
Speaker says the forecasts apply to any year since there is no trend.
The claim that the same forecasts apply to any year depends on the model having no trend term, which is visible in the displayed equation.
The narrator explicitly names the topic as time series data with seasonality but without trend.
The wide table is converted into Year, Quarter, Sales columns before charting and regression.
The formula =IF(1,1,0) is shown and filled across the dummy block.
The narrator calls quarter 4 the reference or omitted category.
The Regression dialog shows Input Y Range 2:18 and Input X Range 2:18.
The chart is annotated with seasonal pattern and no trend.
Large on-screen equation is shown.
Substitution overlays for Q1 through Q4 are visible.
Speaker says there is no trend.
Ratio and S.Index formulas are visible in the formula bar.
Column from ave. is computed by subtracting 1 from S.Index.
Callouts show 25% below average and 15% above average.
Formula bar shows =.
Speaker says to multiply the seasonal ratios by 420.
Covered · Introductory narration and title graphics establish the topic of forecasting seasonality without trend.
Covered · The original wide table is copied, transposed, and rebuilt into Year, Quarter, Sales long format.
Covered · A line-and-marker chart is inserted and interpreted as showing seasonality with no trend.
Covered · The video defines Q1, Q2, Q3 and identifies quarter 4 as the omitted reference category.
Covered · The dummy matrix is generated with =IF(1,1,0) and filled across the worksheet.
Covered · Excel Data Analysis Regression is opened and configured with Sales as Y and Q1-Q3 as X, with Labels checked.
Covered · Excel regression setup with Sales as Y and Q1-Q3 dummies as X variables.
Covered · Regression output appears and only the coefficient block is copied for later use.
Covered · The fitted equation is displayed on screen.
Covered · Quarter-by-quarter substitution produces forecasts 70, 81.5, 116.5, and 107.75.
Covered · Alternative calculation by averaging each quarter's historical sales gives the same forecasts.
Covered · Forecasts are summed, converted to ratios, and multiplied by 4 to form seasonal indices.
Covered · Subtracting 1 from each index yields percentage deviations from average.
Covered · Projected annual sales of 420 are allocated to quarters using the seasonal ratios.
Covered · Closing remarks only; no new mathematical content.
Reviewed subject paths
Continue with related videos in the same subject.
YouTube10:40
YouTube8:20
YouTube6:05