ACT$2292$ Excel Assignment

Forecasting Future Performance $($Sales and Earnings for IBM$)$

Lab Insight: There are many reasons to forecast future performance for a firm. It may be predicting

sales to determine needed manufacturing capacity. It may be predicting cash flows to determine if we

will need a loan or have sufficient cash on hand without getting a loan. An investor might be forecasting

earnings to determine whether there will be a good investment.

The goal of this lab is to forecast sales and earnings for IBM and Microsoft using Excel$$s Forecast Sheet.

Required:

$1.$ Using Excel$$s forecasting sheet, forecast future sales for IBM.

$2.$ Using Excel$$s forecasting sheet, forecast future income before extraordinary items for IBM.

Ask the Question: What will be the company$$s sales and earnings for $2022,2023,$ and $2024$ given

quarterly sales and earnings for IBM given times series $($past quarterly sales and earnings from $2010$ to

$2021)?$

Master the Data: Looking at IBM$$s financial statements, we accumulate the values of quarterly sales and

earnings from $2010$ to $2021.$

Here is the data dictionary.

Open Excel File ACT$2292$ Excel Assignment Data.xlsx

Data Dictionary:

Date: Quarter Begin Date

IBM Sales: Net sales for IBM during the respective quarter

Date: Quarter Begin Date

Income from Extraordinary Items: Income before extraordinary items for IBM during the respective

quarter

Perform the Analysis:

$1.$ To start the forecast analysis, highlight one of the numbers in column B $($for sales$),$ and then click

on Data $>$ Forecast $>$ Forecast Sheet. The following dialog box will open up beginning our

forecast of IBM sales for $2022,2023,$ and $2024.$ The forecast sheet is only available on Windows,

and not on Mac machines. You may need to use a computer lab or a virtual desktop to get

access to this functionality that is only available on Windows.

$2.$ Note the seasonality of the data with the reasonably consistent crest and trough over the four

quarters. Click on the chevron next to the Options chevron near the bottom left.

$3.$ Consider the various options, including the dates forecast start and end, the confidence interval

$(95\%),$ the timeline range and value range as well as how missing points and duplicates are handled.

$4.$ Select $$Include forecast statistics$$ near the bottom left and select the button at the bottom $$Create$$

button at the bottom.

$5.$ Note the new tab with forecast statistics with forecasts for each quarter in $2022,2023$ and $2024.$

Note the lower and the upper confidence bound of the forecast. The forecast sheet suggests that

there is a $95\%$ chance that the actual sales will be in the range between the lower and the upper

confidence bound$\mathrm{.}2.$ Note the seasonality of the data with the reasonably consistent crest and trough over the four

quarters. Click on the chevron next to the Options chevron near the bottom left.

$3.$ Consider the various options, including the dates forecast start and end, the confidence interval

$(95\%),$ the timeline range and value range as well as how missing points and duplicates are handled.

$4.$ Select $$Include forecast statistics$$ near the bottom left and select the button at the bottom $$Create$$

button at the bottom.

$5.$ Note the new tab with forecast statistics with forecasts for each quarter in $2022,2023$ and $2024.$

Note the lower and the upper confidence bound of the forecast. The forecast sheet suggests that

there is a $95\%$ chance that the actual sales will be in the range between the lower and the upper

confidence bound.

$6.$ The forecast graph should look as follows:

$7.$ Note on the graph the widening between the confidence bounds. Calculate the difference between

the lower and the upper bound, noting over time the difference between the lower and upper

confidence bounds get larger since the further we get out in time, the less certainty we have of the

outcome.

a$.$ At $3/31/2022,$ the difference between upper and lower confidence bound is $18,441\mathrm{.}04$

$12,569\mathrm{.}56=5,871\mathrm{.}48.$

b$.$ At $12/31/24,$ the difference between upper and lower confidence bound is $21,317\mathrm{.}63$

$9,822\mathrm{.}83=11,494\mathrm{.}80.$

$8.$ Do the same analysis for Income before Extraordinary Items with the data in Sheet$1$ in column E$,$

repeating steps $1-6$ with the new data. We choose income before any possible extraordinary items

because predicting extraordinary items are very difficult to do$.$

$9.$ The graph for the forecast of future income from extraordinary items is as follows:

$0$

$5000$

$10000$

$15000$

$20000$

$25000$

$30000$

$35000$

$1357911131517192123252729313335373941434547495153555759$

IBM Sales Forecast$($IBM Sales$)$

Lower Confidence Bound$($IBM Sales$)$ Upper Confidence Bound$($IBM Sales$)$

$-4000$

$-2000$

$0$

$2000$

$4000$

$6000$

$8000$

$1357911131517192123252729313335373941434547495153555759$

IBM Income Before Extraordinary Items

Forecast$($IBM Income Before Extraordinary Items$)$

Lower Confidence Bound$($IBM Income Before Extraordinary Items$)$

Upper Confidence Bound$($IBM I