lOMoARcPSD|44729796

IT Skills Assignment (BMB251): Exploring Microsoft Office

Applications
management and technology (Sunderdeep Group of Institutions)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by chanchal ytn ([email protected])
 lOMoARcPSD|44729796

MONAD UNIVERSITY
(SCHOOL OF MANAGEMENT & BUSINESS STUDIES)

An

ASSIGNMENT

ON

IT SKILLS-2 (BMB251)
Project

Submitted in Partial Fulfilment for the award of the Degree of

MBA
MASTER OF BUSINESS ADMINISTRATION

Academic Session
2024-2026
Submitted To: Submitted By:
Dr. Arun Jadon Ishika Tyagi
(H.O.D MBA-2nd Semester
Enrollment No.:2403261
Roll no.: - 241071922095

N.H. 9, Delhi Hapur Road Village & Post Kastla, Kasmabad, Pilkhuwa, Uttar
Pradesh 245304

Page. 1

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

DECLARATION

I, Ishika Tyagi, a student of MBA (Session 2024-26) at Monad University, hereby

declare that the assignment has been completed under the valuable guidance of Dr. Arun
Jadon (HOD).

This project is an original work undertaken as part of the curriculum requirements for the
IT Skills course. The content herein represents my understanding and research on the
significance and applications of the Microsoft Office Suite in a business context. I have
ensured that the work reflects a detailed exploration of the tools, functionalities, and
benefits offered by Microsoft Office, including Word, Excel, PowerPoint, and Outlook,
in enhancing business operations and productivity.

I confirm that this assignment is a genuine effort and has not been copied or reproduced
from any unauthorized source. Any references, if used, have been duly acknowledged in
the work.

I express my sincere gratitude to Dr. Arun Jadon for his invaluable guidance and support
in completing this project successfully.

Name: Ishika Tyagi

Enrollment No.: 2403261
Roll no.: 241071922095
Course: MBA (2nd semester, 1 year)
Date:

Page. 2

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

CERTIFICATE

This is to certify that Ishika Tyagi, a student of the MBA(2nd semester, 1

year) program (Session: 2024-2026) at Monad University, has successfully
completed the project

The project was undertaken as part of the academic curriculum under the
guidance of Dr. Arun Jadon (HOD), whose expertise played a key role in
its success.

The Objective was to explore and apply the functionalities of Microsoft

Office Suite in a business context. The project reflects Krishna’s analytical
skills and ability to use tools like Microsoft Word, Excel, and PowerPoint
to address business challenges.

Krishna Nand Gupta has demonstrated strong commitment and met all
academic and technical requirements. This work showcases his ability to
leverage IT tools for business productivity.

We extend our heartfelt congratulations and best wishes for his future
endeavours

Certified by:
Dr. Arun Jadon
(HOD)
Date:
Signature:

Page. 3

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

ACKNOWLEDGMENT

I would like to express my sincere gratitude to all those who contributed to

the successful completion of this assignment. First and foremost, I am
deeply grateful to Dr. Arun Jadon, Head of the Department (HOD) of
MBA, Monad University, for his constant support, guidance, and
encouragement throughout the course of this project. His expertise and
constructive feedback have played a vital role in shaping the content of this
assignment. The knowledge imparted by him has enriched my
understanding of the Microsoft Office Suite and its applications in the
business world.

I would also like to thank all the faculty members and staff at Monad
University for providing an excellent learning environment and necessary
resources for the successful completion of this project. Their consistent
support throughout the academic journey has been invaluable.

I am equally thankful to my family and friends for their unwavering support,

motivation, and patience during the course of my studies. Their
encouragement has been a constant source of strength.

Lastly, I acknowledge all the sources and references used in this assignment,
which have been duly cited, ensuring that this work is based on authentic
and credible information.

Page. 4

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Abstract
This assignment, focuses on the practical applications of Microsoft Word,
Excel, and PowerPoint in business environments. The primary aim of the
project is to demonstrate how these widely used tools contribute to the
efficiency and productivity of modern businesses.
Microsoft Word is explored in the context of creating professional business
reports, documents, and correspondence. The assignment highlights the
importance of formatting, structuring, and presenting information in a clear
and concise manner, essential for business communication.

Excel’s practical use is discussed through its capability to organize, analyse,

and visualize data. The creation of business reports and charts, along with
the use of formulas for data analysis, is emphasized to support decision-
making and improve operational performance.

PowerPoint is examined in terms of its role in creating impactful

presentations. The project illustrates how business professionals use
PowerPoint to effectively communicate ideas, strategies, and results to
various stakeholders.

By focusing on these tools, this assignment showcases their significance in

improving business operations, enhancing communication, and fostering
data-driven decision-making.

Page. 5

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Index
SR Page
Questions
No. No.

1 Monthly Sales Data Analysis with Charts 07-10

2 Student Marks Performance Visualization 10-12

3 Combined Chart for Daily Temperature Readings 12-13

Surface Chart for Terrain or Temperature
4 Variation 13-14

5 Bubble Chart for Dataset Analysis 14-17

6 Product-Wise Revenue Share with Pie Chart 17-21

7 Customizing Value Axis Range in Charts 21-24

8 Pivot Table for Sales Data Analysis 24-28

9 Data Validation Rules Application 28-33

10 Student Grade Entry Sheet with Validation 33-37

11 Spreadsheet-Based Mini-Application Design 37-40

12 Financial Data Analysis with Display Units 41-43

13 Creating a Macro to Automate Tasks 43-46

Data Entry and Analysis Template for a Small
14 Business 46-49
Budgeting Scenarios and Scenario Summary
15 Report 49-52

16 Appendix 52-54

Page. 6

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

1. Create a spreadsheet showing monthly sales data of a company for four

different products. Use this data to create a Column Chart, Bar Chart, Line
Chart, and Pie Chart.
Insert screenshots of each chart and explain their differences in
representation.

Dataset: Monthly Sales Data of a Company (in Units)

Product Product Product Product

Month
A B C D

January 120 90 150 100

February 135 100 170 110

March 150 95 180 120

April 160 110 190 130

May 170 120 210 140

1. Column Chart

A Column Chart displays vertical bars to show comparisons among categories.

In this case, we use it to compare monthly sales of different products.

• Best Used For: Comparing sales performance across months.

• Analysis: The chart clearly shows that Product C has consistently higher
sales than the other products, with an increasing trend from January to
May.

Page. 7

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Column Chart
250

200

150

100

50

0
January February March April May

Product A Product B Product C Product D

2. Bar Chart

A Bar Chart is similar to a Column Chart, but the bars are horizontal.

• Best Used For: Better readability when category names (months) are
long or when you need to save vertical space.

• Analysis: Helps in comparing sales values side by side, especially if we

sort the bars for easy comparison.

Bar Chart

May

April

March

February

January

0 50 100 150 200 250

Product D Product C Product B Product A

3. Line Chart

A Line Chart connects data points with lines, showing trends over time.

• Best Used For: Visualizing growth or decline in sales over months.

Page. 8

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

• Analysis: You can easily observe the upward trend in sales for all
products, especially for Product C, which shows steep growth.

Line Chart
250

200

150

100

50

0
January February March April May

Product A Product B Product C Product D

4. Pie Chart

A Pie Chart is ideal for showing how a total amount is divided among parts. It is
used here to show the sales contribution of each product in May.

• Best Used For: Showing proportions at a specific point in time.

• Analysis: Product C occupies the largest portion of the pie, indicating

it’s the top-selling product.

Pie Chart
May 16%

February

March

January February March April May

Page. 9

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Comparison & Differences

Chart
Usefulness Limitations
Type

May get cluttered with too

Column Easy for comparison
many data points

Horizontal format makes Similar to Column Chart in

Bar
labels readable data

Excellent for showing Not suitable for comparing

Line
trends values directly

Good for proportion Not ideal for comparing

Pie
analysis exact values

2. Using student marks in 5 subjects, create an XY Scatter

Chart and an Area Chart. Highlight the differences in trends
and visually explain the usefulness of each chart for
performance tracking.

Dataset: Student Marks in 5 Subjects

Student Math Science English History Computer

A 85 90 75 80 95
B 70 80 65 60 85
C 90 85 80 85 90
D 60 70 55 65 75

1. XY Scatter Chart

An XY Scatter Chart shows the relationship between two variables using data
points on the X and Y axes.

Page. 10

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

• Purpose: Analyzing correlation between two subjects (e.g., Math vs

Science).

Page. 11

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

• Analysis: If most points form a straight line, it shows a strong

relationship.

• Use Case: Ideal for checking if strong Math students also score well in
Science.

XY Scatter Chart
100
90
80
70
60
50
40
30
20
10
0
0 1 2 3 4 5 6

2. Area Chart

An Area Chart is a line chart with the area below the line filled in.

• Purpose: Showing total and individual performance across all subjects.

• Analysis: Visual comparison of student performance—higher filled

areas = better marks.

• Use Case: Great for comparing complete subject profiles of students.

Area Chart
400

300

200

100

0
Math Science English History Computer

Page. 12

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Comparison

Chart
Pros Cons
Type

Excellent for finding

XY
correlation between two Limited to two variables
Scatter
subjects

Area Shows full subject-wise Might be hard to interpret

Chart performance if overlapping occurs

3. Prepare a dataset showing daily temperature readings of two

cities for 10 days. Use this to create a Combined Chart (Column +
Line). Label all components clearly and explain the benefit of a
combined chart.

Dataset: Temperature Readings (in °C)

Day City A City B

Day 1 30 28
Day 2 31 29
Day 3 29 27
Day 4 32 30
Day 5 33 31
Day 6 34 32
Day 7 32 30
Day 8 31 29
Day 9 30 28
Day 10 29 27

Combined Chart (Column + Line)

Column Chart: Used for City A

Line Chart: Used for City B

Page. 13

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Purpose

Combining these two types makes it easier to:

Compare daily values (City A – columns)

Observe trends (City B – line)

Benefits of Combined Chart

Shows both quantity and trend together.

Helps identify which city had consistently higher temperatures.

Great for comparing two sets of data with different visual needs.

Components

X-Axis: Days

Y-Axis: Temperature

Legend: City A (Bar), City B (Line)

Title: "Daily Temperature Comparison of City A & City B"

4. Design a Surface Chart using elevation data of a terrain or

temperature variation over an area. Explain how surface
charts provide a 3D perspective. Include a screenshot and
label axes and titles
Sample Dataset: Elevation (in meters)

X1 X2 X3 X4 X5

Y1 100 120 130 125 110

Y2 105 125 135 130 115

Y3 110 130 140 135 120

Y4 100 115 125 120 105

Y5 95 110 120 115 100

Page. 14

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Surface Chart Overview

Surface Charts provide a 3D view of data variation over a surface or region.

Use Case: Perfect for elevation models, temperature distribution, or

scientific measurements.

Surface Chart

150

100
Y5
50 Y4
Y3
0
X1 Y2
X2
X3 Y1
X4
X5

0-50 50-100 100-150

Benefits

Reveals patterns in complex datasets.

Enables better spatial decision-making (e.g., site planning, weather

mapping).

Adds depth and elevation perspective missing in flat charts.

Labeled Components

X-Axis: Distance X

Y-Axis: Distance Y

Z-Axis: Elevation (shown as color gradient)

Page. 15

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

5. Take any dataset (e.g., population, revenue, internet usage)

and create a Bubble Chart. Clearly label the X, Y, and
bubble size data series. Explain how the Bubble Chart
provides deeper insights than a standard scatter plot.

Dataset:

GDP per
Population Internet Users
Country Capita
(Millions) (Millions)
(USD)

USA 331 290 65000

India 1400 800 2500

Brazil 213 160 8700

Germany 83 77 48000

Nigeria 206 86 2000

Bubble Chart Details

X-Axis: Population

Y-Axis: Internet Users

Bubble Size: GDP per Capita

Page. 16

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Bubble Chart
80000
70000
60000
50000
40000
30000
20000
10000
0
-10000 0 1 2 3 4 5 6
20000

Population (Millions) GDP per Capita (USD)

BUBBLE CHART
80000
70000
USA, 65000
60000
50000 Germany, 48000
40000
Axis Title

30000 Population (Millions)

20000 GDP per Capita (USD)
10000 8700
0 an aa
-10000 0 1 2 3 4 5 6

20000
Axis Title

Purpose

Combines three variables in one view.

Large bubble = higher economic prosperity.

Helps in identifying underdeveloped vs developed digital economies.

Benefits Over Scatter Plot

Scatter plots show only two dimensions.

Bubble chart adds a third dimension with size.

Better storytelling—can reveal patterns across economy, population, and

internet usage.

Page. 17

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Visual Insights

India has high population and internet users, but smaller bubble = lower
GDP.

USA has fewer people than India but higher bubble size = stronger economy.

Nigeria has a small bubble, showing economic disparity despite population.

Dataset for Pie Chart: Product-wise Revenue Share

Product Revenue (in ₹)

Product A 40,000

Product B 25,000

Product C 20,000

Product D 15,000

● Step-by-Step Instructions:
1. Create the Pie Chart
• Select the data range (Product names and Revenue).
• Go to Insert → Pie Chart → 2-D Pie.
• A pie chart will be created showing revenue share of each product.

Page. 18

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Revenue (in ₹)

15,000
40,000
20,000

25,000

Product A Product B Product C Product D

2. Add Data Labels

Purpose: To display the exact revenue or percentage for each slice directly on the chart.
Steps:
• Click on the chart to select it.
• Click once more on the pie slices to select all data points.
• Right-click → Add Data Labels → Select either Value, Percentage, or Both
(depending on preference).
• You can also go to Chart Elements (+) → Check Data Labels.
Result: Each slice now shows how much revenue it represents.

Revenue (in ₹), Revenue (in ₹)

Product D,
15,000, 15%

Revenue (in
₹), Product A,
Revenue (in ₹), 40,000, 40%
Product C,
20,000, 20%
Revenue (in ₹),
Product B,
25,000, 25%

Product A Product B Product C Product D

3. Move the Legend to the Bottom

Purpose: For better presentation layout, especially when space is limited on the side.

Page. 19

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Steps:
• Click on the legend to select it.
• Right-click → Format Legend.
• Choose Bottom from the "Legend Position" options.
• Or: Select the chart → Go to Chart Elements (+) → Click the arrow next to
Legend → Select Bottom.
Result: The legend now appears below the chart.

Revenue (in ₹), Revenue (in ₹)

Product D,
15,000, 15%

Revenue (in ₹),

Product A,
Revenue (in ₹),
40,000, 40%
Product C,
20,000, 20%

Revenue (in ₹),

Product B,
25,000, 25%

Product A Product B Product C Product D

4. Explode One Slice

Purpose: To highlight one product’s revenue for emphasis.
Steps:
• Click on the specific slice (e.g., Product B) once to select all slices.
• Click again on the Product B slice to select just that one.
• Drag the slice slightly outward using your mouse.
Result: That slice is now “exploded” or separated from the rest of the pie.

Page. 20

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Revenue (in ₹)
Revenue (in ₹),
Revenue (in ₹), Product D,
Product C, 15,000, 15%
20,000, 20%

Revenue (in
₹), Product A,
40,000, 40%
Revenue (in ₹),
Product B,
25,000, 25%
Product A Product B Product C Product D

5. Format the Slice with an Image Fill

Purpose: To visually enhance the highlighted slice with a relevant or eye-catching
image.
Steps:
• Select the exploded slice.
• Right-click → Format Data Point.
• In the Format Data Point pane, select Fill → Picture or texture fill.
• Click on Insert → From File and choose an image.
• You can adjust transparency and stretch options if needed.
Result: The selected slice is now filled with an image, making it stand out visually.

Revenue (in ₹)
Revenue (in ₹),
Revenue (in ₹), Product D,
Product C, 15,000, 15%
20,000, 20%

Revenue (in
₹), Product A,
40,000, 40%
Revenue (in ₹),
Product B,
25,000, 25%
Product A Product B Product C Product D

Page. 21

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

● Final Output:
After completing all the steps:
• You’ll have a 2D Pie Chart showing revenue by product.
• Each slice will have data labels (e.g., ₹ or %).
• The legend is at the bottom.
• One slice (e.g., Product B) is exploded and filled with an image for emphasis.

Revenue (in ₹)
15,000

40,000
20,000

25,000
Product A Product B Product C Product D

⬛ Explanation of Visualization Benefits:

• Data Labels give precise insight without hovering or checking the table.
• Legend at the bottom allows more horizontal room and better alignment with
text.
• Exploded slice draws attention to a specific product’s share.
• Image fill adds visual engagement and makes the chart more appealing for
presentations or reports.

6. Create a chart with value axis data ranging from 100 to 10,000. Change the
minimum and maximum values, set major intervals, and explain how this
affects the visual scaling of the chart. Show before-and-after screenshots

Objective:
• Create a chart with data values between 100 and 10,000.
• Change the minimum, maximum, and major unit on the value axis (Y-axis).
• Analyze how these changes affect the chart's appearance and interpretation.

¶ Step 1: Prepare the Sample Dataset

#ç/¡

Page. 22

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Product Sales (₹)

A 200

B 900

C 1,200

D 3,000

E 5,500

F 10,000

⬛ Step 2: Insert the Column Chart

#
\

1. Select the entire dataset.

2. Go to Insert → Column or Bar Chart → Clustered Column.
3. A column chart will be created by default with the Y-axis auto-scaling based on
your values.

Sales (₹)
12000

10000

8000

6000

4000

2000

0
A F

껩 Step 3: Adjust the Value Axis Manually

A. Access Axis Options

• Click on the Y-axis (value axis) on the chart.
• Right-click → Format Axis.
• A side pane appears with formatting options.
B. Set the Value Axis Range

Page. 23

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

• Under Axis Options, set:

o Minimum: 0 or 100
o Maximum: 10,000
o Major Unit: Try 2,000 or 1,000 for testing
o Minor Unit: Optional

Sales (₹)
10000

8000

6000

4000

2000

0
A F

⬛ Before & After Comparison

–
³

Setting Default (Auto) Manual Scaling

Minimum 0 100

Maximum ~10,500 (auto) 10,000

Major Unit Auto (random step) 2,000

Sales (₹)
12000

10000

8000

6000

4000

2000

0
A F

C Impact of Changing Axis Scale

˛*
1. Improved Readability

Page. 24

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

• Custom intervals like every 2,000 or 1,000 make it easier for users to read and
compare bars.
• E.g., it becomes easier to see that Product D (₹3,000) is closer to Product E
(₹5,500) than to Product B (₹900).
2. Balanced Layout
• The default auto-scale may compress lower-value columns when higher values
(like ₹10,000) dominate.
• Custom scaling balances the view, giving each bar a better relative height.
3. Avoid Misleading Visuals
• If auto-scaling starts too low or uses uneven intervals, data differences might
appear exaggerated or minimized.
• A set minimum of 100 removes unnecessary space below and makes differences
clearer.
4. Professional Presentation
• Customized axis scales look cleaner and more intentional in reports or
dashboards.
• Major intervals give a uniform professional appearance, especially for
stakeholder presentations.

˙• Example Observation:
Q
Imagine two charts:
• Chart 1 (Default Axis):
o The lower sales of Product A and B appear tiny compared to Product F.
o The differences between Product B and C are hard to distinguish
visually.
• Chart 2 (Manual Axis: Min = 100, Max = 10,000, Major Unit = 2,000):
o Each bar is proportionally accurate.
o The scale helps clearly identify performance gaps between products.

8. Create a Pivot Table using a dataset of sales data including

region, product, quantity, and sales amount. Summarize the
sales amount by product and region. Include screenshots of
the Pivot Table and describe how it helps in analyzing data.

Page. 25

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Purpose:
Pivot Tables in Excel are powerful tools for quickly summarizing, analyzing, exploring,
and presenting large amounts of data. This task helps in understanding how to organize
sales data and draw valuable business insights from it.

굎 Step-by-Step Solution with Example

⬛|
–
,
−
¯

, Sample Dataset:
ˆ
˘*

Region Product Quantity Sales Amount

North A 100 ₹ 10,000
South B 150 ₹ 15,000
East C 200 ₹ 20,000
North B 120 ₹ 12,000
South A 180 ₹ 18,000
East A 140 ₹ 14,000
West B 160 ₹ 16,000
North C 130 ₹ 13,000
West C 110 ₹ 11,000
South C 170 ₹ 17,000

¶ Creating the Pivot Table in Excel

#ç/¡

◆ Step 1: Select the Dataset

• Open Excel.
• Highlight the range A1:D11 (your dataset).

◆ Step 2: Insert a Pivot Table

• Go to the Insert tab on the Ribbon.
• Click PivotTable.

Page. 26

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

• A dialog box appears. Select:

o Data range: $A$1:$D$11
o Choose: New Worksheet.
• Click OK.
•

Product (All)

Row Labels Sum of Sales Amount Sum of Quantity

East ₹ 34,000 340
North ₹ 35,000 350
South ₹ 50,000 500
West ₹ 27,000 270
Grand Total ₹ 1,46,000 1460

◆ Step 3: Set Up the Pivot Table Fields

• From the PivotTable Field List:

Page. 27

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

o Drag Region to the Rows area.

o Drag Product to the Columns area.
o Drag Sales Amount to the Values area.
▪ Ensure it's summarized by Sum.

⬛
Final Pivot Table Output:

Quantity (All)

Sum of Sal es Column

Amount Labels
Grand
Row Labels A B C Total
₹
East ₹ 14,000 20,000 ₹ 34,000
₹ ₹
North ₹ 10,000 12,000 13,000 ₹ 35,000
₹ ₹
South ₹ 18,000 15,000 17,000 ₹ 50,000
₹ ₹
West 16,000 11,000 ₹ 27,000
₹ ₹ ₹
Grand Total ₹ 42,000 43,000 61,000 1,46,000

v Analysis and Explanation:

‘
’
z

• 1. Easy Summarization:
˙Q
• You can now see the total revenue per product in each region instantly.

• 2. Region-wise Performance:
˙Q

• South is the top-performing region with ₹50,000 in sales.

• West has the lowest sales, showing it may need attention.

• 3. Product Performance:
˙Q
• Product C is the best-selling product across all regions (₹61,000 total).
• Product A and B are almost equally contributing.

Ç Why Pivot Tables Are Useful:

Feature Benefit

Drag-and-drop
Easy to rearrange data as per requirement
functionality

Page. 28

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Feature Benefit

Summarization Quick totals, averages, and counts without formulas

Filtering Add filters for product or region to drill down

Sorting Sort by sales, quantity, or region to identify trends

Convert Pivot Table to a Pivot Chart for a better visual

Dynamic Charts
presentation

⬛ Optional Enhancements:
#
/
• Add filters: You can filter by quarter, month, or category.
• Conditional formatting: Use color scale to highlight low/high sales.
• Insert Pivot Chart: Add a chart like a Stacked Column or Line Chart for visual
appeal.

9. Prepare a dataset and apply data validation rules to a

column such as: whole number between 1-100, a list of
department names, valid dates only, and time entries.
Demonstrate how invalid data is restricted and how error
messages guide the user.

Purpose:
Data Validation in Excel helps control the type of data or the values that users can enter
into a cell. It ensures data integrity, avoids entry errors, and enhances the accuracy of the
dataset.

⬛−–¯굎, Sample Dataset for Validation:

|

Employee Joining Shift Performance

Name Department
ID Date Time Score
Ravi
101 Kumar
Meera
102
Jain
Anil
103 Mehta
104 Sita Rana
Aman
105
Roy
We will now apply data validation to the following columns:

Page. 29

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

• Department – Choose from a fixed list

• Joining Date – Only allow valid dates
• Shift Time – Only allow valid time entries
• Performance Score – Whole numbers only between 1 to 100

껩 Step-by-Step Instructions to Apply Data Validation

⬛ 1. Apply Whole Number Validation (1–100) for Performance Score

Steps:
1. Select column F2:F6 (Performance Score).
2. Go to the Data tab → Click Data Validation.
3. In the dialog box:
o Allow: Whole number
o Data: Between
o Minimum: 1
o Maximum: 100

4. Go to the Error Alert tab:

o Title: “Invalid Score”
o Message: “Please enter a number between 1 and 100 only.”
5. Click OK.

9̇„ Result: If a user enters 150 or a text like “Good”, an error will appear.
•.

Page. 30

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

⬛ 2. Create a Drop-down List of Department Names

Steps:
1. Prepare a list of departments (in another sheet or nearby cells):
o HR, Finance, IT, Operations, Marketing
2. Select column C2:C6 (Department).
3. Go to Data → Data Validation.
4. Choose:
o Allow: List
o Source: HR, Finance, IT, Operations, Marketing
5. Error Alert (optional):
o Title: “Invalid Department”
o Message: “Please choose from the list provided.”

• Result: Only these departments can be selected from a dropdown; manual entry
˙9„
.
outside this list will be blocked.

Page. 31

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

⬛
3. Apply Date Validation for Joining Date

Steps:
1. Select D2:D6 (Joining Date).
2. Open Data Validation.
3. Choose:
o Allow: Date
o Data: between
o Start Date: 01/01/2020
o End Date: 31/12/2025
4. Add Error Message:
o Title: “Invalid Date”
o Message: “Enter a valid joining date between 2020 and 2025.”

9̇„ Result: Invalid dates (e.g., 15/09/2030 or text like "Today") will be rejected.
•.

⬛ 4. Apply Time Validation for Shift Time

Steps:
1. Select E2:E6 (Shift Time).
2. In Data Validation, choose:
o Allow: Time
o Data: between
o Start Time: 08:00 AM
o End Time: 06:00 PM

Page. 32

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

3. Error Alert:
o Title: “Invalid Time”
o Message: “Please enter a shift time between 8:00 AM and 6:00 PM.”

„ Result: Entries like “23:00” or “Night” are blocked.

•9˙.

뢐 Demonstration of Invalid Data and Error Alerts

Action Outcome

Error alert: “Please enter a number between 1 and 100

Typing 105 in Score
only.”

Typing "Admin" in
Error alert: “Please choose from the list provided.”
Department

Error alert: “Enter a valid joining date between 2020 and

Typing “12/12/2030” in Date
2025.”

Typing “10:30 PM” in Shift Error alert: “Please enter a shift time between 8:00 AM
Time and 6:00 PM.”
´虊
●
虇
Benefits of Using Data Validation

Feature Benefit

Prevent wrong entries Stops users from entering invalid data

Ensures consistency Only defined departments or values can be entered

Improves usability Drop-down menus make data entry fast and error-free

Adds control Gives you full control over data accuracy and formatting

Page. 33

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

10. Create a student grade entry sheet with validation rules

applied to each column (e.g., marks must be between 0–100).
Apply custom error messages and warning prompts. Test
and document various validation results.

Purpose:
A student grade entry sheet must restrict incorrect or inconsistent data input such as
invalid marks, wrong subject names, or improper student IDs. Excel’s Data Validation
helps implement these checks effectively to ensure data accuracy, consistency, and
reliability.

˘ˆ* Sample Dataset: Student Grade Entry Sheet

,

Student Student Marks

Subject Grade
ID Name Obtained
S101 Priya Sharma
S102 Amit Verma
S103 Neha Gupta
Rohit
S104
Khanna
S105 Simran Kaur
We will now apply validation rules to the Subject and Marks Obtained columns.

’"
虇● Validation Rules to Apply:
虊́

◆ 1. Subject Column – Allow Only Predefined Subjects

Allowed Subjects: English, Math, Science, History, Computer
Steps:
1. Select column C2:C6 (Subject).
2. Go to Data → Data Validation.
3. Under Settings:
o Allow: List
o Source: English, Math, Science, History, Computer
4. Click Error Alert tab:

Page. 34

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

o Style: Stop
o Title: Invalid Subject
o Message: Please select a valid subject from the dropdown list.

„ Result: Only subjects from the dropdown can be entered. Others are blocked.
9•˙.

◆ 2. Marks Obtained Column – Only Numbers Between 0 and 100

Steps:
1. Select column D2:D6 (Marks Obtained).
2. Go to Data Validation.
3. Under Settings:
o Allow: Whole number
o Data: between
o Minimum: 0
o Maximum: 100
4. Click Error Alert:
o Title: Invalid Marks
o Message: Enter a number between 0 and 100 only. No decimals or
negative values allowed.

9̇„• Result: If someone enters -10, 120, or "eighty", they’ll get a warning and the entry
.
will be blocked.

Page. 35

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

◆ 3. Student ID Validation (Optional Enhancement)

You can ensure all Student IDs follow a pattern like "S" followed by numbers.
Steps:
1. Select column A2:A6.
2. Go to Data Validation.
3. Choose:
o Allow: Custom
o Formula:
=AND(LEFT(A2,1)="S",ISNUMBER(VALUE(MID(A2,2,LEN(A2)-
1))))
4. Error Alert:
o Title: Invalid ID
o Message: Student ID must start with 'S' followed by a number, e.g., S101

Page. 36

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

# Testing & Validation Results

¡
/
ç
¶

Test Case Input Result Message Shown

Correct Subject English Accepted —

Incorrect Subject Biology Rejected "Please select a valid subject…"

Valid Marks 85 Accepted —

"Enter a number between 0 and 100

Marks > 100 105 Rejected
only…"
"Enter a number between 0 and 100
Marks with Text Seventy Rejected
only…"

Student ID as
S101 Accepted —
S101
Student ID as Rejected (if
101 "Student ID must start with 'S'…"
101 validation)

꺥 Benefits of Custom Validation & Error Messages:

˙꺣

Feature Purpose

Custom dropdown lists Avoids spelling mistakes and inconsistent entries

Numeric limits (0-100) Ensures proper grading without oversights

Page. 37

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

11. Design a spreadsheet-based mini-application such as an Expense

Tracker, Inventory Manager, or Attendance Sheet. Include formulas,
conditional formatting, data validation, and summary calculations.
Provide a brief write-up and screenshots of key features.

Objective of the Project

The primary objective of this project is to design a spreadsheet-based mini-application
for tracking personal or household expenses. This tool enables users to record daily
expenses, categorize spending, analyze monthly trends, and make informed financial
decisions using built-in formulas, data validation, conditional formatting, and summary
dashboards.

v Scope of the Project

z
‘
’

• Development of a user-friendly spreadsheet that records and categorizes

expenses
• Integration of basic data analytics using Excel formulas
• Implementation of features like conditional formatting for over-budget alerts
• Creation of a summary dashboard to monitor total and category-wise expenses
• Usable by individuals or families for monthly budget tracking

껩 Methodology

The project was executed in the following steps:

1. Requirement Gathering
Identified the key features required in a personal expense tracker:
• Expense categorization
• Budget vs. actual comparison
• Daily entry format
• Payment method tracking

Page. 38

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

2. Spreadsheet Design
Three sheets were designed in Microsoft Excel:
• Expenses
• Summary
• Settings
3. Formulas Used
In the Summary sheet:
• SUMIFS to calculate category-wise total expense
• Arithmetic calculations for budget comparison
• IF formula for budget status logic
4. Data Validation
• Drop-down menus for Category and Payment Method
• Controlled entry to reduce human error
5. Conditional Formatting
• Highlighting high expenses (> ₹100)
• Red/Green background for over/under budget statuses

ç Analysis & Features

/
¡
¶
#

◆ Expenses Sheet

Column Name Functionality

Date Date of expense

Category Selected from drop-down (Settings sheet)

Description Short detail of the expense

Amount (₹) Numeric value of expense

Payment Method Mode of payment selected from drop-down

Formula Example:
excel
CopyEdit
=SUM(D2:D1000) → Total expenses calculation

Page. 39

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

◆ Summary Sheet

Column Formula/Logic

Monthly Budget Input from user

Total Spent =SUMIFS(Expenses!D:D, Expenses!B:B, A2)

Difference =B2 - C2

Status =IF(D2<0, "Over Budget", "Under Budget")

Conditional Formatting Example:

• Over Budget: Cell background = Red
• Under Budget: Cell background = Green

z Findings
v
‘
’
• Users can quickly visualize their monthly expenditure pattern.
• Budget limits act as a financial control mechanism.
• Easy to customize with new categories or payment methods.
• Visual feedback through conditional formatting enhances usability.

˙굚`
•
ˆ Screenshots / Sample Layouts (to be included in report)

1. Expenses Sheet Sample

Page. 40

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

2. Summary Sheet Dashboard

3. Settings Sheet with Categories

Page. 41

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

12. Design a chart using financial data (revenue, expense, profit) and
change the display units of the value axis to thousands, then millions.
Explain how display units affect interpretation, especially in presentations.
Include formatted screenshots.

Objective
To visualize key financial metrics (Revenue, Expense, Profit) using an Excel chart and
evaluate the impact of changing display units (Thousands vs. Millions) on the clarity and
interpretation of data, particularly in business presentations.

ç#¶ Step 1: Create Financial Data Table

/¡
Here's the sample data used:

Year Revenue (₹) Expenses (₹) Profit (₹)

2021 1,50,00,000 90,00,000 60,00,000

2022 1,85,00,000 1,10,00,000 75,00,000

2023 2,20,00,000 1,25,00,000 95,00,000

2024 2,50,00,000 1,40,00,000 1,10,00,000

\ Step 2: Insert a Column Chart

z’
‘
1. Select Data Table
2. Go to Insert > Column Chart > Clustered Column
3. Format chart title: "Financial Performance (2021-2024)"

Page. 42

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Financial Performance (2021-2024)

30000000

25000000

20000000

15000000

10000000

5000000

0
Year Revenue (₹) Expenses (₹) (₹)

Series1 Series2 Series3 Series4

×k굯군 Step 3: Change Display Units of Value Axis

˙´ 구.́

4. ⬛#
\ A. Display Units: Thousands
• Right-click Y-axis > Format Axis
• Under Display Units, select Thousands
• Axis labels now show: 5,000; 10,000; 15,000 instead of full numbers

Financial Performance (2021-2024)

30000

25000

20000

15000

10000

5000

0
Year Revenue (₹) Expenses (₹) (₹)

Series1 Series2 Series3 Series4

● Interpretation:
• Easier to read than full numbers
• Good for internal reports where granularity matters
• Still shows detail, but less cluttered

Page. 43

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

5. #
⬛/ B. Display Units: Millions
• Change Display Units to Millions
• Now axis labels show: 5; 10; 15 (interpreted as ₹5M, ₹10M...)

Financial Performance (2021-2024)

30
Millions

25

20

15

10

0
Year Revenue (₹) Expenses (₹) (₹)

Series1 Series2 Series3 Series4

● Interpretation:
• Ideal for executive presentations
• Simplifies comprehension of large-scale figures
• Cleaner, more professional look

" Impact of Display Units on Interpretation

´●
虇’虊

Unit Use Case Pros Cons

Internal reports, Slightly cluttered

Thousands More detail
departmental use visually

Executive summary, Clean, high-level Less precise (rounded

Millions
presentations overview view)

Page. 44

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

13.Create a macro to automate a common task such as formatting a report,

inserting a summary row, or generating charts. Use the macro recorder and
edit the macro code if needed. Describe how the macro improves productivity.

Objective
To create and implement a macro that automatically inserts a summary row at the bottom
of a dataset, calculates totals, and applies professional formatting. This reduces repetitive
manual work and ensures consistency.

o Use Case Scenario

#
«

You are maintaining monthly sales or expense reports. At the end of each month, you
must:
• Scroll to the last row
• Insert a "Total" label
• Sum numeric columns
• Format the summary row (bold, border, etc.)
This repetitive process can be automated using a macro.

z Steps Performed Using the Macro Recorder

\‘
’
1. Selected the data table
2. Navigated to the last row
3. Entered a "Total" label in the first column
4. Used =SUM() to calculate totals for columns
5. Applied bold, background color, and borders
껩 VBA Macro Code

vba
CopyEdit
Sub InsertSummaryRow()
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim i As Integer
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Page. 45

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

' Insert "Total" label

ws.Cells(lastRow + 1, 1).Value = "Total"
ws.Cells(lastRow + 1, 1).Font.Bold = True

' Apply SUM formula for all numeric columns

For i = 2 To lastCol
ws.Cells(lastRow + 1, i).Formula = "=SUM(" & ws.Cells(2, i).Address & ":" &
ws.Cells(lastRow, i).Address & ")"
ws.Cells(lastRow + 1, i).Font.Bold = True
Next i

' Format the summary row

With ws.Range(ws.Cells(lastRow + 1, 1), ws.Cells(lastRow + 1, lastCol))
.Interior.Color = RGB(220, 230, 241) ' Light blue fill
.Borders.LineStyle = xlContinuous
End With

MsgBox "Summary Row Inserted Successfully!", vbInformation

End Sub

J How to Use This Macro

)
1. Press ALT + F11 to open the VBA Editor
2. Insert a new module (Insert > Module)
3. Paste the code above
4. Close the editor
5. Run the macro via ALT + F8 > InsertSummaryRow > Run

Page. 46

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

⬛ Optional: Assign the macro to a button on the sheet for easy access

s7•’ How This Macro Improves Productivity

˙.̧

Manual Process Time Required With Macro

Scroll to last row 15 seconds ⬛ Automated

Insert totals with formulas 2–3 minutes ⬛ Automated

Apply formatting (bold, border, fill) 1–2 minutes ⬛ Automated

Risk of error in formula ranges High ⬛

Minimized

Total time saved per report: ~5 minutes

Reports/month: 10+ → Time saved: ~1 hour/month

11. Build a complete data entry and analysis template for a small
business. Include Pivot Tables, validation, scenarios, and at least
one macro. Describe each component in detail, demonstrate its use,
and attach screenshots of the interface and results.

Objective:
To build an Excel-based system that helps a small business manage daily sales data,
analyze performance, and make informed decisions using automated tools like pivot
tables, macros, and scenario analysis.

Page. 47

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

* Workbook Structure:
˘,̂

Sheet Name Purpose

Data_Entry Daily entry of sales transactions

Pivot_Analysis Dynamic report of sales by category

Inventory Stock tracking and reorder alerts

Scenario_Analysis Forecast with Scenario Manager

Macro_Tool Button to auto-format & summarize data

Settings Product list & validation sources

1. Data Entry Sheet (Data_Entry)

˙x Features:
+

• Data Validation:
o Product and Category fields use dropdowns sourced from the Settings
sheet.
o Prevents typos and inconsistent entries.
excel
=D2 * E2 // Calculates total amount

2. Pivot Table Sheet (Pivot_Analysis)

• Purpose:
Analyze monthly sales by product and category.
Pivot Table Fields:
• Rows: Category → Product
• Values: Sum of Total Amount
• Filter: Month or Date Range

Page. 48

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Add Pivot Chart (e.g., Column Chart) to visualize performance.

Sum of Sum of Unit Sum of Total
Row Labels Quantity Price Amount
Apparel 5 200 1000
01-04-
2025 5 200 1000
Grand Total 5 200 1000

uJ́◆¶, 3. Inventory Sheet (Inventory)

Product Category Stock Qty Reorder Level Status

T-Shirt Apparel 10 15 Reorder Now '.

• * Conditional Formatting:
˛C

excel
CopyEdit

=IF(C2 < D2, "Reorder Now '

.", "Stock OK ⬛
")
k구 4. Scenario Analysis Sheet (Scenario_Analysis)
˙군´
굯×.

• Q
•
˙ Use: What-if analysis using Scenario Manager.

Scenario Unit Price Quantity Sold Projected Revenue

Best Case 300 1000 =B2*C2

Worst Case 180 600 =B3*C3

Expected Case 250 800 =B4*C4

• + Use:
• Go to Data > What-If Analysis > Scenario Manager
• Add 3 scenarios: Best, Worst, Expected
• Output: Projected Revenue

Auto-format the Data_Entry sheet and add a summary row for monthly sales.
5. Settings Sheet

Page. 49

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

Product List Category List

T-Shirt Apparel

Sneakers Footwear

Hat Accessories

• + Use:
• Reference these lists in Data Validation in Data_Entry sheet.

12. Create at least three different named scenarios (e.g., Best Case, Worst
Case, Average Case) for a budgeting sheet.
Show how to switch between scenarios, edit, and delete them. Create a
Scenario Summary Report and explain the results.

Objective:
To evaluate different financial planning possibilities by creating and analyzing three
scenarios in a budgeting worksheet using Excel’s Scenario Manager.

Step 1: Create a Budgeting Table

꼑
● Step 2: Define Named Scenarios
˙*

Open Excel and follow these steps:

Page. 50

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

1. Go to: Data → What-If Analysis → Scenario Manager

2. Click Add
3. Enter Scenario Name: Best Case
o Changing cells: B2:B5
o Values:
▪ Revenue: 120000
▪ Marketing: 10000
▪ Operational: 25000
▪ Salaries: 30000

4. Repeat for:
o Average Case
▪ Revenue: 100000
▪ Marketing: 15000
▪ Operational: 30000
▪ Salaries: 35000

Page. 51

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

o Worst Case
▪ Revenue: 80000
▪ Marketing: 18000
▪ Operational: 35000
▪ Salaries: 40000

깣⬛ Step 3: Switch Between Scenarios

’

1. Go to Data > What-If Analysis > Scenario Manager

2. Select a scenario (e.g., "Worst Case")
3. Click Show
− The budgeting table updates with the selected values.
–
距
跚

,‘`¸Step 4: Edit or Delete Scenarios

Page. 52

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

• Edit:
o In Scenario Manager, select a scenario and click Edit.
o Change values or range if needed.
• Delete:
o Select a scenario and click Delete.

#⬛/ Step 5: Create a Scenario Summary Report

1. Open Scenario Manager → Click Summary

2. Select the result cell (B6 – the Profit cell)
3. Choose Scenario Summary
4. Excel will generate a new sheet with a comparison table.

⬛굎− Sample Summary Table:

5. – |
,̄

Changing B2 B3 B4 B5 Result Cell

Cells → (Revenue) (Marketing) (Operations) (Salaries) (Profit)

Best Case ₹120,000 ₹10,000 ₹25,000 ₹30,000 ₹55,000

Average Case ₹100,000 ₹15,000 ₹30,000 ₹35,000 ₹20,000

Worst Case ₹80,000 ₹18,000 ₹35,000 ₹40,000 -₹13,000

⬛ Interpretation of Results

Scenario Outcome Implication

Best Case ₹55,000 Profit Strong revenue and cost control

Average Case ₹20,000 Profit Moderate performance, sustainable outcome

Worst Case -₹13,000 Loss Operational risks and rising costs

Appendix

1. Monthly Sales Data Analysis

o Use a spreadsheet to track sales for four products over several months.
Create and compare Column, Bar, Line, and Pie Charts.
2. Student Marks Performance Visualization

Page. 53

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

o Use student marks from five subjects to create XY Scatter and Area
Charts. Highlight how each chart type shows trends.
3. Combined Chart for Daily Temperature Readings
o Prepare temperature data for two cities over 10 days. Use a Combined
Chart (Column + Line) to compare trends.
4. Surface Chart for Terrain or Temperature Variation
o Use elevation or temperature data to create a Surface Chart,
demonstrating the 3D view of the data.
5. Bubble Chart for Dataset Analysis
o Visualize a dataset (e.g., population) using a Bubble Chart and explain
its benefits over a regular scatter plot.
6. Product-Wise Revenue Share with Pie Chart
o Create a Pie Chart showing product-wise revenue share, with an
explanation of its interpretation.
7. Customizing Value Axis Range in Charts
o Change the axis range, intervals, and minimum/maximum values to see
how this affects chart scaling and interpretation.
8. Pivot Table for Sales Data Analysis
o Create a Pivot Table summarizing sales by region and product, and
explain its usefulness for data analysis.
9. Data Validation Rules Application
o Apply data validation to restrict entries (e.g., whole numbers,
department names) and show how invalid data is restricted.
10. Student Grade Entry Sheet with Validation
• Design a grade entry sheet with custom validation rules and error messages to
ensure valid inputs.
11. Spreadsheet-Based Mini-Application Design
• Build an app like an Expense Tracker with formulas, conditional formatting,
and macros, and explain its components.
12. Financial Data Analysis with Display Units
• Design a financial chart and adjust the value axis display to show units in
thousands or millions, demonstrating how this changes data interpretation.
13. Creating a Macro to Automate Tasks
• Record and edit a macro to automate formatting tasks, and describe how it
improves productivity.

Page. 54

Downloaded by chanchal ytn ([email protected])

 lOMoARcPSD|44729796

14. Data Entry and Analysis Template for a Small Business

• Build a comprehensive template with Pivot Tables, scenarios, validation, and
macros, and explain each component.
15. Budgeting Scenarios and Scenario Summary Report
• Create and switch between scenarios in a budgeting sheet, and generate a
Scenario Summary Report for analysis.

Page. 55

Downloaded by chanchal ytn ([email protected])