GURU GOBIND SINGH INDRAPRASTHA UNIVERSITY
UNIVERSITY SCHOOL OF MANAGEMENT STUDIES
SESSION: 2023-2024
BATCH: 2023-2025
BUSINESS ANALYTICS – LAB
MFA-120
PRACTICAL FILE
MBA(FA)-II Sem
SUBMITTED BY: SUBMITTED TO:
NAME-MUSKAN DR. PRAVEEN KUMAR
ENROLLMENT NO.- 04116659423 ASSISTANT PROFESSOR
ACTIVITY 1
4 Types of Data – Nominal, Ordinal, Discrete and Continuous
QUESTION: As a Business Analyst of a FMCG company, you are being asked
to collect the data from shoppers of Delhi NCR region. This data will be used
for new product promotions and also to align the product in competitive pricing
scenarios.
1. Identify the questions, data types and their relevance.
2. Design a simple questionnaire in word document.
3. Create a XLS file with dummy data as probable outcome.
ANALYSIS
ACTIVITY 2
BUSINESS DECISION MODELS
GFA-9 NETWORK AND OPTIMIZATION MODELS
QUESTION: LINEAR PROGRAMMING AƩEMPTS TO EITHER MAXIMIZE OR
MINIMIZE THE VALUES OF AN OBJECƟVE FUNCƟON. A SOLVER PROGRAM CAN
BE USED FOR BOTH EQUAƟON-SOLVING OR GOAL-SEEKING AND CONSTRAINED
OPƟMIZAƟON, USING LINEAR PROGRAMMING, NONLINEAR PROGRAMMING,
AND INTEGER PROGRAMMING METHODS
ANALYSIS
This scenario involves a combination of data analysis and mathematical modelling to enhance
Amazon's transportation efficiency. Analysing Amazon's transportation model involves
evaluating various aspects, such as delivery efficiency, cost optimization, and customer
satisfaction.
Key metrics for analysis may include:
1. Delivery Time: Evaluate the time taken for packages to reach customers and identify
patterns or bottlenecks that may affect delivery speed.
2. Route Optimization: Analyze historical data to optimize delivery routes, minimizing travel
time and fuel costs while ensuring timely deliveries.
3. Cost Efficiency: Assess transportation costs by analysing fuel consumption, vehicle
maintenance, and other expenses, aiming to identify areas for cost reduction.
4. Customer Satisfaction: Utilize customer feedback data to understand the impact of
transportation on overall satisfaction. Identify areas for improvement based on reviews and
ratings.
5. Inventory Management: Integrate data on inventory levels with transportation data to
optimize supply chain efficiency and reduce stock outs or excess inventory.
Proposed Solution FOR DUMMY DATA AS TRANSPORTATION COST BY AMAZON
BACKGROUN[Link]
Last-Mile Delivery Efficiency: Last-mile delivery, the final leg of the delivery process to the
customer's doorstep, is a critical challenge for Amazon. They need to optimize routes, delivery
schedules, and logistics operations to ensure faster and more efficient last-mile delivery. With
a vast product range and a global customer base, Amazon faces the challenge of optimizing
its fulfilment network to ensure timely delivery while minimizing costs. They need to
determine the optimal location of warehouses and distribution centres to serve customers
efficiently.
–––
ACTIVITY 3
LOOKUP FUNCTIONS AND DATA VALIDATION
PERFORM THE FOLLOWING:
- EXCEL LOOKUP FUNCTIONS
- VERTICAL LOOKUP IN COLUMNS
- HORIZONTAL LOOKUP IN ROWS
- XLOOK UP (LOOKUP WITH MULTIPLE CRITERIA)
ACTIVITY 4
DATA VALIDATION AND WHAT IF ANALYSIS
QUESTION: Perform the following:
Q1. The function XLOOKUP
XLOOKUP (what want to look, lookup list, result list)
1. All the Customers from India
2. Amount list of items procured
OR
Q2. Missing data details in Customer_ID, CustomerTier, Storeid
Q3. Total_spend less then 100, also identify the “0” list
Q4. Signed_up_app_date frequency table.
Q5 Perform the What-if Analysis task as per the XLS as attached
- SCENARIO MANAGER
- GOAL SEEK
- DATA TABLE
ACTIVITY 5
IDENTIFICATION OF OUTLIERS THROUGH NORMAL DISTRIBUTION
AND BOX PLOTS
Question: Perform the following:
Q1. Identify the data trends using Mean, Standard Deviation and Variance with OPEN, Close
and No of trades.
Mean
Standard
deviation
variance
Q2. Use the data of close price to identify the outliers using
1. Sorting method
2. Visualisation method
3. Z-score
4. IQR
ACTIVITY 6
MAHALANOBIS’ DISTANCE SPREADSHEET MODELLING FOR
BUSINESS DECISIONS
Q1. Identify the data trends using Mean, Standard Deviation and Variance.
Q2 Calculate the Mahalanobis’ distance for the following:
1. Data of (Height, Age, Marks obtained)
2. Data of (Share price, Inflation, CurrencyValue)
3. Data of (Team, no_of_wins, Price_money
outliers using mahalanobis.
ACTIVITY 7
Outlier Detection Methodologies
RESULTS OF DUMMY SITUATION
NK
ACTIVITY 8
REGRESSION ANALYSIS SPREADSHEET MODELLING FOR
BUSINESS DECISIONS
RESULTS OF DUMMY SITUATION
