Class 10

Class 10 IT (402) – Electronic Spreadsheet Practical Questions with Solution

Class-10-IT-402-Electronic-Spreadsheet-Practical-Questions-with-Solution

1. Consolidate Data

  1. You have monthly sales data for January, February, and March in three separate worksheets (Jan, Feb, Mar). Each sheet has the same structure:
    • Column A: Product Name
    • Column B: Sales Amount
      Create a new sheet Q1_Total and consolidate the data to get the total Q1 sales per product using the Consolidate feature.
      a) Which consolidation function will you use?
      b) Will you consolidate by position or by category? Explain briefly.
  2. Branch offices (North, South, East, West) send you separate workbooks containing weekly expense data with the same layout (same row/column structure). Describe the steps to consolidate all these workbooks into a single workbook that shows:
    • Total expenses of each branch
    • Grand total of all branches
  3. You have two worksheets:
    • Sheet1 contains product-wise sales with Product IDs in column A and Sales in column B.
    • Sheet2 contains the same Product IDs in column A but in a different order, with Sales in column B.
      Explain how you would use Consolidate to combine sales by Product ID even though the order of products in both sheets is different.
  4. A manager wants a consolidated report of quarterly sales from 4 department sheets where some departments have additional products not present in others.
    • How will you ensure that all unique products from all sheets appear in the consolidated report?
    • Which options in the Consolidate dialog box are crucial to achieve this?
  5. You receive monthly attendance data from multiple teams in different workbooks. The structure is identical:
    • Row 1: Employee Names
    • Column A: Dates
      How will you consolidate all the files to get total days present per employee for the month?

2. Subtotal

  1. You have a sales list with the following columns: Region, Salesperson, Product, Sales Amount.
    Create a report that shows:
    • Subtotal of Sales Amount for each Region
    • Grand total of all regions
      Write the sequence of steps to use the Subtotal feature for this.
  2. Given a list of invoices with columns: Customer, Invoice Date, Invoice Amount, you want to see:
    • Total Invoice Amount for each customer
    • The outline (grouping) so you can expand/collapse each customer’s detail
      Describe the steps to insert and use Subtotals for this purpose.
  3. Your worksheet has data sorted by Category and then by Product. You want:
    • Subtotal of Quantity Sold for each Category
    • Average Quantity Sold within each Category
      Can you use a single Subtotal operation for both functions? If not, how will you achieve this?
  4. After applying Subtotals on a large dataset grouped by Project, you are asked to:
    • Show only the subtotal rows (hide details) and copy them to another sheet.
      Explain how to show only subtotal rows and copy just those rows.
  5. You applied Subtotals but then received updated data that needs fresh grouping by another column (Department instead of Region).
    • How do you remove the existing Subtotals?
    • Then, how do you apply new Subtotals based on Department?

3. Scenario Manager

  1. A company’s monthly profit is calculated as:
    Profit = Revenue – Expenses
    Assume:
    • Revenue depends on Price per unit and Units sold
    • Expenses are fixed plus variable per unit
      Create three scenarios in Scenario Manager:
    • Best Case
    • Worst Case
    • Most Likely
      Specify which cells will be changing cells in Scenario Manager and what each scenario might contain.
  2. You are making a personal monthly budget sheet with these adjustable items:
    • Rent
    • Groceries
    • Transport
    • Entertainment
      You want to compare “Low-cost lifestyle”, “Normal spending” and “Luxury lifestyle” options.
      Describe how to set up and store these as scenarios and generate a summary report comparing total monthly expense in all three.
  3. A project plan has three key inputs:
    • Team size
    • Hourly rate
    • Total hours
      And you calculate Total Project Cost.
      Explain how you would use Scenario Manager to quickly show management three cost options based on different team sizes and hourly rates.
  4. A sales forecast sheet uses these variables:
    • Market Growth Rate
    • Advertising Spend
    • Sales Conversion Rate
      You have already created scenarios named Optimistic, Conservative, and Pessimistic.
    • How do you switch between scenarios?
    • How do you generate a Scenario Summary that clearly lists the values of the changing cells and the resulting sales for each scenario?
  5. In your spreadsheet, some inputs for Scenario Manager are on different sheets (e.g., Inputs sheet and Rates sheet).
    • What limitations does Scenario Manager have regarding the location of changing cells?
    • How can you organize your sheet to still use Scenario Manager effectively?

4. Goal Seek

  1. In a loan calculation sheet, you know:
    • Loan amount
    • Term (in months)
    • Monthly payment you can afford
      You want to find the interest rate that makes the monthly payment equal to your budget.
      Describe step-by-step how you would use Goal Seek to find the interest rate.
  2. You sell a product with:
    • Fixed monthly costs (rent, salaries, etc.)
    • Variable cost per unit
    • Selling price per unit
      Profit formula: Profit = (Selling price – Variable cost) × Units sold – Fixed costs
      Use Goal Seek to find how many units you must sell to reach a target profit of ₹50,000.
      Which cell is the “Set cell”? Which is the “By changing cell”?
  3. Your sheet calculates Final Grade as:
    Final Grade = 0.4 × Midterm + 0.6 × Final Exam
    A student needs at least 70 marks as the final grade to pass.
    Using Goal Seek, determine the minimum marks the student must score in the Final Exam (Midterm marks are already known).
    Explain the configuration of Goal Seek (Set cell, To value, By changing cell).
  4. A sales target sheet calculates total revenue as:
    Total Revenue = Units sold × Selling price
    Currently, Units sold and Selling price are fixed, but management wants to know what selling price is needed to achieve a revenue of ₹10,00,000 with the current units sold.
    Describe how you would use Goal Seek to find the required selling price.
  5. A savings plan sheet calculates the future value of an investment using a compound interest formula. You know:
    • Monthly deposit
    • Interest rate
    • Number of months
      You want your savings to reach a specific target amount in 5 years.
      Use Goal Seek to find the necessary monthly deposit. What assumptions must be correctly set before you run Goal Seek?

5. Data Table (What‑If Analysis)

  1. You have a loan EMI formula that calculates monthly payment based on:
    • Loan amount (fixed)
    • Interest rate (variable)
    • Number of years (fixed)
      Create a one‑variable Data Table that shows how EMI changes for interest rates from 6% to 15% in steps of 0.5%.
      Describe the layout of the table and which cell will be the Column input cell.
  2. In a profit sheet, Profit = Units sold × Profit per unit.
    You want to examine how total profit changes for different combinations of:
    • Units sold (e.g., 1,000; 2,000; 3,000; 4,000)
    • Profit per unit (e.g., ₹50; ₹75; ₹100)
      Design a two‑variable Data Table and explain how to set Row input cell and Column input cell.
  3. A project’s Net Present Value (NPV) depends on:
    • Discount rate
    • Initial investment
      You want to see how NPV changes for different discount rates (rows) and different initial investments (columns).
      Show how to structure a two‑variable Data Table for this scenario.
  4. You have a retirement calculator that computes the final amount based on:
    • Monthly contribution
    • Years of investment
    • Fixed interest rate
      Create a one‑variable Data Table that varies years of investment (from 5 to 30 years) while keeping other inputs constant.
      Explain how you would interpret the resulting table.
  5. A company’s budgeting model calculates Annual Profit based on:
    • Sales growth rate
    • Cost increase rate
      You want to see how profit is affected by simultaneous changes in both rates.
    • Propose how to set up a two‑variable Data Table to test various combinations.
    • How does this differ from using Scenario Manager?

6. Solver

  1. A manufacturer makes two products, A and B.
    • Profit per unit: A = ₹40, B = ₹50
    • Machine hours per unit: A = 2 hours, B = 3 hours
    • Total machine hours available: 240 hours
    • Demand constraint: At most 80 units of B can be sold
      Use Solver to determine how many units of A and B should be produced to maximize profit.
      Specify:
    • Objective cell
    • Variable cells
    • Constraints
  2. A diet plan must meet the following daily requirements:
    • At least 2,000 calories
    • Between 50 and 70 grams of protein
      You have two food items with known calories and protein per unit and known cost per unit.
      Use Solver to find the combination of the two food items that meets the requirements at minimum cost.
      Explain how you will set up the objective, decision variables, and constraints.
  3. A delivery company wants to minimize transportation cost given:
    • A list of routes with cost per trip
    • A required minimum number of deliveries for each region
      Describe, in general terms, how you would model this problem in a spreadsheet and use Solver to minimize total cost subject to meeting all delivery requirements.
  4. A factory must schedule employees for shifts such that:
    • Each shift requires at least a certain number of workers
    • Each worker can work at most 8 hours per day
    • Total wage cost should be minimized
      You have wage rate per worker and availability.
      How would you use Solver to determine the number of workers assigned to each shift?
  5. A financial planner is choosing an investment mix among three types of funds:
    • Equity fund (high return, high risk)
    • Bond fund (medium return, low risk)
    • Money market (low return, very low risk)
      Constraints:
    • Total investment = ₹10,00,000
    • At least 30% in bonds
    • At most 50% in equity
    • Overall portfolio risk index must not exceed a given value
      Use Solver to maximize expected return.
      Identify the changing cells, objective cell, and constraints.
Sharing is caring – pass it on!

Related Posts

Post Comment

You May Have Missed