Spreadsheets: Formulas, Functions, Charts & Business Data

What is a Spreadsheet?

A spreadsheet is an electronic document that organizes data in rows and columns, allowing calculations, analysis, and visualization. It is widely used in business for financial modeling, inventory tracking, and data analysis.

Key Components of a Spreadsheet

• Cells: The intersection of rows and columns (e.g., A1, B2).
• Worksheet: A single sheet within a spreadsheet file (e.g., Excel workbook).
• Workbook: A collection of worksheets (e.g., Excel file .xlsx).
• Formulas: Equations that perform calculations (e.g., =SUM(A1:A10)).
• Functions: Predefined formulas for specific tasks (e.g., =AVERAGE(), =VLOOKUP()).
• Charts: Visual representations of data (e.g., bar, pie, line charts).

Cells and Cell References

Cells are identified by their column letter and row number (e.g., B5). Cell references can be:

• Relative: Adjusts when copied (e.g., A1 becomes B1 if copied right).
• Absolute: Fixed with $ (e.g., $A$1 remains A1 when copied).
• Mixed: Combines relative and absolute (e.g., A$1 locks the row).

Example: If you copy =A1+B1 from row 1 to row 2, it becomes =A2+B2 (relative). If you copy =$A$1+B1, it becomes =$A$1+B2 (mixed).

Formulas and Functions

Basic Formulas

Formulas always start with = and use operators:

• + (addition), - (subtraction), * (multiplication), / (division), ^ (exponent).
• Example: =10+5*2 → =20 (multiplication first, then addition).

Common Functions

sequenceDiagram
    participant User as User
    participant Spreadsheet as Spreadsheet
    User->>Spreadsheet: Inputs: =VLOOKUP("Apple", A1:B10, 2)
    Spreadsheet-->>User: Returns: 150 (Price of Apple)
    User->>Spreadsheet: Inputs: =SUM(B2:B4)
    Spreadsheet-->>User: Returns: 450 (Total Sales)

Example: Calculate the total sales for a month:

| Product | Sales |
|---------|-------|
| A       | 100   |
| B       | 200   |
| C       | 150   |

Formula: =SUM(B2:B4) → 450.

Formatting and Data Validation

Formatting

• Number formats: Currency (₹1,000), percentage (50%), date (2023-12-31).
• Cell styles: Bold, italic, borders, fill color.
• Conditional formatting: Highlight cells based on rules (e.g., red if value > 100).

Example: Format sales data to show amounts in currency:

| Product | Sales   |
|---------|---------|
| A       | ₹1,000  |
| B       | ₹2,000  |

Data Validation

Restrict cell input to specific values (e.g., dropdown lists, numbers only). Example: Limit sales entries to numbers between 1 and 10,000.

Charts and Graphs

Charts visualize data trends. Common types:

• Column/Bar: Compare categories (e.g., monthly sales).
• Pie: Show proportions (e.g., market share).
• Line: Track trends over time (e.g., stock prices).
• Scatter: Show relationships (e.g., temperature vs. sales).

Example: Create a bar chart for monthly sales:

| Month   | Sales |
|---------|-------|
| Jan     | 500   |
| Feb     | 700   |
| Mar     | 600   |

Steps:

1. Select data (A1:B4).
2. Insert → Bar Chart.
3. Customize labels, colors, and titles.

Sorting, Filtering, and Data Analysis

Sorting

Arrange data alphabetically or numerically (e.g., sort products by sales). Example: Sort A1:C10 by column B (Sales) in descending order.

Filtering

Display only rows matching criteria (e.g., show products with sales > 500). Example: Filter A1:C10 to show only rows where B2:B10 > 500.

Data Tools

• PivotTables: Summarize large datasets (e.g., total sales by region).
• What-If Analysis: Test scenarios (e.g., "What if sales increase by 10%"?).

Example: Create a PivotTable for sales by product category:

| Category | Total Sales |
|----------|-------------|
| A        | 1,000       |
| B        | 2,000       |

Real-World Applications

1. eSewa (Nepal)

• Use: Tracks government payments (electricity, taxes).
• Spreadsheet Idea: Uses formulas to calculate due amounts, penalties, and payment histories.
• Example: If a user pays ₹500 late, eSewa applies a 5% penalty (=B2*1.05).

2. Daraz (E-Commerce)

• Use: Manages inventory and order processing.
• Spreadsheet Idea: Tracks stock levels, sales, and profit margins.
• Example: If 100 units are sold, Daraz updates stock (=C2-100) and calculates profit (=D2*100-C2*10).

3. Nepal Rastra Bank (NRB)

• Use: Analyzes economic data (inflation, GDP).
• Spreadsheet Idea: Uses AVERAGE() and SUM() to forecast trends.
• Example: Average inflation rate over 5 years (=AVERAGE(B2:B6)).

4. Pathao (Ride-Hailing)

• Use: Optimizes driver routes and fare calculations.
• Spreadsheet Idea: Uses VLOOKUP() to match driver IDs with fares.
• Example: Find fare for driver ID "D123" (=VLOOKUP("D123", A1:B100, 2)).

Worked Example: Budget Planning for a Small Business

Scenario: A shopkeeper tracks monthly expenses and profits.

Chart: Create a line chart for monthly profit trends.

Advantages and Disadvantages of Spreadsheets

Common Mistakes to Avoid

1. Circular References: A formula referring to its own cell (e.g., A1=B1, B1=A1).
2. Incorrect Cell References: Forgetting $ for absolute references.
3. Ignoring Data Validation: Allowing invalid entries (e.g., text in a number field).
4. Overcomplicating Formulas: Using nested functions unnecessarily.
5. Not Backing Up: Losing data due to unsaved files.

In the real world

• eSewa: Uses formulas (=B2*1.05) to calculate late payment penalties for electricity bills, ensuring accurate financial tracking for users.
• Daraz: Employs VLOOKUP to match product IDs with prices and stock levels, automating inventory management and order processing.
• Pathao: Leverages conditional formatting to highlight high-demand routes, helping drivers optimize their earnings based on real-time data.

Exam Tip

1. Understand Formulas: Know how to write and debug formulas (e.g., =SUM(), =AVERAGE()).
2. Practice Data Analysis: Be ready to create PivotTables or charts from given data.
3. Real-World Scenarios: Expect questions on budgeting, inventory, or financial calculations.
4. Shortcuts: Learn basic shortcuts like Ctrl+C (copy), Ctrl+V (paste), F4 (toggle absolute reference).
5. Diagrams: If asked to design a spreadsheet layout, sketch it with clear labels (e.g., columns for "Product," "Sales," "Profit").