Thinking about a Research Design

profilegysgtclarke
BooksRUsRawData.xls
Home>Business & Finance homework help>Thinking about a Research Design

Packets Sent

These data show the number of information packets that have been sent out by Books R Us on a monthly basis. To use them to forecast weekly sales, you'll
need to massage the data, as these are in monthly units, and sales are in weekly units.
Use the Trend Line process that is in the text for week 5 - see an example in the week 5 power point.
Here, I've used packets sent for the previous month.
Packets
Packets Telemarketers Per Tel Sales Months Revenue/Packet Note: This is a "new variable" we've created by manipulating two other variables.
 01/04  392 4 1 98 6660.28 1
02/04   251 4 2 62.75 9445.81 2
03/04   187 4 3 46.75 67015.34 3
04/04  253 4 4 63.25 54250.47 4
05/04 261 3.5 5 74.5714285714 129030.92 5
6/4 290 5 6 58 52879.41 6
07/04  134 2.5 7 53.6 67163.91 7
8/4 204 3 8 68 81665.54 8
Correlaton Coef
Slope
Standard Error of Est
Y-Intercept

Regression Analysis BRUS Starte

In the example below, total sales are forecasted using service sales.
Instead, use the packet info to develop a set of forecasts of sales data.
For more information about this ficticious company and it's industry,
Visit www.inkwaterpress.com, www.xlibris.com or www.firstbooks.com. Get a feel for this industry, then examine the sales data for “Books R Us”. Ratio of Raw Data To Weighted Average
Note that this gets you started with the 3 week moving average data Note that this gets you started with the total sales data
3 - Week Mov Forecasted Forecasted
Ave., Service Y Total Y
Date Sales Total Date Services Total
_ _
X Y Y X Y Y
Feb 2 - 6, 04 678.45 1411.65 3155.9547357478 Note the way this cell is set up Jan 19 - 23, 04 $1,868.45 3259.77 4222.3471713299 Note the way this cell is set up
Feb 9 - 13, 04 274.6666666667 1398.97 Jan 26 - 30, 04 1,227.35 3400.51
Feb 16 - 20, 04 32 302.9 Feb 2 - 6, 04 760.00 1411.65
Feb 23 - 27, 04 716.85 6332.29 Feb 9 - 13, 04 48.00 1398.97
Mar 1 - 5, 04 986.75 1930.27 Feb 16 - 20, 04 16.00 302.9
Mar 8 - 12, 04 6717.1333333333 9328.66 Feb 23 - 27, 04 5255.5 6332.29
Mar 15 - 19, 04 12746.7433333333 20876.3 Mar 1 - 5, 04 1,417.70 1930.27
Mar 22 - 26, 04 17146.5933333333 20144.12 Mar 8 - 12, 04 555.80 9328.66
Mar 29 - Apr 1, 04 13405.86 14735.99 Mar 15 - 19, 04 18,177.90 20876.3
Apr 5 - 9, 04 10663.5 7811.45 Mar 22 - 26, 04 19,506.53 20144.12
Apr 12 - 16, 04 14622.0966666667 10897.21 Mar 29 - Apr 1, 04 13,755.35 14735.99
Apr 19 - 23, 04 16155.3066666667 25352.02 Apr 5 - 9, 04 6,955.70 7811.45
Apr 26 - 30, 04 17508.9166666667 10189.79 Apr 12 - 16, 04 11,279.45 10897.21
May 3 - 7, 04 24196.02 16057.54 Apr 19 - 23, 04 25,631.14 25352.02
5.10 - 5.14 26675.8866666667 45746.03 Apr 26 - 30, 04 11,555.33 10189.79
5.17 - 5.21 33942.5433333333 20103.36 May 3 - 7, 04 15,340.28 16057.54
5.24-5.28 21382.8933333333 38372.29 5.10 - 5.14 45,692.45 45746.03
5.31-6.04 19522.72 8751.7 5.17 - 5.21 18,994.93 20103.36
6.7-6.11 9852.4033333333 14844.62 5.24-5.28 37,140.25 38372.29
6.14-6.18 16106.7233333333 9190.94 5.31-6.04 8,013.50 8751.7
6.28-7.2 14371.6566666667 28843.85 6.7-6.11 13,414.41 14844.62
7.5-7.9 14303.64 9215.86 6.14-6.18 8,129.30 9190.94
7.12-7.16 12088.85 11216.78 6.28-7.2 26,776.46 28843.85
7.19-7.23 17306.4966666667 20688.91 7.5-7.9 8,209.21 9215.86
7.26-7.30 $20,356.01 26,042.36 7.12-7.16 7,925.25 11216.78
Aug. 2 - 6 $22,110.33 14336.76 7.19-7.23 20,132.09 20688.91
Aug 9 - 13 $19,543.87 25951.86 7.26-7.30 23,862.15 26,042.36
Aug 13-20 $22,442.93 18342.99 Aug. 2 - 6 10580.5 14336.76
Aug 23-27 $17,316.72 23033.93 Aug 9 - 13 22349.55 25951.86
Aug 30 - Sept 3 $16,194.49 10573.23 Aug 13-20 14328.85 18342.99
Sept 6 - 10 $14,721.88 14976.3 Aug 23-27 19876.6 23033.93
Sept 13 - 17 $18,603.20 18616.1 Aug 30 - Sept 3 8482.5 10573.23
Sept 20 - 24 $23,112.08 22217.21 Sept 6 - 10 12305.45 14976.3
Sept 27 - Oct 01 $30,072.60 28502.94 Sept 13 - 17 16959.5 18616.1
Oct 4 - Oct 8 $27,318.88 39497.64 Sept 20 - 24 19378.5 22217.21
Oct 11 - 15 $24,066.32 13956.06 Sept 27 - Oct 01 26340.25 28502.94
Oct 18 - 22 $19,188.00 18745.25 Oct 4 - Oct 8 37582.55 39497.64
Oct 25 - 29 $19,057.43 24862.7 Oct 11 - 15 12383.11 13956.06
Nov. 1 - 5 $17,873.33 13564.34 Oct 18 - 22 16537.25 18745.25
Oct 25 - 29 24057.05 24862.7
Correlaton Coef Nov. 1 - 5 12081.65 13564.34
Slope Nov. 8 - 12 14646.1 15192.94
Standard Error of Est
Y-Intercept Correlaton Coef
Slope
Standard Error of Est
Y-Intercept

Regression Analysis BRUS Starte

0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Unadjust
Tota

Books R Us Sales Data

In the example below, total sales are forecasted using service sales. Sales Revenue by week
You can use the packet info to develop a set of forecasts of sales data or used a moving average of total services sales to forecast total sales.
For more information about this ficticious company and it's industry, First Quarter Second Quarter Third Quarter Fourth Quarter
Visit www.inkwaterpress.com, www.xlibris.com or www.firstbooks.com. Get a feel for this industry & examine the sales data Week Total Services Books Week Total Services Books Week Total Services Books Total Services Books
for “Books R Us”. Jan 5 - 9 7,932 6,985 947 Apr 5 - 9 7,811 6,956 556 7.5-7.9 9,216 8,209 1,017 Oct 4 - Oct 8 39,498 37,583 1,915
Jan 12 - 16 16,781 14,934 1,848 Apr 12 - 16 10,897 11,279 -69 7.12-7.16 11,217 7,925 3,292 Oct 11 - 15 13,956 12,383 1,573
Note that we have four sets of data. Bookselling Services Sales Revenue by week Jan 19 - 23 3,260 1,868 1,071 Apr 19 - 23 25,352 25,631 106 7.19-7.23 20,689 20,132 557 Oct 18 - 22 18,745 16,537 2,208
For each sample, how can I measure: Set up book signings Jan 26 - 30 3,401 1,227 2,141 Apr 26 - 30 10,190 11,555 1,310 7.26-7.30 26,042 23,862 2,180 Oct 25 - 29 24,863 24,057 806
Set up Radio / TV interviews Total Book Total Other Feb 2 - 6 1,412 760 580 May 3 - 7 16,058 15,340 717 Aug. 2 - 6 14,337 10,581 3,756 Nov. 1 - 5 13,564 12,082 1,483
The Mean Send e-mails to book stores Date Total Services Books Charges Feb 9 - 13 1,399 48 1,200 5.10 - 5.14 45,746 45,692 456 Aug 9 - 13 25,952 22,350 3,602 Nov. 8 - 12 15,193 14,646 547
Coefficient of Variation Send mass mailings to bookstores Jan 5 - 9 7932.04 6984.90 947.14 0 Feb 16 - 20 303 16 261.2 5.17 - 5.21 20,103 18,995 1,137 Aug 13-20 18,343 14,329 3,929 Nov. 15 - 19 25,946 21,819 2,527
The Standard Deviation Contact Film Makers Jan 12 - 16 16781.18 14933.55 1847.58 0.00 Feb 23 - 27 6,332 5255.5 1050.72 5.24-5.28 38,372 37,140 1,238 Aug 23-27 23,034 19,877 3,157 Nov. 22-26 10,612 9,004 1,608
The degree of skewness Print Books Jan 19 - 23, 04 3259.77 1868.45 1071.48 0.00 Mar 1 - 5 1,930 1,418 513 5.31-6.04 8,752 8,014 738 Aug 30 - Sept 3 10,573 8,483 2,104 Nov. 29-12/3 31,053 28,586 2,386
Line up book reviews Jan 26 - 30, 04 3400.51 1227.35 2141.44 319.84 Mar 8 - 12 9,329 556 8,773 6.7-6.11 14,845 13,414 1,370 Sept 6 - 10 14,976 12,305 2,671 Dec 6 - 10 20,910 20,237 673
How can I use these measures and compare Feb 2 - 6, 04 1411.65 760.00 579.68 31.72 Mar 15 - 19 20,876 18,178 1,198 6.14-6.18 9,191 8,129 1,062 Sept 13 - 17 18,616 16,960 1,657 Dec 13-17 14,231 13,051 1,180
to the overall total average (which you must first calculate) via: Feb 9 - 13, 04 1398.97 48.00 1199.80 71.97 Mar 22 - 26 20,144 19,507 638 6.21-6.25 11,166 8,878 2,288 Sept 20 - 24 22,217 19,379 2,839 Dec 20 - 24 4,575 4,056 519
A ratio analysis Feb 16 - 20, 04 302.90 16.00 261.2 151.17 Mar 29 - Apr 1 14,736 13,755 981 6.28-7.2 28,844 26,776 2,419 Sept 27 - Oct 01 28,503 26,340 2,211 Dec 27 - 31 18,756 17,783 973
The calculation of an index Feb 23 - 27, 04 6332.29 5255.5 1050.72 25.70 AVERAGE AVERAGE AVERAGE AVERAGE
A comparison of short term vs. long term trends Mar 1 - 5, 04 1930.27 1417.70 512.57 26.07 Standard Deviation Standard Deviation Standard Deviation Standard Deviation
Mar 8 - 12, 04 9328.66 555.80 8772.86 0 Coefficient of Var
Sample 1 Mar 15 - 19, 04 20876.30 18177.90 1198.40 0
Sales Revenue by week Mar 22 - 26, 04 20144.12 19506.53 637.59 1500.00 Ave. Weekly Sales
Mar 29 - Apr 1, 04 14735.99 13755.35 980.64 0
Total Other Total Total Books Apr 5 - 9, 04 7811.45 6955.70 555.75 0 Total Services Books Week Total
Date Total Charges Services Sales Apr 12 - 16, 04 10897.21 11279.45 -68.96 300.00 Quarter 1 0.00 0.00 0.00 Jan 5 - 9 7,932
Jan 19 - 23, 04 3259.77 319.84 1868.45 1071.48 Apr 19 - 23, 04 25352.02 25631.14 106.47 -313.28 Quarter 2 0.00 0.00 0.00 Jan 12 - 16 16,781
Jan 26 - 30, 04 3400.51 31.72 1227.35 2141.44 Apr 26 - 30, 04 10189.79 11555.33 1310.37 -385.59 Quarter 3 0.00 0.00 0.00 Jan 19 - 23 3,260
Feb 2 - 6, 04 1411.65 71.97 760.00 579.68 May 3 - 7, 04 16057.54 15340.28 717.26 -2675.91 Quarter 4 0.00 0.00 0.00 Jan 26 - 30 3,401
Feb 9 - 13, 04 1398.97 151.17 48.00 1199.80 5.10 - 5.14 45746.03 45692.45 456.39 0 Feb 2 - 6 1,412
Feb 16 - 20, 04 302.90 25.70 16.00 261.2 5.17 - 5.21 20103.36 18994.93 1137.38 -402.81 Feb 9 - 13 1,399
Feb 23 - 27, 04 6332.29 26.07 5255.5 1050.72 5.24-5.28 38372.29 37140.25 1237.54 -28.95 Services Feb 16 - 20 303 Total Reviewers 1646 8.13%
Mar 1 - 5, 04 1930.27 0 1417.70 512.57 5.31-6.04 8751.70 8013.50 738.20 -5.50 Quarter 1 6,604.29 Feb 23 - 27 6,332 Total Non-reviewes 6,828.00 33.74%
Mar 8 - 12, 04 9328.66 555.80 8772.86 6.7-6.11 14844.62 13414.41 1370.44 0.00 Total Reviewers 430.60 Mar 1 - 5 1,930 Total ca Publishing Services 11645.5 57.54%
Mar 15 - 19, 04 20876.30 1500.00 18177.90 1198.40 6.14-6.18 9190.94 8129.30 1061.64 59.77 Total Publishing 5,216.73 Mar 8 - 12 9,329 Frieght 117.75 0.58%
Mar 22 - 26, 04 20144.12 19506.53 637.59 6.21-6.25 11165.78 8877.90 2287.88 0.00 Total Other Serivces 950.36 Mar 15 - 19 20,876 Total Service 20237.25 1
Mar 29 - Apr 1, 04 14735.99 13755.35 980.64 6.28-7.2 29547.15 26776.46 2419.04 0.00 Misc 6.60 Mar 22 - 26 20,144 TOTAL 20910.41 1
Apr 5 - 9, 04 7811.45 300.00 6955.70 555.75 7.5-7.9 9215.86 8209.21 1016.94 351.65 Quarter 2 18292.39 Mar 29 - Apr 1 14,736
Apr 12 - 16, 04 10897.21 -313.28 11279.45 -68.96 7.12-7.16 11216.78 7925.25 3291.53 -10.29 Total Reviewers 1487.16 Apr 5 - 9 7,811
Apr 19 - 23, 04 25352.02 -385.59 25631.14 106.47 7.19-7.23 20688.91 20132.09 556.82 0.00 Total Publishing 10525.39 Apr 12 - 16 10,897
Apr 26 - 30, 04 10189.79 -2675.91 11555.33 1310.37 7.26-7.30 26042.36 23862.15 2180.21 0.00 Total Other Serivces 6171.82 Apr 19 - 23 25,352 Total Reviewers 2543 19.49%
May 3 - 7, 04 16057.54 15340.28 717.26 Aug. 2 - 6 14336.76 10580.50 3756.26 0.00 Misc 106.10 Apr 26 - 30 10,190 Total Non-reviewes 3209 24.59%
9589.34 Aug 9 - 13 25951.86 22349.55 3602.31 0.00 Quarter 3 16210.03 May 3 - 7 16,058 Total ca Publishing Services 7178 55.0%
Sample 2 7868.5703896646 Aug 13-20 18342.99 14328.85 3928.80 0.00 Total Reviewers 3159.33 5.10 - 5.14 45,746 Frieght 120.75 0.93%
Aug 23-27 23033.93 19876.60 3157.33 85.34 Total Publishing 8915.52 5.17 - 5.21 20,103 Total Service 13050.75 1
Sales Revenue by week Aug 30 - Sept 3 10573.23 8482.50 2104.00 0.00 Total Other Serivces 3986.05 5.24-5.28 38,372 TOTAL 14230.96 1
Sept 6 - 10 14976.30 12305.45 2670.85 -13.27 Misc 150.75 5.31-6.04 8,752
Total Other Total Total Books Sept 13 - 17 18616.10 16959.50 1656.60 0.00 Quarter 4 17832.61 6.7-6.11 14,845 Total Reviewers 399.00 2.25%
Date Total Charges Services Sales Sept 20 - 24 22217.21 19378.50 2838.71 0.00 Total Reviewers 401.23 6.14-6.18 9,191 Total Non-reviewes 6,923.00 39.05%
5.10 - 5.14 45746.03 -402.81 45692.45 456.39 Sept 27 - Oct 01 28502.94 26340.25 2210.54 0.00 Total Publishing 10342.91 6.21-6.25 11,166 Total ca Publishing Services 10,407.00 58.7%
5.17 - 5.21 20103.36 -28.95 18994.93 1137.38 Oct 4 - Oct 8 39497.64 37582.55 1915.09 -47.88 Total Other Serivces 6954.72 6.28-7.2 28,844 Frieght 17,729.00
5.24-5.28 38372.29 -5.50 37140.25 1237.54 Oct 11 - 15 13956.06 12383.11 1572.95 0.00 Misc 133.74 7.5-7.9 9,216 Total Service 17783
5.31-6.04 8751.70 0.00 8013.50 738.20 Oct 18 - 22 18745.25 16537.25 2208.00 0.00 7.12-7.16 11,217
6.7-6.11 14844.62 59.77 13414.41 1370.44 Oct 25 - 29 24862.70 24057.05 805.65 0.00 7.19-7.23 20,689
6.14-6.18 9190.94 0.00 8129.30 1061.64 Nov. 1 - 5 13564.34 12081.65 1482.69 0.00 7.26-7.30 26,042
6.21-6.25 11165.78 8877.90 2287.88 0.00 Nov. 8 - 12 15192.94 14646.10 546.84 0.00 Aug. 2 - 6 14,337
6.28-7.2 29547.15 351.65 26776.46 2419.04 Nov. 15 - 19 25946.12 21819.45 2526.67 0.00 Aug 9 - 13 25,952
7.5-7.9 9215.86 -10.29 8209.21 1016.94 Nov. 22-26 10611.55 9003.78 1607.77 1600.00 Aug 13-20 18,343
7.12-7.16 11216.78 0.00 7925.25 3291.53 Nov. 29-12/3 31053.16 28586.00 2386.16 0.00 Aug 23-27 23,034
7.19-7.23 20688.91 0.00 20132.09 556.82 Dec 6 - 10 20910.41 20237.25 673.16 81.00 Aug 30 - Sept 3 10,573
7.26-7.30 26042.36 0.00 23862.15 2180.21 Dec 13-17 14230.96 13050.75 1180.21 0.00 Sept 6 - 10 14,976
20407.15 Dec 20 - 24 4574.82 4056.00 518.82 0.00 Sept 13 - 17 18,616
Sample 3 12313.9882662146 Dec 27 - 31 18756.04 17783.00 973.04 0.00 Sept 20 - 24 22,217
Sales Revenue by week Sept 27 - Oct 01 28,503
Oct 4 - Oct 8 39,498
Total Other Total Total Books Oct 11 - 15 13,956
Date Total Charges Services Sales Oct 18 - 22 18,745
Aug. 2 - 6 14336.76 0.00 10580.50 3756.26 Oct 25 - 29 24,863
Aug 9 - 13 25951.86 0.00 22349.55 3602.31 Nov. 1 - 5 13,564
Aug 13-20 18342.99 85.34 14328.85 3928.80 Nov. 8 - 12 15,193
Aug 23-27 23033.93 0.00 19876.60 3157.33 Nov. 15 - 19 25,946
Aug 30 - Sept 3 10573.23 -13.27 8482.50 2104.00 Nov. 22-26 10,612
Sept 6 - 10 14976.30 0.00 12305.45 2670.85 Nov. 29-12/3 31,053
Sept 13 - 17 18616.10 0.00 16959.50 1656.60 Dec 6 - 10 20,910
Sept 20 - 24 22217.21 0.00 19378.50 2838.71 Dec 13-17 14,231
Sept 27 - Oct 01 28502.94 -47.88 26340.25 2210.54 Dec 20 - 24 4,575
Oct 4 - Oct 8 39497.64 0.00 37582.55 1915.09 Dec 27 - 31 18,756 Pop standard dev
Oct 11 - 15 13956.06 0.00 12383.11 1572.95 16,361 9889.4654693024
Oct 18 - 22 18745.25 0.00 16537.25 2208.00
Oct 25 - 29 24862.70 0.00 24057.05 805.65
Nov. 1 - 5 13564.34 0.00 12081.65 1482.69
Nov. 8 - 12 15192.94 0.00 14646.10 546.84
20158.02
Sample 4 7458.1619342433
Total Other Total Total Books
Date Total Charges Services Sales
Nov. 15 - 19 25946.12 1600 21819.45 2526.67
Nov. 22-26 10611.55 0 9003.78 1607.77
Nov. 29-12/3 31053.16 81 28586 2386.16
Dec 6 - 10 20910.41 0 20237.25 673.16
Dec 13-17 14230.96 0 13050.75 1180.21
Dec 20 - 24 4574.82 0 4056 518.82
Dec 27 - 31 18756.04 0 17783 973.04
Mean 18011.87 16362.3185714286 1409.4042857143
Stand Dev 9051.68 8294.4977091793 797.8954805164

Books R Us Sales Data
Total 7932.04 16781.18 3259.77 3400.51 1411.65 1398.97 302.90 6332.29
Weeks
Thousands $$
Books R Us Total Sales Trends

Sales distribution
Total
Services
Books
Average Weekly Sales BRUS

Packets & Telemarketers

A Typical Month
Qty Amount % of Sales Avg Price Gross Margin Gross Margin %
Inventory
1113 5.00 29.85 0.07% 5.97 29.85 100.0%
1596 -19.00 -193.23 -0.7% 10.17 -193.23 100.0%
1842 5.00 29.85 0.08% 5.97 29.85 100.0%
1868 30.00 269.10 0.98% 8.97 269.10 100.0%
1868A 10.00 47.94 105.95% 4.79 47.94 100.0%
1869 1.00 14.95 0.04% 14.95 14.95 100.0%
1907 2.00 25.90 0.39% 12.95 25.90 100.0%
1976 -23.00 -192.97 -6.46% 8.39 -192.97 100.0%
2024 2.00 142.00 0.31% 71.00 142.00 100.0%
2066 1.00 25.00 0.06% 25.00 25.00 100.0%
2293 20.00 258.00 0.56% 12.90 258.00 100.0%
Total Inventory 456.39 101.27%
Service Service Service Service Service
Bookselling Services Bookselling Services Bookselling Services Bookselling Services Bookselling Services
B/SpkgIntro 4.00 747.00 1.62% 186.75 B/SpkgIntro 2 698 0.03338 349 B/SpkgIntro 1.00 349.00 2.67% 349.00 B/SpkgIntro 1.00 299.00 6.54% 299.00 B/SpkgIntro 7.00 1,744.00 9.3% 249.14
B/SpkgMax 3.00 4,047.00 8.75% 1,349.00 Guar PLace 10 2 1390 0.06647 695 Guar PLace 10 2.00 1,390.00 10.65% 695.00 B/SpkgPrem 2.00 1,499.00 32.77% 749.50 B/SpkgMax 3.00 3,957.00 21.1% 1,319.00
Reviewers Newspaper Ad 9 4165 0.19918 462.78 Newspaper Ad 3.00 1,470.00 11.26% 490.00 Reviewers Guar PLace 10 2.00 1,395.00 7.44% 697.50
Book Clubs 8.00 1,692.50 3.66% 211.56 Reviewers Reviewers Bowker/Star Review 1.00 399.00 8.72% 399.00 Raido Show
Ediitors 2.00 398.00 0.86% 199.00 Bowker/Star Review 3 1148 0.0549 382.67 Bowker/Star Review 3.00 1,197.00 9.17% 399.00 Filmmakers 1.00 0.00 0.0% 0.00 Radio Time -1.00 -399.00 -2.13% 399.00
Filmmakers 4.00 747.00 1.62% 186.75 Signed by the Author 3 498 0.02382 166 E-mails 1.00 349.00 2.67% 349.00 Total Reviewers 399.00 8.72% Total Raido Show -399.00 -2.13%
Media Book Critics 1.00 150.00 0.32% 150.00 Total Reviewers 1646 0.07872 Ediitors 1.00 299.00 2.29% 299.00 Total Non-reviewes 1,798.00 39.3% Reviewers
Total Reviewers 2,987.50 6.46% Telemarketing 1 575 0.0275 575 Filmmakers 1.00 299.00 2.29% 299.00 Total Bookselling Services 2,197.00 48.02% Bowker/Star Review 1.00 399.00 2.13% 399.00
Telemarketing 4.00 1,050.00 2.27% 262.50 Total Non-reviewes 6,828.00 32.65% Producers 1.00 399.00 3.06% 399.00 ca Publishing Services Total Reviewers 399.00 2.13%
World Wide Wire 4.00 747.00 1.62% 186.75 Total Bookselling Services 8474 0.40525 Total Reviewers 2,543.00 17.87% Publish Book 2.00 1,798.00 39.3% 899.00 Telemarketing 1.00 226.00 1.21% 226.00
Total Non-reviewes 6,591.00 14.25% ca Publishing Services Total Non-reviewes 3,209.00 22.55% Total ca Publishing Services 1,798.00 39.3% World Wide Wire 1.00 0.00 0.0% 0.00
Total Bookselling Services 9,578.50 20.71% Data Entry/Scanning 1 242 0.01157 242 Total Bookselling Services 5,752.00 40.42% Frieght 9.00 61.00 1.33% 6.78 Total Non-reviewes 6,923.00 36.91%
ca Publishing Services Print Add'l Books 950 3811.5 0.18228 4.01 ca Publishing Services TOTAL Total Service 4,056.00 88.66% Total Bookselling Services 7,322.00 39.04%
Data Entry/Scanning 1.00 246.60 0.53% 246.60 Production 4 1300 0.06217 325 Print Add'l Books 100.00 882.00 6.76% 8.82 4,574.82 100.0% ca Publishing Services
Print Add'l Books 10,250.00 27,474.80 59.39% 2.68 Publish Book 7 6292 0.3009 898.86 Publish Book 7.00 6,296.00 48.24% 899.43 Data Entry/Scanning 1.00 11.00 0.06% 11.00
Production 250.00 60.00 0.13% 0.24 Total ca Publishing Services 11645.5 0.55692 Total ca Publishing Services 7,178.00 55.0% Print Add'l Books 200.00 800.00 4.27% 4.00
Publish Book 11.00 8,400.00 18.16% 763.64 Frieght 22 117.75 0.00563 5.35 Frieght 17.00 120.75 0.93% 7.10 Publish Book 11.00 9,596.00 51.16% 872.36
Total ca Publishing Services 36,181.40 78.21% TOTAL Total Service 20237.25 0.96781 TOTAL Total Service 13,050.75 100.0% Total ca Publishing Services 10,407.00 55.49%
Frieght 5.00 45.25 0.1% 9.05 20910.41 1 13,050.75 100.0% Frieght 10.00 54.00 0.29% 5.40
Total Service 45,805.15 99.01% TOTAL 14,230.96 100.0% Total Service 17,783.00 94.81%
TOTAL 46,261.54 100.0% TOTAL 18,756.04 100.0%
Qty Amount % of Services
Total Reviewers 2,987.50 6.52% Total Reviewers 2,987.50 6.52%
Total Non-reviewes 6,591.00 14.39% Total Non-reviewes 6,591.00 14.39%
Total ca Publishing Services 36,181.40 78.99% Total ca Publishing Services 36,181.40 78.99%
Frieght 45.25 0.1% Frieght 45.25 0.1%
Total Service 45,805.15 99.01% Total Service 45,805.15 99.01%
TOTAL 46,261.54 100.0% TOTAL 46,261.54 100.0%
Total Reviewers 1646 8.13% Total Reviewers 1646 8.13%
Total Non-reviewes 6,828.00 33.74% Total Non-reviewes 6,828.00 33.74%
Total ca Publishing Services 11645.5 57.54% Total ca Publishing Services 11645.5 57.54%
Frieght 117.75 0.58% Frieght 117.75 0.58%
Total Service 20237.25 1 Total Service 20237.25 1
TOTAL 20910.41 1 TOTAL 20910.41 1
Total Reviewers 2543 19.49% Total Reviewers 2543 19.49%
Total Non-reviewes 3209 24.59% Total Non-reviewes 3209 24.59%
Total ca Publishing Services 7178 55.0% Total ca Publishing Services 7178 55.0%
Frieght 120.75 0.93% Frieght 120.75 0.93%
Total Service 13050.75 1 Total Service 13050.75 1
TOTAL 14230.96 1 TOTAL 14230.96 1
Total Reviewers 399.00 9.84% Total Reviewers 399.00 9.84%
Total Non-reviewes 1,798.00 44.33% Total Non-reviewes 1,798.00 44.33%
Total ca Publishing Services 1,798.00 44.33% Total ca Publishing Services 1,798.00 44.33%
Frieght 61.00 1.5% Frieght 61.00 1.5%
Total Service 4,056.00 1 Total Service 4,056.00 1
Total Reviewers 399.00 2.13% Total Reviewers 399.00 2.13%
Total Non-reviewes 6,923.00 36.91% Total Non-reviewes 6,923.00 36.91%
Total ca Publishing Services 10,407.00 55.49% Total ca Publishing Services 10,407.00 55.49%
Frieght Frieght
Total Service 17783 Total Service 17783
&L&"Arial,Bold"&8 8:41 AM &"Arial,Bold"&8 08/03/04 &"Arial,Bold"&8 Accrual Basis&C&"Arial,Bold"&12 Bookman Marketing LLC &"Arial,Bold"&14 Sales by Item Summary &"Arial,Bold"&10 May 10 - 14, 2004
&R&"Arial,Bold"&8 Page &P of &N

Moving Ave. Example

These data show the number of information packets that have been sent out by Books R Us on a monthly basis. To use them to forecast weekly sales, you'll
need to massage the data, as these are in monthly units, and sales are in weekly units.
Month Packets Sent Week # Telemarketers
(Month/Year) Jan 19 - 23, 04 4 Week # Telemarketers
8/02                  46 Jan 26 - 30, 04 4 7.26-7.30 3
9/02           107 Feb 2 - 6, 04 4 Aug. 2 - 6 3
10/02       419 Feb 9 - 13, 04 4 Aug 9 - 13 3
11/02         329 Feb 16 - 20, 04 4 Aug 13-20 3
12/02       279 Feb 23 - 27, 04 4 Aug 23-27 3
01/03        235 Mar 1 - 5, 04 4 Aug 30 - Sept 3 4
02/03       465 Mar 8 - 12, 04 4 Sept 6 - 10 4
03/03     319 Mar 15 - 19, 04 4 Sept 13 - 17 3
04/03     254 Mar 22 - 26, 04 4 Sept 20 - 24 3
05/03     265 Mar 29 - Apr 1, 04 4 Sept 27 - Oct 01 3
06/03   271 Apr 5 - 9, 04 4 Oct 4 - Oct 8 3
07/03   438 Apr 12 - 16, 04 4 Oct 11 - 15 3
08/03   278 Apr 19 - 23, 04 4 Oct 18 - 22 3
09/03   436 Apr 26 - 30, 04 3 Oct 25 - 29 3
10/03   350 May 3 - 7, 04 2 Nov. 1 - 5 3
11/03   274 5.10 - 5.14 2 Nov. 8 - 12 3
12/03   339 5.17 - 5.21 2
 01/04  392 5.24-5.28 5
02/04   251 5.31-6.04 5
03/04   187 6.7-6.11 5
04/04  253 6.14-6.18 5
05/04 261 6.28-7.2 3
6/4 290 7.5-7.9 3
07/04  134 7.12-7.16 2
8/4 204 7.19-7.23 3
Notice that:
The packet info is on a monthly basis, but the sales data is weekly. How do I manipulate the data to compare the two?
The packet data goes from 2002 through 2004, but sales data is for Jan. - Nov., 2004. What do I do?
How can I take the two variables I have (packets sent per month and weekly sales) and develop a BRAND NEW variable which
allows me to analyzed both at the same time?

Packets & Telemarketers (2)

Sales Revenue by week Survey 1
3 - Week Mov 3 - Week Mov
Total Other Total Total Books Ave., Total Ave., Service
Date Total Charges Services Sales Sales Sales
Jan 19 - 23, 04 3259.77 $319.84 $1,868.45 $1,071.48
Jan 26 - 30, 04 3400.51 31.72 1,227.35 2,141.44 $2,690.64 $1,285.27
Feb 2 - 6, 04 1411.65 71.97 760.00 579.68 $2,070.38 $678.45
Feb 9 - 13, 04 1398.97 151.17 48.00 1,199.80 $1,037.84 $274.67
Feb 16 - 20, 04 302.9 25.70 16.00 261.2 $2,678.05 $32.00
Feb 23 - 27, 04 6332.29 26.07 5255.5 1050.72 $2,855.15 $716.85
Mar 1 - 5, 04 1930.27 0 1,417.70 512.57 $5,863.74 $986.75
Mar 8 - 12, 04 9328.66 555.80 8,772.86 $10,711.74 $6,717.13
Mar 15 - 19, 04 20876.3 1,500.00 18,177.90 1,198.40 $16,783.03 $12,746.74
Mar 22 - 26, 04 20144.12 19,506.53 637.59 $18,585.47 $17,146.59
Mar 29 - Apr 1, 04 14735.99 13,755.35 980.64 $14,230.52 $13,405.86
Apr 5 - 9, 04 7811.45 300.00 6,955.70 555.75 $11,148.22 $10,663.50
Apr 12 - 16, 04 10897.21 -313.28 11,279.45 -68.96 $14,686.89 $14,622.10
Apr 19 - 23, 04 25352.02 -385.59 25,631.14 106.47 $15,479.67 $16,155.31
Apr 26 - 30, 04 10189.79 -2,675.91 11,555.33 1,310.37 $17,199.78 $17,508.92
May 3 - 7, 04 16057.54 15,340.28 717.26
Notice that to create the moving average, we took the weeks of Jan. 19-23 + Jan. 26-30 + Feb. 2-6. Then, we divided by 3.
The value for total sales was 2,690.64, and for services sales it was 1,285.27.
When we do the next moving average, we drop the week of Jan. 19-23 and pick up the week of Feb. 9-13. Thus, the average "moves)
Can you see why we don't have a value in either CELL G8 or CELL G23? Go and create a moving average for the rest of the weeks.
Here are some questions to ask:
Why would I deveop a moving average.
A moving average of 3 weeks was developed for this example. But if I really wanted to use a moving average, how might I
go about decding the best model to use? What additional factors would I need to consider?
How can I apply this analysis technique to study businesses that have a product or service which is seasonal in nature?
How can I apply this analysis technique to study businesses that have varying sales cycles?
These data show the number of information packets that have been sent out by Books R Us on a monthly basis. To use them to forecast weekly sales, you'll
need to massage the data, as these are in monthly units, and sales are in weekly units.
Month Packets Sent Week # Telemarketers
(Month/Year) Jan 19 - 23, 04 4 Week # Telemarketers
8/02                  46 Jan 26 - 30, 04 4 7.26-7.30 3 Notice that:
9/02           107 Feb 2 - 6, 04 4 Aug. 2 - 6 3
10/02       419 Feb 9 - 13, 04 4 Aug 9 - 13 3 The packet info is on a monthly basis, but the sales data is weekly. How do I manipulate the data to compare the two?
11/02         329 Feb 16 - 20, 04 4 Aug 13-20 3 The packet data goes from 2002 through 2004, but sales data is for Jan. - Nov., 2004. What do I do?
12/02       279 Feb 23 - 27, 04 4 Aug 23-27 3 How can I take the two variables I have (packets sent per month and weekly sales) and develop a BRAND NEW variable which
01/03        235 Mar 1 - 5, 04 4 Aug 30 - Sept 3 4 allows me to analyzed both at the same time?
02/03       465 Mar 8 - 12, 04 4 Sept 6 - 10 4
03/03     319 Mar 15 - 19, 04 4 Sept 13 - 17 3
04/03     254 Mar 22 - 26, 04 4 Sept 20 - 24 3
05/03     265 Mar 29 - Apr 1, 04 4 Sept 27 - Oct 01 3
06/03   271 Apr 5 - 9, 04 4 Oct 4 - Oct 8 3
07/03   438 Apr 12 - 16, 04 4 Oct 11 - 15 3
08/03   278 Apr 19 - 23, 04 4 Oct 18 - 22 3
09/03   436 Apr 26 - 30, 04 3 Oct 25 - 29 3
10/03   350 May 3 - 7, 04 2 Nov. 1 - 5 3
11/03   274 5.10 - 5.14 2 Nov. 8 - 12 3
12/03   339 5.17 - 5.21 2
 01/04  392 5.24-5.28 5
02/04   251 5.31-6.04 5
03/04   187 6.7-6.11 5
04/04  253 6.14-6.18 5
05/04 261 6.28-7.2 3
6/4 290 7.5-7.9 3
07/04  134 7.12-7.16 2
8/4 204 7.19-7.23 3