Supplementary Problem 15.33 Supplementary Problem 15.33 The U.S. Department of C

Question

Supplementary Problem 15.33

Supplementary Problem 15.33

The U.S. Department of Commerce publishes a series of census documents referred to as Current Industrial Reports. Included in these documents are the manufacturers’ shipments, inventories, and orders over a 5-year period. Displayed here is a portion of these data representing the shipments of chemicals and allied products from January of year 1 through December of year 5.
Time Period Chemicals
and Allied
Products
($ billion) Time Period Chemicals
and Allied
Products
($ billion) January (year 1) 23.701 January (year 2) 23.347 February 24.189 February 24.122 March 24.200 March 25.282 April 24.971 April 25.426 May 24.560 May 25.185 June 24.992 June 26.486 July 22.566 July 24.088 August 24.037 August 24.672 September 25.047 September 26.072 October 24.115 October 24.328 November 23.034 November 23.826 December 22.590 December 24.373 January (year 3) 24.207 January (year 4) 25.316 February 25.772 February 26.435 March 27.591 March 29.346 April 26.958 April 28.983 May 25.920 May 28.424 June 28.460 June 30.149 July 24.821 July 26.746 August 25.560 August 28.966 September 27.218 September 30.783 October 25.650 October 28.594 November 25.589 November 28.762 December 25.370 December 29.018 Time Period Chemicals
and Allied
Products
($ billion) January (year 5) 28.931 February 30.456 March 32.372 April 30.905 May 30.743 June 32.794 July 29.342 August 30.765 September 31.637 October 30.206 November 30.842 December 31.090
Use the seasonal indexes computed below to deseasonalize the data.
Month Index Jan 95.35 Feb 99.69 Mar 106.75 Apr 103.99 May 100.99 June 106.96 July 94.53 Aug 99.60 Sept 104.16 Oct 97.04 Nov 95.75 Dec 95.19

What is the deseasonalized data value for June (year 3)?

(Round your answer to 2 decimal places.)

Explanation / Answer

I can solve this problem by using MS Excel,

Copy the data in the excel first,

You can use a pivot table to calculate the seasonal indices. To do that, follow these steps:

1. Move the date field to the Row Labels. Add historical sales to the Values area. You will have a pivot table identical to the original data set.

4. Select one of the date cells in the pivot table. Select Group Field from the Options tab of the ribbon.

It looks like

1537.84

5. In the Grouping dialog, choose only Months. I always say that you should never choose Months without also choosing Years; however, calculating seasonal indices is the one time that you can choose only Month,

In the next column, use the formula for finding deseasonalized data, =B2/index(B column,month(forecast(corresponding first row) and drag it you will get deseasonalized data.

Hope this will help you.

Row Labels Sum of Chemicals January (year 2) 23.347 January (year 3) 24.207 January (year 4) 25.316 January (year 5) 28.931 (blank) April(YEAR 1) 24.971 May(YEAR 1) 24.56 June(YEAR 1) 24.992 July(YEAR 1) 22.566 August(YEAR 1) 24.037 September(YEAR 1) 25.047 October(YEAR 1) 24.115 November(YEAR 1) 23.034 December(YEAR 1) 22.59 February (year 3) 25.772 March (year 3) 27.591 April (year 3) 26.958 May (year 3) 25.92 June(year 3) 28.46 July(year 3) 24.821 August(year 3) 25.56 September(year 3) 27.218 October(year 3) 25.65 November(year 3) 25.589 December(year 3) 25.37 February(year 2) 24.122 March(year 2) 25.282 April(year 2) 25.426 May(year 2) 25.185 June(year 2) 26.486 July(year 2) 24.088 August(year 2) 24.672 September(year 2) 26.072 October(year 2) 24.328 November(year 2) 23.826 December(year 2) 24.373 February(year 4) 26.435 March(year 4) 29.346 April(year 4) 28.983 May(year 4) 28.424 June(year 4) 30.149 July(year 4) 26.746 August(year 4) 28.966 September(year 4) 30.783 October(year 4) 28.594 November(year 4) 28.762 December(year 4) 29.018 February (year 5) 30.456 March (year 5) 32.372 April (year 5) 30.905 May (year 5) 30.743 June (year 5) 32.794 July (year 5) 29.342 August (year 5) 30.765 September (year 5) 31.637 October (year 5) 30.206 November (year 5) 30.842 December (year 5) 31.09 Grand Total

1537.84

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.