iLab 5: Predict sales patterns using simple linear regression.
Joe Sanders scratched his head. He owned two Hungry Boy Subs franchises in a city. In an unusual
arrangement, one of the franchises was located in the food court of the local mall. The other franchise was
about two blocks down the street from the mall. The area in which Joe had purchased the franchises was in a
new end of town, well-known for its good schools, professional offices, and well-kept neighborhoods. The area
had been growing rapidly since Joe purchased the franchise about 10 months ago.
Joe purchased the local mall’s sub shop first, and found it to be immensely successful. Encouraged by the
success of the food court shop, he went on to purchase a new franchise in a street vendor area up the street.
Even though the two franchises were close together geographically, he felt they catered to different markets;
one to mall employees and mall-goers, and another to passers-by who had no intention of going to the mall.
Joe thought the sales patterns of his sales at the food court would help him forecast sales of his street
location, and thus help him determine quantities of various products to order. Particularly, Joe wanted to
forecast the number of drinks he would sell in each period and the percent of sales that are cold drinks.
Therefore, Joe collected data from both franchises and decided to do an analysis to see if he could design a
forecasting model for each location. He was also interested in analyzing buying patterns in both franchises to
see if there were any similarities or differences. The data is found in DrinkSales.txt.
Your task is to predict sales patterns using simple linear regression. You will import the comma delimited file
into a SQL database table that has marketing research data on drinking patterns and perform some basic
analyses on this data by creating a scatter plot. After you analyze this data you will use simple regression to
try to predict drink patterns.
Print
Submit your assignment to the Dropbox located on the silver tab at the
top of this page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due
dates.)
i L A B O V E R V I E W
Scenario and Summary
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
1 of 21 9/30/2014 8:29 PM
Submit the YourName_Lab5_Questions.docx to the Week 5 iLab Dropbox.
Section Deliverable Points
Part A Import comma delimited file and create SQL 2008 table. 10
Part B Create scatter plots on drink sales. 20
Part C Regression analyses on drink sales. 30
Download the Week5_Lab5_Questions.docx from DocSharing. You will answer the questions and
provide screen prints as required for each part of the lab.
1.
Create a folder on your local 2. drive named Transfer.
3. Download the Week5_DrinkSales.txt file from DocSharing to your Transfer folder.
Login to the Citrix remote lab.
a. Follow the login instructions located in the iLab tab in Course Home.
Upload your txt file to your Citrix drive using the instructions provided in the iLab tab in Course
Home.
b.
4.
Part A: Import your DrinkSales.txt File into an SQL 2008 Database
Import your DrinkSales.txt file, a comma delimited file, into a SQL 2008 database.
Open your Citrix Laboratory, click the SQL 2008 Import Utility, and then click Next to get to the Choose
a Data Source screen.
a.
b. On the choose a Data Source screen select for the Data Source the Flat File Source in the combo box.
Browse to your Transfer folder. When the Client File Security pop up window appears, select Full
Access and then click OK.
c.
Navigate to your Transfer folder, select your DrinkSales.txt file and then click Open to select this file for
the File Name area.
d.
e. Check the Column names in the first data row on the Choose a Data Source screen.
f. Your screen should look similar to the following:
Deliverables
i L A B S T E P S
Preparation Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Use the SQL Import Wizard Back to Top
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
2 of 21 9/30/2014 8:29 PM
Click Next until you get to the Choose A g. Destination screen.
h. Leave the default SQL Server Native Client 10.0 for Destination.
i. Insert into the Server Name combo box your server: BIS445SQL2008
In the Database combo box area select BIS445_CoffeeMerchant for your Database (you do not have
the access rights in the Citrix Lab area to create a new database).
j.
k. Your screen should look similar to the following:
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
3 of 21 9/30/2014 8:29 PM
Click Next to get to the Select Source Tables l. and Views screen.
Click Edit Mappings on the Select Source Tables and Views screen. Change the Type column to get
more meaningful SQL field types.
m.
n. Your field type selections should look similar to the following:
o. Take a screen shot of the above Column Mappings screen for your Lab 6 questions.
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
4 of 21 9/30/2014 8:29 PM
Click OK on the Column Mappings screen p. and the click Next.
q. On the Review Data Type Mapping screen, change both pull-down menus to Ignore:
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
5 of 21 9/30/2014 8:29 PM
Click Next twice and then Finish until you see the end of the SQL 2008 Import wizard. Note: You may
receive a warning message indicating that data might be truncated due to the column length being
reduced from 50 to 2.
Please disregard this and click OK since your Period column data length does not exceed 2 characters.
r.
Take a screen shot at the end of the import wizard for your s. Lab5_Question area.
Open up SQL Server 2008 Management Studio and check your DrinkSales table to make sure
everything was imported into your new table correctly.
t.
Close your Microsoft SQL Server Management Studio when you have verified the import wizard created
correctly your DrinkSales table.
u.
Your results at the end of the import wizard should look similar to this.
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
6 of 21 9/30/2014 8:29 PM
Using the Lab5_Questions.docx, paste your screen shots in the appropriate areas.
Part B: Create scatter plots of the period number and drink sale information.
Create a scatter plot of the period number and food court number of drinks sold, and then a scatter plot of
street drink sales.
STEP 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Use the SQL Import Wizard Back to Top
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
7 of 21 9/30/2014 8:29 PM
Open your Citrix Laboratory, click the icon for Microsoft Office 2010 Applications, and then select
Microsoft Excel 2010.
Note: You Must Use Microsoft Excel 2010 In The Citrix Environment.
a.
Click the Data ribbon, then select the From Other Sources icon, and click the From SQL Server
option.
b.
Insert into the Server name of the Data Connection Wizard the following: c. BIS445SQL2008
Click Next and then select the BIS44_CoffeeMerchant database and your DrinkSales table. Your
Select Database and Table screen should look like the following:
d.
e. Click Next and then Finish.
f. Your Import Data screen will then appear and accept the default information:
g. Click OK. Your Excel Spread Sheet should look like the following:
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
8 of 21 9/30/2014 8:29 PM
Create a scatter plot of your food court drink sales. Start by highlighting the left two columns in your
Excel spreadsheet:
h.
Go to the Insert ribbon and put your mouse over the i. Scatter diagram item:
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
9 of 21 9/30/2014 8:29 PM
When you put your mouse over the Scatter diagram icon, you will get a pop up window. Select the top
left scatter diagram format which is titled, Scatter with only Markers:
j.
After you select this format, a scatter diagram should appear on your spreadsheet. Move this scatter
diagram so that it does not overlap with your data:
k.
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
10 of 21 9/30/2014 8:29 PM
Now you need to format the title and axes of your scatter diagram. Make sure that you have selected
the Layout ribbon. You should see the Axis Titles menu item, select it:
l.
Select the primary horizontal axis title option, and then select the Title Below Axis option. A textbox
should appear below the X axis where you can type the name of the axis: Period.
m.
With the chart still selected, go back to the Axis Titles option on the Layout ribbon and choose
Primary Vertical Axis Title. Then select the Rotated Title option. A textbox should appear to the left of
the y-axis. Place your mouse inside this textbox and type the following: Number of Drinks Sold.
n.
Now you need to fix the title of the scatter plot. Go to the Layout ribbon again and put your mouse in
the Title textbox. Change this textbox to read Food Court Number of Drinks Sold by Period [Your
Name]. Your scatter plot should look similar to the following:
o.
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
11 of 21 9/30/2014 8:29 PM
Take a screen shot of this scatter plot and paste it in your Lab5_Questions.p. docx Section A area.
Create a second scatter plot which shows the Street Number of Drinks Sold by Period. For the
second scatter plot, select column A data and then hold CTRL and click and drag to select column C
data.
q.
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
12 of 21 9/30/2014 8:29 PM
Go to the Insert ribbon and select the Scatter diagram icon. Create a scatter diagram for the Street
Vendor Number of Drinks Sold by Period data that you have selected. Use the Layout ribbon to
create the necessary axis titles and format the title appropriately.
r.
Take a screenshot of your second scatter plot and paste it in your Lab5_Questions.docx in the
appropriate area.
s.
Your results at the end of the second Scatter plot diagram should look similar to this.
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
13 of 21 9/30/2014 8:29 PM
Using the Lab5_Questions.docx, paste your screen shots of your scatter plots in the appropriate areas.
Part C: Create regression analyses on drink sales.
After creating scatter plots on your drink sale data you will conduct regression analyses on your drink sales
data.
Review your scatter plots to get a feel for how drink sales have been a. changing over time.
Before you do your linear regression analyses, you need to make sure the Analysis Tookpak is
installed on your machine. To verify this, go to the Office button at the top left of your screen and left
click it. The following menu should appear:
b.
STEP 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
Step 2: Create the dimension and fact tables for your star schema data warehouse. Back to Top
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
14 of 21 9/30/2014 8:29 PM
Click on Excel Options at the bottom right of the screen. Then click on Add Ins that appears in the left
hand area. You should see the following screen:
c.
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
15 of 21 9/30/2014 8:29 PM
Verify that the Analysis TookPak is installed. If it is installed, it will appear in the Active Application
Add-Ins. If it is not installed, then it will appear in the Inactive Add-Ins area. If the Analysis ToolPak is
not installed, click on it in the Inactive Add-Ins section of the screen and then click Go. In the Add-Ins
dialog select Analysis ToolPak and click OK. The Analysis TookPak should now appear in the Active
Application Add-Ins area and in the Data ribbon.
d.
Replace cell values A2:A11 with the corresponding number on your keyboard. For example, in cell A2
enter new value of 1, in cell A3 enter a value of 2, etc. Initially this will not appear to change any values;
however, when the database was imported into excel additional data was imported into cells A2:A11
e.
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
16 of 21 9/30/2014 8:29 PM
which would cause problems in later steps.
Select the Data ribbon, Data Analysis to open the f. following dialog:
g. Select Regression and press OK to produce this screen:p
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
17 of 21 9/30/2014 8:29 PM
In the Input Y Range textbox select the h. cells in B2 to B11:
i. Put your mouse in the Input X Range textbox and select the numerical period values:
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
18 of 21 9/30/2014 8:29 PM
Now select the Output Range Radio button, and then select cell $A$21. This indicates where Excel
should put the linear regression output:
j.
Press OK. You should get the following linear regression output describing the relationship between
period number and food court num sales:
k.
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
19 of 21 9/30/2014 8:29 PM
Take a screen shot of your above regression results and paste it to your Lab5_Questions.docx
Section C area.
l.
Repeat this process for the relationship between period number and street drink sales (repeat steps g
through p). Your regression entry screen should resemble the following:
m.
When you are finished, you should have two sets of regression output n. under your data.
o. Take screen shot of your final results.
Your final results should look similar to the following:
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
20 of 21 9/30/2014 8:29 PM
Using the Lab5_Questions.docx, paste your screenshots and answer the questions for Part C.
Submit your completed YourName_Lab5_Questions.docx to the Week 5 iLab Dropbox.
STEP 4: Deliverable Back to Top
Final Deliverable Back to Top
Submit your assignment to the Dropbox located on the silver tab at the
top of this page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due
dates.)
iLab https://devry.equella.ecollege.com/file/c231695a-2060-49fc-b6e9-1b120…
21 of 21 9/30/2014 8:29 PM