Date of Sales IBM Sales3/31/2010
6/30/2010
9/30/2010
12/31/2010
3/31/2011
6/30/2011
9/30/2011
12/31/2011
3/31/2012
6/30/2012
9/30/2012
12/31/2012
3/31/2013
6/30/2013
9/30/2013
12/31/2013
3/31/2014
6/30/2014
9/30/2014
12/31/2014
3/31/2015
6/30/2015
9/30/2015
12/31/2015
3/31/2016
6/30/2016
9/30/2016
12/31/2016
3/31/2017
6/30/2017
9/30/2017
12/31/2017
3/31/2018
6/30/2018
9/30/2018
12/31/2018
3/31/2019
6/30/2019
9/30/2019
12/31/2019
3/31/2020
6/30/2020
9/30/2020
22857
23724
24271
29019
24607
26666
26157
29486
24673
25783
24747
29304
23408
24236
23338
27386
22236
24047
22397
24113
19590
20813
19279
22059
18685
20238
19226
21771
18155
19288
19154
22542
19072
20003
18755
21761
18182
19161
18027
21777
17571
18123
17559
Date of Income
IBM Income Before Extraordinary Items
3/31/2010
2601
6/30/2010
3386
9/30/2010
3589
12/31/2010
5257
3/31/2011
2863
6/30/2011
3663
9/30/2011
3839
12/31/2011
5490
3/31/2012
3066
6/30/2012
3882
9/30/2012
3823
12/31/2012
5833
3/31/2013
3032
6/30/2013
3494
9/30/2013
4139
12/31/2013
6216
3/31/2014
2530
6/30/2014
4252
9/30/2014
3455
12/31/2014
5514
3/31/2015
2415
6/30/2015
3527
9/30/2015
2962
12/31/2015
4460
3/31/2016
2016
6/30/2016
2505
9/30/2016
2854
12/31/2016
4506
3/31/2017
1753
6/30/2017
2332
9/30/2017
2725
12/31/2017
-1052
3/31/2018
1675
6/30/2018
2403
9/30/2018
2692
12/31/2018
1953
3/31/2019
1593
6/30/2019
2500
9/30/2019
1673
12/31/2019
3669
3/31/2020
1176
6/30/2020
1362
9/30/2020
1698
12/31/2020
3/31/2021
6/30/2021
9/30/2021
12/31/2021
20367
17729
18745
17619
16694
12/31/2020
3/31/2021
6/30/2021
9/30/2021
12/31/2021
1265
956
1325
1130
2461
ACT2292 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 ACT2292 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.
6. The forecast graph should look as follows:
35000
30000
25000
20000
15000
10000
5000
IBM Sales
Forecast(IBM Sales)
Lower Confidence Bound(IBM Sales)
Upper Confidence Bound(IBM Sales)
10/1/2024
3/31/2024
10/1/2023
3/31/2023
10/1/2022
3/31/2022
9/30/2021
3/31/2021
9/30/2020
3/31/2020
9/30/2019
3/31/2019
9/30/2018
3/31/2018
9/30/2017
3/31/2017
9/30/2016
3/31/2016
9/30/2015
3/31/2015
9/30/2014
3/31/2014
9/30/2013
3/31/2013
9/30/2012
3/31/2012
9/30/2011
3/31/2011
9/30/2010
3/31/2010
0
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.04 –
12,569.56 = 5,871.48.
b. At 12/31/24, the difference between upper and lower confidence bound is 21,317.63–
9,822.83 = 11,494.80.
8. Do the same analysis for Income before Extraordinary Items with the data in Sheet1 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:
8000
6000
4000
2000
0
-2000
IBM Income Before Extraordinary Items
Forecast(IBM Income Before Extraordinary Items)
Lower Confidence Bound(IBM Income Before Extraordinary Items)
Upper Confidence Bound(IBM Income Before Extraordinary Items)
10/1/2024
3/31/2024
10/1/2023
3/31/2023
10/1/2022
3/31/2022
9/30/2021
3/31/2021
9/30/2020
3/31/2020
9/30/2019
3/31/2019
9/30/2018
3/31/2018
9/30/2017
3/31/2017
9/30/2016
3/31/2016
9/30/2015
3/31/2015
9/30/2014
3/31/2014
9/30/2013
3/31/2013
9/30/2012
3/31/2012
9/30/2011
3/31/2011
9/30/2010
3/31/2010
-4000
Share the Story:
We have now developed a forecast for future sales and income before extraordinary items.
Assessment:
1. Save your data file with your name and upload to Canvas.
2. Answer the multiple choice questions:
1. What is the IBM forecast sales for the 10/1/2022 quarter?
2. What is the IBM forecast upper confidence bound for sales the 10/1/2023 quarter?
3. By the looks of the graph and breadth of the confidence intervals, which seems harder
to predict, sales or income before extraordinary items?
4. What is the IBM forecast of Income before Extraordinary Items for the 7/1/2022
quarter?
5. What is the IBM forecast lower confidence bound for Income before Extraordinary
Items in the 10/1/2023 quarter?
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Achiever Papers is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Dissertation Writing Service Works
First, you will need to complete an order form. It's not difficult but, if anything is unclear, you may always chat with us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download