Bus231 about Excel

Assignment 3 Part A: The objective of this exercise is to smooth out seasonal data, first by deflating and then by using a seasonal adjustment technique. Data: From CANSIM (must be done on campus in order to get free access to data), download the Canadian CPI data - 2011 basket - health and personal care, for all available quarters 2004 Q1 - 201 5 Q4 (Table 326 -0020 in CANSIM). Also download the quarterly sales data for health and personal care products as reported by large retailers (Table 080 - 0009 in CANSIM). First deflate the sales data using the CPI, and then further smooth it by de -seasonalizing it. Now build a forecasting model to predict actual sales for all quarters for 201 6 To download data: 1. Go to Camosun.bc.ca –Library – Article & Database s – database by subject or titles Scroll down to, select CANSIM from Statistics Canada . 2. Search Table 326 -0020 3. Click on the Tab “ADD/REMOVE DATA”, Choose Canada, Health and Personal Care, Jan 2004 -Dec 201 5, HTML TABLE times as rows, Normal Retrieve, then click “APPLY”. 4. Click on the Tab “Manipulate”, Select: Quarterly Data (Average), Use Calendar Year, Data as Retrieved, APPLY 5. Click on the Tab “Download”, Select Time as Rows, and everything else by default. 6. Download the Data Now you shoul d be getting the CPI Canada, Health and Personal Care from 2004 Q1 till 201 5 Q4 Now go back to CANSIM, Search for Survey of Large Retailers 1. Select: Survey of Large Retailers (080 -0009) 2. Select: Health and personal care products, unadjusted, Jan 20 04 -Dec 2015 , HTML TABLE times as rows, Normal Retrieve, then click “APPLY”. 3. Click on the Tab “Manipulate”, Select: Quarterly Data (Average), Use Calendar Year, Data as Retrieved, APPLY 4. Click on the Tab “Download”, Select Time as Rows, and everything else by default. 5. Download the Data You should put the two series of data onto one sheet in an Excel File, and name it as Assignment2_last name Please  convert the sales number to d eflated sales using CPI;  de -seasonalize the deflated sales number using seasonal index;  run a time -series regression model; forecast for all quarters for 2015 Part B : Please refer to the Excel file, Assignment 03 Part B on D2L Ex2: The safety director of a large steel mill took samples at random from company records of minor work -related accidents and classified them according to the time the accident took place. Using the goodness -of-fit test and the .01 level of significance, determine whether the ac cidents are evenly distributed throughout the day. Write a brief explanation of your conclusion. Ex3: The IRS was interested in the number of individual tax forms prepared by small accounting firms. They usually expected 5% for 20 -30, 25% for 30 -40, 45% for 40 -50, 15 % for 50 -60, and 10% for 60 -70. The IRS randomly sampled 50 public accounting firms with 10 or fewer employees in the Dalla -Fort worth Area. The following frequency table reports the results of the study . Use 0.05 significance level, please conduct a test to see whether these 50 firms have the same number of clients as expected by IRS. EX4: The director of advertising for the Victoria Sun Times, the largest newspaper in the region , is studying the relationship between the type of community in which a subscriber resides and the section of the newspaper he or she reads first. For a sample of readers, she collected the sample information in the following table. At the .05 significance level, can we conclude there is a relationship be tween the type of community where the person resides and the section of the paper read first?