Data description: The ShoesSales file contains weekly sales data from March 2009 to December 2010 for...

 

Transcribed Image Text:

Data description: The ShoesSales file contains weekly sales data from March 2009 to December 2010 for the unit sales of one product, unit price, whether an ad campaign was run that week (1 = ad campaign, 0 = no ad campaign), competitor's price, target sales, and sales revenue. Use this file to perform the following exercises: 1). Create pivot table and pivot chart to summarize the effect of Ad campaign on sales revenue (15 points). 2). Create pivot table and pivot chart to analyze the nature of the monthly seasonality of sales revenue (15 points). 3). Forecast weekly sales revenue for each week in January and February 2011 (20 points). Week 3/2/09 3/9/09 3/16/09 3/23/09 3/30/09 4/6/09 4/13/09 4/20/09 4/27/09 5/4/09 5/11/09 5/18/09 5/25/09 6/1/09 6/8/09 6/15/09 6/22/09 6/29/09 7/6/09 7/13/09 7/20/09 7/27/09 8/3/09 8/10/09 8/17/09 8/24/09 8/31/09 9/7/09 9/14/09 9/21/09 9/28/09 10/5/09 10/12/09 10/19/09 10/26/09 11/2/09 11/9/09 11/16/09 11/23/09 11/30/09 12/7/09 12/14/09 12/21/09 12/28/09 1/4/10 1/11/10 1/18/10 Ad Unit Campaign Price 0 0 1 1 1 1 1 0 0 0 1 1 0 1 0 1 0 1 1 0 0 1 1 9.6 9.6 9.6 9 9.8 9.8 9.8 8.6 8 10 9.2 9 9.4 8.8 9.8 9.4 9 8.4 9.8 9.2 9.4 8.6 8.8 8.2 9.4 9.6 10 9.8 8 9.2 9.6 8.6 8 8.6 10 9.4 9.8 8.4 9.8 8.8 9.4 8.2 9.8 8.4 8.8 8.4 9.2 Units Sold 2235 1655 2373 3005 3107 2407 1925 1584 2481 2824 2137 666 1458 1302 1188 1100 1197 2500 2607 2175 1706 2561 2851 2582 2966 2329 1906 1257 2073 2542 2902 2293 1825 1535 1137 1041 941 844 250 330 682 761 794 292 533 1569 2247 Competi tor's $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 Sales Target Sales Revenue $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $21,456 $15,888 $22,781 $27,045 $30,449 $23,589 $18,865 $13,622 $19,848 $28,240 $19,660 $14,994 $13,705 $11,458 $11,642 $10,340 $10,773 $21,000 $25,549 $20,010 $16,036 $22,025 $25,089 $21,172 $27,880 $22,358 $19,060 $12,319 $16,584 $23,386 $27,859 $19,720 $14,600 $13,201 $11,370 $9,785 $9,222 $7,090 $2,450 $2,904 $6,411 $6,240 $7,781 $2,453 $4,690 $13,180 $20,672 2/1/10 2/8/10 2/15/10 2/22/10 3/1/10 3/8/10 3/15/10 3/22/10 3/29/10 4/5/10 4/12/10 4/19/10 4/26/10 5/3/10 5/10/10 5/17/10 5/24/10 5/31/10 6/7/10 6/14/10 6/21/10 6/28/10 7/5/10 7/12/10 7/19/10 7/26/10 8/2/10 8/9/10 8/16/10 8/23/10 8/30/10 9/6/10 9/13/10 9/20/10 9/27/10 10/4/10 10/11/10 10/18/10 10/25/10 11/1/10 11/8/10 11/15/10 11/22/10 11/29/10 12/6/10 12/13/10 12/20/10 12/27/10 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 1 0 0 1 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 0 0 8.4 9.2 8.2 9.8 10 8.8 9.8 8.4 8.4 10 8.8 8.2 8.6 8.8 8.8 8.2 9.4 10 10 9.8 8.6 8 9.2 8.2 10 9 10 9 9.2 9.6 8.6 9 8.4 8.2 9.4 10 8.4 8.6 9.2 8.6 10 8.4 9.6 9.8 8.2 10 9 10 9.6 1/31 1341 1216 962 1025 808 721 768 790 676 715 774 890 1658 2445 2968 2223 1691 1368 1324 1353 1225 2500 1131 1847 1596 1423 2320 1770 1535 1680 1363 796 720 614 607 1738 2331 2827 2151 1450 1573 1167 350 370 827 724 644 450 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $14,540 $12,337 $9,971 $9,428 $10,250 $7,110 $7,066 $6,451 $6,636 $6,760 $6,292 $6,347 $7,654 $14,590 $21,516 $24,338 $20,896 $16,910 $13,680 $12,975 $11,636 $9,800 $23,000 $9,274 $18,470 $14,364 $14,230 $20,880 $16,284 $14,736 $14,448 $12,267 $6,686 $5,904 $5,772 $6,070 $14,599 $20,047 $26,008 $18,499 $14,500 $13,213 $11,203 $3,430 $3,034 $8,270 $6,516 $6,440 $4,320 Data description: The ShoesSales file contains weekly sales data from March 2009 to December 2010 for the unit sales of one product, unit price, whether an ad campaign was run that week (1 = ad campaign, 0 = no ad campaign), competitor's price, target sales, and sales revenue. Use this file to perform the following exercises: 1). Create pivot table and pivot chart to summarize the effect of Ad campaign on sales revenue (15 points). 2). Create pivot table and pivot chart to analyze the nature of the monthly seasonality of sales revenue (15 points). 3). Forecast weekly sales revenue for each week in January and February 2011 (20 points). Week 3/2/09 3/9/09 3/16/09 3/23/09 3/30/09 4/6/09 4/13/09 4/20/09 4/27/09 5/4/09 5/11/09 5/18/09 5/25/09 6/1/09 6/8/09 6/15/09 6/22/09 6/29/09 7/6/09 7/13/09 7/20/09 7/27/09 8/3/09 8/10/09 8/17/09 8/24/09 8/31/09 9/7/09 9/14/09 9/21/09 9/28/09 10/5/09 10/12/09 10/19/09 10/26/09 11/2/09 11/9/09 11/16/09 11/23/09 11/30/09 12/7/09 12/14/09 12/21/09 12/28/09 1/4/10 1/11/10 1/18/10 Ad Unit Campaign Price 0 0 1 1 1 1 1 0 0 0 1 1 0 1 0 1 0 1 1 0 0 1 1 9.6 9.6 9.6 9 9.8 9.8 9.8 8.6 8 10 9.2 9 9.4 8.8 9.8 9.4 9 8.4 9.8 9.2 9.4 8.6 8.8 8.2 9.4 9.6 10 9.8 8 9.2 9.6 8.6 8 8.6 10 9.4 9.8 8.4 9.8 8.8 9.4 8.2 9.8 8.4 8.8 8.4 9.2 Units Sold 2235 1655 2373 3005 3107 2407 1925 1584 2481 2824 2137 666 1458 1302 1188 1100 1197 2500 2607 2175 1706 2561 2851 2582 2966 2329 1906 1257 2073 2542 2902 2293 1825 1535 1137 1041 941 844 250 330 682 761 794 292 533 1569 2247 Competi tor's $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 Sales Target Sales Revenue $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $21,456 $15,888 $22,781 $27,045 $30,449 $23,589 $18,865 $13,622 $19,848 $28,240 $19,660 $14,994 $13,705 $11,458 $11,642 $10,340 $10,773 $21,000 $25,549 $20,010 $16,036 $22,025 $25,089 $21,172 $27,880 $22,358 $19,060 $12,319 $16,584 $23,386 $27,859 $19,720 $14,600 $13,201 $11,370 $9,785 $9,222 $7,090 $2,450 $2,904 $6,411 $6,240 $7,781 $2,453 $4,690 $13,180 $20,672 2/1/10 2/8/10 2/15/10 2/22/10 3/1/10 3/8/10 3/15/10 3/22/10 3/29/10 4/5/10 4/12/10 4/19/10 4/26/10 5/3/10 5/10/10 5/17/10 5/24/10 5/31/10 6/7/10 6/14/10 6/21/10 6/28/10 7/5/10 7/12/10 7/19/10 7/26/10 8/2/10 8/9/10 8/16/10 8/23/10 8/30/10 9/6/10 9/13/10 9/20/10 9/27/10 10/4/10 10/11/10 10/18/10 10/25/10 11/1/10 11/8/10 11/15/10 11/22/10 11/29/10 12/6/10 12/13/10 12/20/10 12/27/10 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 1 0 0 1 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 0 0 8.4 9.2 8.2 9.8 10 8.8 9.8 8.4 8.4 10 8.8 8.2 8.6 8.8 8.8 8.2 9.4 10 10 9.8 8.6 8 9.2 8.2 10 9 10 9 9.2 9.6 8.6 9 8.4 8.2 9.4 10 8.4 8.6 9.2 8.6 10 8.4 9.6 9.8 8.2 10 9 10 9.6 1/31 1341 1216 962 1025 808 721 768 790 676 715 774 890 1658 2445 2968 2223 1691 1368 1324 1353 1225 2500 1131 1847 1596 1423 2320 1770 1535 1680 1363 796 720 614 607 1738 2331 2827 2151 1450 1573 1167 350 370 827 724 644 450 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $9 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $14,540 $12,337 $9,971 $9,428 $10,250 $7,110 $7,066 $6,451 $6,636 $6,760 $6,292 $6,347 $7,654 $14,590 $21,516 $24,338 $20,896 $16,910 $13,680 $12,975 $11,636 $9,800 $23,000 $9,274 $18,470 $14,364 $14,230 $20,880 $16,284 $14,736 $14,448 $12,267 $6,686 $5,904 $5,772 $6,070 $14,599 $20,047 $26,008 $18,499 $14,500 $13,213 $11,203 $3,430 $3,034 $8,270 $6,516 $6,440 $4,320

Answer rating: 100% (QA)

VB V2 fi 9824 021 60 762mm Vo A 178mm A 0 A 762mm V 268 ms AB170 mm fr... View the full answer