This assignment consists of three parts. The assignment description is to assist managerial decisions using exel built in functions, pivot tables, filters, conditional formatting, and visualization techniques.
Part A (Business Intelligence Basics)- The effect of FTSE 100 Index and other variables on the GBP exchange rate in the UK (40%)
You are given the information related to the UK economy since 2010 on a daily basis. Suppose you want to develop a forecasting model to predict the value of EURO/GBP in the January 2021. Hence “EURO GBP Rate” is our response variable.
A.1. Insert a new column in front of each variable to calculate the percent of change (compared with the previous day). Apply conditional formatting to show increases with green arrows and decreases with red arrows.
A.2. Visualize the data on the line charts. Note that the values for the last three columns are much lower than the others. Hence, use the
secondary axis to illustrate them. Your chart should be self-explanatory so don’t forget to include all chart elements.
A.3. During Excel lab sessions, we have seen moving average method. Implement this method to predict the values of GBP/EURO in the
first week of Jan 2021.
Part B. The role of pivot tables in improving business data processing [30%].
You are given a dataset for a company including “Customer_ID”, “Area”, “Payment Method”, “Transaction Type”, “Amount”, “Product Type”,
and “Purchase Time”. Your job is to create a series of Pivot Tables (or Pivot Charts) to answer the following business questions. You must create
pivot tables in new sheets and name them properly.
B.1. What are the averages for purchases in each area?
B.2. What type of payment is the most frequent?
B.3. Do customers make their purchases at work (most likely between 8:00 and 17:00) or at home?
Part C. Data Reporting, Querying, Analysis in Excel [40%].
The purpose of this part is to test your knowledge of basic concepts and definitions of knowledge discovery in databases that form the pillars of
business intelligence applications. Based on the sheet Titanic that contains the following information on the passenger list of the Titanic: “class”,
“age”, “gender”, and “survived”. You should generate reports, write queries, and perform data analysis to understand whether survivors and
non-survivors exhibit a pattern in the Titanic accident. You may easily think of this part of the project in a different setting. For instance, suppose
that we are trying to distinguish between buyers and non-buyers of a particular product based on some transaction and demographic information
we have. The simplicity of the titanic example makes it a good case for beginners. As tools, you should use pivot tables, filters, and some data
analysis methods in Excel.
C.1. Using pivot tables for summarizing and reporting: Using a pivot table, analyze the number of survivors compared to the class of
passengers in an effort to see if passenger class might have an effect on survival. Justify your answer.
C.2. Perform a similar analysis with respect to a) age, b) gender, and c) a combination of various attributes on your own choice. Can you
make any conclusions based on these reports? For instance, can you hypothesize that women survived more than men? Did first class
passengers survive more than the rest? Here, you can look at counts, ratios, percentages, whichever you think is more appropriate.
C.3. Using Filters for query-like data processing: Filters under the Data menu can be used to generate a query-like environment in Excel.
Create various filters of your choice. What additional information can you obtain using filters as opposed to the reports we did for C.1.
and C.2.? Can you come to any conclusions as to who survived and who did not based on your analysis?
C.4. Let us define a survival model like female adult passengers survived, others did not. To be more explicit, this would mean “If
gender=female and age=adult, then “we guess the passenger survived”. For this, on a blank column type “Model1”. On the row of the
first passenger type “=IF(AND(“ Then click on the cell representing the gender of the passenger then type “=”Female”, ” then click on the
Approved BBS Coursework Brief Template 2020/21 3 of 5
Brunel Business School
cell representing the age of the passenger then type “=”Adult”);”guess_yes”;”guess_no”)” . If the logical expression is true, the cell value
will be guess_yes. Otherwise it will be guess_no. Of course this is not 100% correct.
Now, build a confusion matrix to see how accurate our rule is. For this, you can count your right guesses and false guesses and see how well you
did. A right guess is when survived=yes and model1=guess_yes OR survived=no and model1=guess_no. This can be done by IF functions, but a
pivot table would be much easier. Build a pivot table.
In the titanic dataset, there are 2201 records of passengers. So the total of numbers that appear in your confusion matrix should be 2201. Based
on this study, how right would it be to say “adult women survived more” in Titanic?
C.5. Come up with a different model than the survival model above and complete the evaluation in the same way. Call this model model2.
Is model2 better than model1 in explaining survivors? Why?
C.6. This question is optional. You may also explore other data analysis tools to determine the attribute “survived” using the other
attributes (age, gender, class) as inputs. For instance, converting the data into numerical form and performing a regression analysis would
be one of the choices if you are familiar with such techniques. In order to transform your data into numerical form, you can add a new
binary variable for differentiating the gender, add several binary variables that serve as indicators for the class of the passenger, etc.
Last Completed Projects
| topic title | academic level | Writer | delivered |
|---|
