PRACTICAL FILE
MBA (DUAL)
SEMESTER -2
ACADEMIC YEAR: 2024-26
SUBJECT: IT Skills Lab-2
( BMB251)
(2024 -2026)
SUBMITTED BY: KALYANI KUMARI SUBMITTED TO:
Scholar ID: 2401057 Dr. AJAY TRIPATHI
(Associate Professor)
Table of Contents
S. No Contents Page No.
Unit-1
I. Given the data of Product Sales (ITSkill2_WS01_Pivot Table
Question.xlsx), Create the following Pivot Tables to analyze
I. Gross Sales by Country using Pivot Table
II. Total Profits by Country and by Products (rows & Columns)
1-3
III. Total Profits by Country and by Products (Rows only)
IV. Gross Sales for Country and Product.
V. Apply filter for Year, Profit by Product.
VI. Use Slicer on Month to filter the data.
2. Use Goal Seek to solve the following (ITSkill2_WS02_Goal Seek
Question)
I. To save $10,000 in 24 months, calculate how much one
need to save each month to meet this goal.
II. Calculate the rate of interest when the principal amount, 4-5
time period, and simple interest are already known.
III. To find the loan amount that produces a monthly payment of
$1500.
IV. if you sell 100 items at $5 each, minus the 10%
commission, you will make $450. How many items do you
have to sell to make $1,000?
3, Use Solver to (ITSkill2_WS03_Solver Question.xlsx) Calculate the
6
Total Profit for Cycle Trader using LP.
4. One Variable Data Table (ITSkill2_WS04_Data Table Question) -
Assume you own a book store and have 100 books in storage. One
can sell a certain % for the highest price of $50 and a certain % for
the lower price of $20. 7
If you sell 60% for the highest price, Calculate the profit using Data
Table in each case.
5. Two Variable Data (ITSkill2_WS04_Data Table Question) :
For a Book store data provided in (ITSkill2_WS04_Data Table 8
Question), Calculate the percentage of books sold at highest price to
maximize the total profit
6. Scenario Analysis: Create a profit model for the data given in 9
ITSkill2_WS05_Scenario Manager Question given:
Input cells: Price, Units Sold, Cost
Formula cell: Profit = (Price × Units Sold) -
Cost You can create:
Best Case (High Price, High Sales, Low Cost)
Worst Case (Low Price, Low Sales, High
Cost) Expected Case (Average values)
7 In the Data Validation Techniques Sheet (ITSkill2_WS06_Data 10
Validation Question), Write data validation criteria to
I. Insert marks of student from 1 -50
II. Insert Subject from given list of subjects
Insert date from 1 July2025 - 31 Aug 2025.
8. Macros – Create a Macro to simplify everyday task of data entry by 11
uploading a CSV file. (ITSkill2_WS07_VBA Macro Question.xlsx)
Unit -2: Charts and Dashboard
9. Based on the data given in ITSkill2_WS08_Charts 01_Question.xlsx
(Sheet: Column Chart), Create Column Chart from given data
I. Column Chart by Genre
II. Column Chart by Product
III. Add Chart title, X label, y label, legend and Trend Line.
IV. Switch Rows and Columns
11. Based on the data given in ITSkill2_WS08_Charts 01_Question.xlsx
(Sheet: Bar Chart), Create Bar Chart from given data
I. Bar Chart by Region
II. Bar Chart by Product
III. Add Chart title, X label, y label, legend and Trend Line
12. Based on the data given in ITSkill2_WS08_Charts 01_Question.xlsx
(Sheet: Line Chart), Create Line Chart from given data
I. Add Chart title, X label, y label, legend and Trend Line
II. In the line chart remove the data for walrus
III. Show the gridlines
IV. The color for three lines should be red, blue and green
13. Based on the data given in ITSkill2_WS09_Charts 02_Question.xlsx
(Sheet: Area Chart), Ex - Create Area Chart from given data
14. Based on the worksheet given in ITSkill2_WS09_Charts
02_Question.xlsx (Sheet: Pie Chart), Ex - Create Pie Chart from
given data:
I. Add Chart title
II. Show Data Labels using Data Call outs
III. Pull out a slice
15 Based on the worksheet given in ITSkill2_WS09_Charts
02_Question.xlsx (Sheet: Doughnut Chart), Ex - Create Doughnut
Chart from given data.
16 Based on the worksheet given in ITSkill2_WS10_Charts
03_Question.xlsx (Sheet: Scatter Chart), Ex - Create Scatter Chart
from given data.
Identify the type of distribution
Add a Trend Line
Format the chart
17 Based on the worksheet given in ITSkill2_WS10_Charts
03_Question.xlsx (Sheet: Bubble Chart), Ex - Create Bubble Chart
from given data
You are analyzing sales performance for five stores. You want to
visualize:
Sales (in $M) → as the X-axis
Customer Satisfaction (out of 10) → as the Y-axis
Number of Employees → as the bubble size
18. Based on the worksheet given in ITSkill2_WS12_Charts
04_Question.xlsx (Sheet: Map Chart), Create Map chart of US
States for the Sales Data.
19. Based on the worksheet given in ITSkill2_WS12_Charts
04_Question.xlsx (Sheet: Combo Chart), Use Lamborghini Sales
Chart, Create combo chart
i. Line and Column for Year, Sales and Growth
ii. Column and multiple line
iii. Area and Column
iv. Stacked Area and Bar
v. Line and Clustered column
vi. Line and Stacked Column
vii. Line and 100% Stacked Column
20. Create a Dashboard for the following case study
(ITSkill2_WS14_Dashboard_Case_CookieSales_Question)
A cookie company wants to create a Dashboard displaying
1. Profit each month
2. Units sold each month
3. Profit by Product and by Country
4. The manger should be able to filter out data based on
country, Product and Date
5. The dashboard should be able to refresh all the charts with the
new data .
I. Gross Sales by Country using Pivot Table
II. Total Profits by Country and by Products (rows & Columns)
III. Total Profits by Country and by Products (Rows only)
IV. Gross Sales for Country and Product.
V. Apply filter for Year, Profit by Product.
VI. Use Slicer on Month to filter the data.
I. To save $10,000 in 24 months, calculate how much one need to save each
month to meet this goal.
II. Calculate the rate of interest when the principal amount, time period, and simple interest
are already known.
III. To find the loan amount that produces a monthly payment of $1500.
IV. if you sell 100 items at $5 each, minus the 10% commission, you will make
$450. How many items do you have to sell to make $1,000?
3. Use Solver to (ITSkill2_WS03_Solver Question.xlsx) Calculate the Total Profit for
Cycle Trader using LP.
4. One Variable Data Table (ITSkill2_WS04_Data Table Question) - Assume you own a
book store and have 100 books in storage. One can sell a certain % for the highest price of $50 and a
certain % for the lower price of $20.
If you sell 60% for the highest price, Calculate the profit using Data Table in each case.
5. Two Variable Data (ITSkill2_WS04_Data Table Question) :
For a Book store data provided in (ITSkill2_WS04_Data Table Question), Calculate the
percentage of books sold at highest price to maximize the total profit
6. Scenario Analysis: Create a profit model for the data given in
ITSkill2_WS05_Scenario Manager Question given:
Input cells: Price, Units Sold, Cost
Formula cell: Profit = (Price × Units Sold) -
Cost You can create:
Best Case (High Price, High Sales, Low Cost)
Worst Case (Low Price, Low Sales, High Cost)
Expected Case (Average values)
1. Input cells: Price, Units Sold, Cost
2. Best Case (High Price, High Sales, Low
Cost) Worst Case (Low Price, Low Sales, High
Cost) Expected Case (Average values)
Input cells: Price, Units Sold, Cost
7. In the Data Validation Techniques Sheet (ITSkill2_WS06_Data
Validation Question), Write data validation criteria to
I. Insert marks of student from 1 -50
II. Insert Subject from given list of subjects
Insert date from 1 July2025 - 31 Aug 2025.
8. Macros – Create a Macro to simplify everyday task of data entry by uploading a
CSV file. (ITSkill2_WS07_VBA Macro Question.xlsx)
Unit -2: Charts and Dashboard
9. Based on the data given in ITSkill2_WS08_Charts 01_Question.xlsx (Sheet:
Column Chart), Create Column Chart from given data
I. Column Chart by Genre
II. Column Chart by Product
III. Add Chart title, X label, y label, legend and Trend Line.
IV. Switch Rows and Columns
11. Based on the data given in ITSkill2_WS08_Charts 01_Question.xlsx (Sheet: Bar Chart), Create
Bar Chart from given data
I. Bar Chart by Region
II. Bar Chart by Product
III. Add Chart title, X label, y label, legend and Trend Line
12. Based on the data given in ITSkill2_WS08_Charts 01_Question.xlsx (Sheet: Line Chart),
Create Line Chart from given data
I. Add Chart title, X label, y label, legend and Trend Line
II. In the line chart remove the data for walrus
III. Show the gridlines
IV. The color for three lines should be red, blue and green
13. Based on the data given in ITSkill2_WS09_Charts
02_Question.xlsx (Sheet: Area Chart), Ex - Create Area Chart
from given data
14. Based on the worksheet given in ITSkill2_WS09_Charts02_Question.xlsx (Sheet: PieChart), Ex -
Create Pie Chart from
given data:
I. Add Chart title
II. Show Data Labels using Data Call outs
III. Pull out a slice
15. Based on the worksheet given in ITSkill2_WS09_Charts02_Question.xlsx (Sheet: Doughnut
Chart), Ex - Create Doughnut
Chart from given data.
16. Based on the worksheet given in ITSkill2_WS10_Charts03_Question.xlsx (Sheet: Scatter Chart),
Ex - Create Scatter Chart from given data.
Identify the type of distribution
Add a Trend Line
Format the chart
17.Based on the worksheet given in ITSkill2_WS10_Charts03_Question.xlsx (Sheet: Bubble Chart),
Ex - Create Bubble Chart from given data
You are analyzing sales performance for five stores. You want to visualize:
o Sales (in $M) → as the X-axis
o Customer Satisfaction (out of 10) → as the Y-axis
o Number of Employees → as the bubble size
18.Based on the worksheet given in ITSkill2_WS12_Charts04_Question.xlsx (Sheet: Map Chart),
Create Map chart of US
States for the Sales Data.
19.Based on the worksheet given in ITSkill2_WS12_Charts 04_Question.xlsx (Sheet: Combo Chart),
Use Lamborghini Sales Chart, Create combo chart
i. Line and Column for Year, Sales and Growth
ii. Column and multiple line
iii. Area and Column
iv. Stacked Area and Bar
v. Line and Clustered column
vi. Line and Stacked Column
vii. Line and 100% Stacked Column
20. Create a Dashboard for the following case study
(ITSkill2_WS14_Dashboard_Case_CookieSales_Question)
A cookie company wants to create a Dashboard
displaying
1. Profit each month
2. Units sold each month
3. Profit by Product and by Country
4. The manger should be able to filter out data based on
country, Product and Date
5. The dashboard should be able to refresh all the charts with the
new data .
