sity Of CalCU

er tt
v a
U ni

Stream : [Link] Honours (CMSM)

Registration No. : 213-1211-0219-25
CU Roll No. : 253213-11-0015
Subject Code : CMSM
Course Code :SEC-1
Course Name: Data Visualization Using
Spreadsheet
 inDeX
Serial Experimental Page Teacher’s
Topic
No. Date No. Signature

Create a personal budget spreadsheet that tracks

income, expenses, and savings over a specified period.
1 Use formulas and functions to calculate totals, 21.11.2025 01-03
percentages, and remaining balances.

A dataset containing sales data for a company to be

provided. A spreadsheet to be created that calculates
monthly sales totals, identifies top-selling products,
2 and visualizes sales trends using line charts or bar 03.12.2025 04-06
graphs. Use conditional formatting to highlight
exceptional sales performances.

Design a grade book spreadsheet that calculates

students' final grades based on assignments, exams,
and participation. Incorporate weighted grading
systems, formulas for calculating averages, and
3 conditional formatting to indicate performance levels.
15.12.2025 07-09
Generate reports to track individual student progress.

Create a spreadsheet that tracks inventory for a

hypothetical business. Include columns for item names,
quantities, prices, and total values. Use formulas to
4 automatically update inventory totals, generate alerts 08.01.2026 10-12
for low stock, and create visualizations to represent
inventory levels over time.

Loan parameters, such as principal amount, interest

rate, and loan term to be provided. Create a spreadsheet
that calculates monthly loan payments, remaining
5 balances, and interest paid over time using appropriate 20.01.2026 13-15
formulas. Create a chart to visualize the loan's
repayment schedule.

Dataset to be provided which will allow various data

analysis tasks using spreadsheets. Calculation of
summary statistics, sorting and filtering data, creating
6 pivot tables for deeper insights, and generation of 04.02.2026 16-18
charts or graphs to visualize patterns or trends within
the data.

A dataset to be selected (e.g., stock prices, weather

data, population growth, etc) and create line charts or
area charts to visualize trends over time. Students
7 should choose appropriate chart types, label axes, and 18.02.2026 19-20
add titles and legends to make the visualization clear
and informative.
 A dataset containing information about different
products or variables (e.g., sales data, customer
satisfaction ratings) to be provided and following to be
done; create bar charts or column charts to compare the
8 performance or rankings of the items. Use color, data
27.02.2026 21-22
labels, and chart elements to enhance the visual
comparison.

A dataset containing time-series data for multiple

variables (e.g., monthly sales data for different
products) to be provided and the following task to be
9 performed; to create a combo chart with lines and 03.03.2026 23-25
columns to compare the trends of the variables and
identify any relationships or patterns.

To create a unique visualization using advanced

spreadsheet features and tools. For example, an
experiment with sparklines, radar charts, or treemaps to
10 represent specific types of data or explore innovative
18.03.2026 26-28
ways to visualize information.
1

1. Create a personal budget spreadsheet that tracks income, expenses, and savings over
a specified period. Use formulas and functions to calculate totals, percentages, and
remaining balances.

Ans:
Main Table:

Table Procedure and Formulas:

(a) Total Income Calculation

Total Income Calculation: To find the total monthly income, summed up all income sources
( Salary, Bonus, Bank Int., and Others ).

• Formula: =SUM(B4:4) (Example for January)

(b) Total Expenses Calculation

2

This column calculates the sum of all expense categories (Electricity, Grocery, Rent, Medical, and
Ent.)

• Formula: =SUM(G4:K4) (Example for January)

(c) Saving Calculation

To find the monthly savings, subtracted the Total Expenses from the Total Income

• Formula: =F4-L4(Example for January)

(d) Percentage Calculation

This calculates the percentage of income that was saved.

• Formula: =M4/F4 (clicked on the “%” icon in the google sheets toolbar) (Example
for January)

Graph Procedure:
3

1. Data Selection : The Month column (A3:A15), the Total Income column (F3:F15), and the Total
Expenses column (L3:L15) were selected from the spreadsheet.

2. Chart Insertion: The Insert menu was accessed, and the Chart option was clicked to generate the
visualization.

3. Selecting Chart Type: From the Chart Editor panel, the Column Chart was selected as the chart
type to facilitate a clear comparison between monthly income and expenses.

4. Setting Axis and Title: The Chart Title was updated to "Personal Budget Chart." Additionally, the
X-axis was labeled as "Months" and the Y-axis was labeled as "Amount."

5. Final Customization: Legend positions and bar colours were adjusted to ensure a clear distinction
between Total Income and Total Expenses.
4

2. A dataset containing sales data for a company to be provided. A spreadsheet to be

created that calculates monthly sales totals, identifies top-selling products, and
visualizes sales trends using line charts or bar graphs. Use conditional formatting to
highlight exceptional sales performances.

Ans:
Main Table:

Table Procedure and Formulas:

(a) Total Revenue Calculation per Product

The total revenue for each product was calculated by multiplying the Quantity Sold by the Unit Price.
5

• Formula: =C3*D3

(b) Monthly Total Revenue

To find the total sales for each month (e.g., January), the revenues of all individual products within
that month were summed up.

• Formula:=SUM(E3:E7) (Example for January)

(c) Grand Total Sales

The total revenue for the entire six-month period was calculated by summing all monthly revenues.

• Formula:=SUM(F3:F38)

(d) Identifying Top Performance (Green Highlights): To highlight exceptional sales performance
(Top-selling products), Conditional Formatting was applied.
6

* Rule: The range (E3:E38) was selected. From the Format menu, Conditional Formatting was
chosen. A rule was set to highlight cells with a green background where the revenue exceeded a
specific threshold (e.g., "Greater than 25,000").

Graph Procedure:

(1) Data Selection: The Month column (A2:A38) and the Monthly Total Revenue column (F2:F38)
were selected from the spreadsheet.

(2) Chart Insertion: The Insert menu was accessed, and the Chart option was clicked to generate the
visualization.

(3) Selecting Chart Type: From the Chart Editor panel, a Line Chart was selected as the chart type
to represent the sales trends clearly over the six-month period.

(4) Setting Axis and Title: The Chart Title was updated to “Monthly Sales Trend – 2025”. The X-
axis was labeled as “Months” and the Y-axis was labeled as “Revenue (in INR)”.

(5) Final Formatting: Data points and labels were enabled to show the exact revenue values on the
line chart for better clarity.
7

3. Design a grade book spreadsheet that calculates students' final grades based on
assignments, exams, and participation. Incorporate weighted grading systems, formulas
for calculating averages, and conditional formatting to indicate performance levels.
Generate reports to track individual student progress.

Ans:
Main Table:

Table Procedure and Formulas:

(a) Final Score Calculation

Students' final scores were calculated based on a weighted system (Assignments, Exams, and
Participation).

• Formula: =(E3/B3*20) + (F3/C3*60) + (G3/D3*20) (Example for A3 cell)

(b) Average Score Calculation

8

The simple average of the marks obtained in three categories was calculated in this column.

• Formula: =AVERAGE(E3:G3) (Example for A3 cell)

(c) Grade Calculation

The final grade (A+, A, B+, etc.) was automatically assigned using nested IF functions based on the
Final Score.

• Formula: =IF(H3>=85,"A+", IF(H3>=70,"A", IF(H3>=60,"B+", "C"))) (Example for

A3 cell)

(d) Status Determination

The performance status (Excellent, Good, Satisfactory) was generated based on the Grade obtained.

• Formula:
=IF(H3>=85,"Excelent",IF(H3>=70,"Good",IF(H3>=50,"Satisfactory",IF(H3<=50,"
Need Improvement",)))) (Example for A3 cell)

(e) Class average

The overall average score of the entire class was calculated at the bottom of the table.

• Formula: =AVERAGE(H3:H12) (Example for A3 cell)

9

(f) Conditional Formatting

Performance levels were visually indicated using Conditional Formatting.

*Rule: To visually highlight the performance levels, the Final Score column was selected. From the
Format menu, the Conditional Formatting option was accessed to apply a specific rule. A condition
was set as "Greater than or equal to" with the value of 80. Following this rule, all cells containing a
score of 80 or above were automatically formatted with a green background color to distinguish the
top-performing students from the rest of the dataset.

Individual Report Card:

An automated report card was created to track individual student progress. When a student's name is
selected, their result is fetched using the VLOOKUP function.

(1) Select Student Name (Formula):=A3(Example for A3 cell) (2) Final Score(Formula):=VLOOKUP(B17,A3:K12,8,FALSE)

(Example for A3 cell)

(3) Grade (Formula):=VLOOKUP(B17,A2:K12,10,FALSE) (4) Status (Formula):=VLOOKUP(B17,A2:K12,11,FALSE)

(Example for A3 cell) (Example for A3 cell)

10

4. Create a spreadsheet that tracks inventory for a hypothetical business. Include

columns for item names, quantities, prices, and total values. Use formulas to
automatically update inventory totals, generate alerts for low stock, and create
visualizations to represent inventory levels over time.

Ans:
Main Table:

Table Procedure and Formulas:

(a) Total Inventory Quantity

To determine the overall stock count for each month, the quantities of all items (Laptop, Mouse,
Keyboard, Monitor) were summed up.

• Formula: =SUM(C3:C6) (Example for January)

(b) Total Value Calculation

11

The total value for each individual item was calculated by multiplying the Quantity with its Price.

• Formula: =C3 * E3 (Example for F3 cell)

(c) Monthly Total Value

The cumulative financial value of the inventory for each month was calculated using the sum of the
total values of all items.

• Formula: =SUM(F3:F6) (Example for January)

(d) Low Stock Alert (Status)

To automatically monitor stock levels, an IF function was used in the Status column. An alert was
generated if the quantity fell below a certain threshold.

• Formula: =IF(C3>=10,"In Stock",IF(C3<=10,"Low Stock")) (Example for H3 cell)

(e) Grand Total Inventory Value

12

The final total value of the inventory across all months was calculated at the bottom of the sheet.

• Formula: =SUM(G3:G22)

(f) Conditional Formatting for Alerts

A specific formatting rule was applied to the Status column to highlight "Low Stock" in a different
color. The column was selected, and through the Conditional Formatting menu, the "Text is exactly"
rule was used with the value "Low Stock".

Graph Procedure:

(1) Data Selection: The Months column, the Total Inventory Quantity column, and the Monthly Total
Value column were selected to create a comparative visualization.

(2) Chart Insertion: From the Insert menu, the Chart option was selected to generate the visual
representation.

(3) Selecting Combo Chart: A Combo Chart was chosen from the Chart Editor to represent two
different scales—bars for "Total Inventory Quantity" and a line for "Monthly Total Value."

(4) Customization: The chart was titled "Inventory Levels Over Time (Jan-May)." The X-axis was
labeled as "Months," while the dual Y-axes were set to represent "Monthly Total Value" and "Total
Inventory Quantity."

(5) Final Adjustments: Data labels were added to the bars and the line to ensure that exact quantities
and values are visible at a glance.
13

5. Loan parameters, such as principal amount, interest rate, and loan term to be
provided. Create a spreadsheet that calculates monthly loan payments, remaining
balances, and interest paid over time using appropriate formulas. Create a chart to
visualize the loan's repayment schedule.

Ans:
Main Table:

Table Procedure and Formulas:

(a) Monthly Loan Payment (EMI)

The fixed monthly payment was calculated using the PMT function based on the annual interest rate
(10%), the loan term (12 months), and the initial principal amount (1,00,000).

• Formula: =PMT(10%/12, 12, -100000) (Example for january)

(b) Interest Paid Calculation

14

The interest for each month was determined by multiplying the Principal Amount of that month by the
monthly interest rate.

• Formula:=B4*(10%/12) (Example for January)

(c) Principal Paid Calculation

The amount of principal repaid each month was calculated by subtracting the Interest Paid from the
total Monthly Loan Payment.

• Formula: =C4-D4 (Example for January)

(d) Remaining Balance

To find the outstanding balance at the end of the month, the Principal Paid for that month was
subtracted from the current Principal Amount.

• Formula: =B4 - E4 (Example for January)

(e) Principal Amount Setup (February onwards)

15

For the month of February and all subsequent months, the opening Principal Amount was updated by
linking it to the Remaining Balance of the previous month.

*Action: The value from cell F4 (January's Remaining Balance) was copied/linked
to cell B5 (February's Principal Amount).

Graph Procedure:

(1) Data Selection: The Month column, the Principal Paid column, and the Interest Paid column were
selected for the visualization.

(2) Chart Insertion: The Insert menu was accessed, and the Chart option was selected.

(3) Selecting Stacked Column Chart: From the Chart Editor, a Stacked Column Chart was chosen to
show how the total monthly payment is split between principal and interest over the 12-month period.

(4) Customization: The chart was titled "Monthly Repayment Breakdown: Principal vs Interest." The
X-axis was labeled as "Months" and the Y-axis as "Amount."

(5) Final Formatting: Different colours (Green for Principal and Red for Interest) were used to make
the repayment schedule clearly distinguishable.
16

6. Dataset to be provided which will allow various data analysis tasks using
spreadsheets. Calculation of summary statistics, sorting and filtering data, creating
pivot tables for deeper insights, and generation of charts or graphs to visualize patterns
or trends within the data.

Ans:
Main Table: Pivot Table:

Table Procedure and Formulas:

(a) Category Classification

The Category column was automated using a logical formula to classify each product into its
respective group (e.g., Electronics or Accessories) based on the product name.

• Formula:
=IF(OR(B3="Laptop",B3="Monitor"),"Electronics",IF(OR(B3="Mouse",B3="Keyb
oard"),"Accessorice")) (Example for B3 cell)

(b) Revenue Calculation

17

The revenue for each sales transaction was calculated by multiplying the Units Sold by the Unit Price.

• Formula: =D3*E3 (Example for 01/01/2025)

(c) Summary Statistics Table (Creating the small table)

A separate table was created to calculate overall summary statistics.

*Action: The small summary statistics table (visible above the Pie Chart) was constructed
with specific functions to derive overall insights:

(i) Total Revenue:

• Formula:=SUM(F3:F12)

(ii) Average Units Sold:

• Formula:=AVERAGE(D3:D12)

(iii) Highest Sale:

• Formula:=MAX(F3:F12)

(iv) Total Transactions:

• Formula:=COUNT(A3:A12)

(d) Sorting and Filtering Setup

For data organization, sorting and filtering capabilities were activated.

*Action: The entire main dataset range was selected, and from the Data menu, the
"Create a filter" option was clicked. This added filter dropdown arrows to the header row for
easy sorting and filtering by Product, Category, or other parameters.
18

Pivot Table Procedure:

1. Pivot Table Creation: For deeper insights (Total Revenue per Product per Category), a Pivot Table
was generated.

*Process: The entire main dataset range was selected. From the Insert menu, the "Pivot Table"
option was clicked, and a new sheet was selected for the pivot table.

2. Pivot Table Configuration: The Pivot Table was configured with the following parameters:

• Rows: Set to Product.

• Columns: Set to Category.

• Values: Set to Revenue (using the SUM function).

Graph Procedure:

1. Data Selection: The Product column and the Revenue column from the main spreadsheet were
selected as the source for the chart.

2. Chart Insertion: From the Insert menu, the Chart option was clicked to create the visualization.

3. Selecting Chart Type: In the Chart Editor, the Pie Chart was chosen from the available chart types.

4. Setting Title: The chart was titled "Revenue Distribution by Product" through the customize
settings.

5. Applying Data Labels: Data Labels were enabled to show the Percentage for each slice.

6. Positioning Labels: These labels were placed outside the slices with Leader Lines pointing to each
specific part of the pie.

7. Slices Identification: Each slice was automatically assigned a distinct color to differentiate
between the products.

[Link] Placement: The chart was resized and moved to its designated position on the spreadsheet.
19

7. A dataset to be selected (e.g., stock prices, weather data, population growth, etc) and
create line charts or area charts to visualize trends over time. Students should choose
appropriate chart types, label axes, and add titles and legends to make the visualization
clear and informative.

Ans:
Main Table:

Table Procedure:

(a) Data Selection: A dataset representing the "Monthly Maximum and Minimum Temperature Data
for Kolkata, India (2025)" was selected for this task.

(b) Structuring the Table: A spreadsheet table was designed with three primary columns: Month,
Max Temp (°C), and Min Temp (°C).

(c) Data Entry: Temperature values for all twelve months, from January to December, were manually
entered into the respective cells to track the seasonal variation.

(d) Header Formatting: The header row was highlighted with a distinct background color, and the
text was bolded to differentiate it from the data rows.

(e) Borders and Alignment: Proper borders were applied to the entire table, and the data was centre-
aligned to ensure a professional and organized look.

Graph Procedure:

1. Data Selection: The entire range of the table, including the Month, Max Temp, and Min Temp
columns, was selected.
20

2. Chart Insertion: The Insert menu was accessed, and the Chart option was clicked to generate the
visualization.

3. Selecting Chart Type: From the Chart Editor, a Line Chart was selected to clearly illustrate the
temperature trends over the twelve-month period.

4. Setting Chart Title: The chart was titled "Monthly Temperature Variation in Kolkata (2025)" to
accurately describe the data.

5. Labelling Axes: The X-axis was labeled as "Months of the Year", and the Y-axis was labeled as
"Temperature in Celsius (°C)".

6. Configuring the Legend: A Legend was positioned at the top-right corner, where a red dot
represents Max Temp (°C) and a blue dot represents Min Temp (°C).

7. Data Points: Markers (dots) were enabled on each line to pinpoint the exact temperature values for
each month.
21

8. A dataset containing information about different products or variables (e.g., sales

data, customer satisfaction ratings) to be provided and following to be done; create bar
charts or column charts to compare the performance or rankings of the items. Use color,
data labels, and chart elements to enhance the visual comparison.

Ans:
Main Table:

Table Procedure and Formulas:

(a) Total Sales Calculation

The total sales for each product were calculated by multiplying the Units Sold by the Unit Price in Rs.

• Formula: =B3*C3 (Example for Laptop)

(b) Customer Feedback Automation

The Feedback column was automated using a logical formula based on the Customer Rating.

• Formula: =IF(E3=5,"Excellent",IF(E3>=4,"Very
Good",IF(E3>=3,"Good",IF(E3>=2,"Poor",IF(E3>=1,"Very Poor","Worst")))))
(Example for Laptop)
22

(c) Table Formatting: The header was styled with a dark blue background and white text for better
visibility. Proper cell borders and currency formatting (Rs.) were applied to the sales columns.

Graph Procedure:

1. Data Selection: The Product Name column and the Total Sales in Rs. column were selected as the
primary data for the chart.

2. Chart Insertion: From the Insert menu, the Chart option was selected.

3. Selecting Chart Type: In the Chart Editor, a Column Chart (Vertical Bar Chart) was chosen to
compare the sales performance across different products.

4. Labelling Axes: The X-axis was labeled as "Product Names" and the Y-axis was labeled as "Total
Sales in Rs."

5. Adding Chart Title: The chart was titled "Comparison of Product Sales Performance" using the
customize settings.

6. Customizing Series Labels: The names of the products on the X-axis were tilted for better
readability, and the Total Sales in Rs. legend was placed at the top.

7. Data Labels: Data labels were enabled on top of each bar to show the exact sales figures (e.g.,
2,250,000 for Laptop) directly.

8. Analysis: Each bar was color-coded in blue, allowing for a quick visual identification of the
highest-performing product (Smartphone) and the lowest-performing product (Mouse).
23

9. A dataset containing time-series data for multiple variables (e.g., monthly sales data
for different products) to be provided and the following task to be performed; to create
a combo chart with lines and columns to compare the trends of the variables and
identify any relationships or patterns.

Ans:
Main Table:

Table Procedure & Formulas:

(a) Total Quantity Sold (Monthly)

The total number of units sold across all products for a particular month was calculated using the
SUM function.

• Formula:=SUM(C3:C7) (Example for January)

(b) Total Revenue(each product)

24

The revenue for each specific product was calculated by multiplying the Quantity Sold by its Unit
Price.

• Formula:=C3*E3 (Example for January - Laptop)

(c) Monthly Total Revenue

To find the total income generated in a month, the revenue from all five products was added together.

• Formula:=SUM(F3:F7) (Example for January)

(d) Table Formatting: The data was organized by grouping rows under each Month. Borders were
applied to distinguish between different product categories, and the Total Revenue column was
formatted with currency symbols.

Graph Procedure:

1. Data Selection: The Month, Total Quantity Sold, and Monthly Total Revenue columns were
selected from the dataset to provide the foundation for the visual summary.

2. Chart Insertion: The Insert menu was accessed from the top navigation bar, and the Chart option
was clicked to generate the visualization.

3. Selecting Combo Chart: From the Chart Editor panel, a Combo Chart was chosen. This allowed
for the simultaneous representation of two different metrics: Total Quantity Sold as a Column Chart
and Monthly Total Revenue as a Line Chart.
25

4. Setting Title and Axis Labels: The chart was titled "Monthly Sales Performance and Revenue
Trend (January - May)". The left Y-axis was configured for Revenue and the right Y-axis was set for
Quantity.

5. Configuring Legend: A Legend was positioned at the top of the chart area, clearly indicating that
the blue bars represent the "Total Quantity Sold" and the red line represents the "Monthly Total
Revenue."

6. Enabling Data Labels: To ensure precision, Data Labels were enabled for both the column and
line series to display exact numerical values directly on the chart.

7. Customizing Series Color: The colours of the bars and the line were customized (e.g., Blue for
bars and Red for the line) to make the distinction between quantity and revenue more prominent.

8. Final Formatting: The chart was resized and aligned next to the main table on the spreadsheet to
complete the financial summary report.
26

10. To create a unique visualization using advanced spreadsheet features and tools. For
example, an experiment with sparklines, radar charts, or treemaps to represent specific
types of data or explore innovative ways to visualize information.

Ans:
Main Table:

Table Procedure & Formulas:

(a) Total Sales Calculation

The cumulative sales for the entire six-month period (January to June) for each product were
calculated using the SUM function.

• Formula:=SUM(C4:H4) (Example for Ball Pen)

(b) Sales Trend Visualization

27

Small Sparklines were inserted within individual cells in the Sales Trend column to visualize the
fluctuation in sales across the months.

• Formula:=SPARKLINE(C4:H4) (Example for Ball Pen)

(c) Feedback Automation

A logical Nested IF formula was applied to the Feedback column to automatically categorize products
based on their Quality Rating (1-5).

• Formula: =IF(K4=5,"Excellent",IF(K4>=4,"Very
Good",IF(K4>=3,"Good",IF(K4>=2,"Poor",IF(K4>=1,"Very Poor","Worst")))))

(d) Main Inventory Database: A comprehensive dataset was constructed containing details such as
Product Name, Category, monthly sales (Jan-Jun), Total Sales, and Quality Ratings.

(e) Hierarchy Table Construction: A secondary Product and Category Hierarchy Table was created
to organize Total Orders by specific categories (Stationery, Household, Accessories) for the dashboard
visualization.

Graph Procedure A: Treemap Chart (Total Orders)

1. Data Selection: The Category, Product Name, and Total Orders columns from the hierarchy table
were highlighted to serve as the data source.

2. Accessing Chart Menu: The Insert menu was accessed from the top navigation bar, and the Chart
option was clicked.

3. Selecting Treemap Type: From the Chart Editor, the Treemap chart was specifically selected to
represent the hierarchical data.
28

4. Categorical Grouping: The chart was configured to automatically group products into large
rectangular blocks based on their parent categories (e.g., Stationery, Household).

5. Visual Representation: The size of each block was set to represent the volume of Total Orders,
allowing for a quick comparison of product performance.

6. Labelling: Data labels were added to each rectangle to display the product name clearly within its
respective category block.

Graph Procedure B: Radar Chart (Performance Comparison)

1. Data Selection: The Product Names and their corresponding monthly sales or performance
metrics were selected.

2. Chart Insertion: From the Insert menu, the Chart option was selected to open the editor.
3. Radar Chart Selection: In the setup tab of the Chart Editor, the Radar Chart (Spider
Chart) was chosen to visualize the data distribution from a central point.

4. Axis Mapping: Multiple axes were created, each representing a different data point (e.g.,
monthly sales or ratings), allowing for a multi-dimensional comparison.

5. Configuring Legend: A Legend was positioned at the top to identify different products
based on the color of the radar lines.

6. Customizing Series: Different colours were assigned to each product's data path to ensure
visual distinction and clarity within the radar grid.