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.
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
i L A B O V E R V I E W
Scenario and Summary
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.
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
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.
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.
Navigate to your Transfer folder, select your DrinkSales.txt file and then click Open to select this file for
the File Name area.
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:
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
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).
k. Your screen should look similar to the following:
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.
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.
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:
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.
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.
Close your Microsoft SQL Server Management Studio when you have verified the import wizard created
correctly your DrinkSales table.
Your results at the end of the import wizard should look similar to this.
STEP 3: Sample Output Back to Top
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
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.
Click the Data ribbon, then select the From Other Sources icon, and click the From SQL Server
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:
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:
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
Go to the Insert ribbon and put your mouse over the i. Scatter diagram item:
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:
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:
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:
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.
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.
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:
11 of 21 9/30/2014 8:29 PM
Take a screen shot of this scatter plot and paste it in your Lab5_Questions
Here is complete solution for BIS445 Lab 5
If you have more questions, please contact me via email firstname.lastname@example.org
I will help you any time