MS Excel:
Section C: Practical Tasks (Perform in MS Excel)
1. Consolidating Data from Multiple Sheets
* Step 1: Create Sheets: I'd open a new Excel workbook. Then, I'd create three new sheets and
rename them "North," "South," and "East" respectively.
* Step 2: Add Data to Each Sheet:
* North Sheet: I'd add columns "Product" and "Sales." I'd then input some sample data like:
* Product A, 1500
* Product B, 2000
* Product C, 1200
* South Sheet: I'd add "Product" and "Sales" columns and input:
* Product A, 1000
* Product D, 1800
* Product B, 2500
* East Sheet: Again, "Product" and "Sales" columns, with data like:
* Product C, 900
* Product A, 1300
* Product E, 700
* Step 3: Create Consolidation Sheet: I'd create a new fourth sheet and name it "Total Sales."
* Step 4: Use Consolidate Feature: On the "Total Sales" sheet, I'd go to the "Data" tab, then
click on "Consolidate" in the "Data Tools" group.
* In the Consolidate dialog box, I'd select "Sum" as the function.
* For "Reference," I'd click the collapse dialog button and navigate to each of my North, South,
and East sheets, select the "Product" and "Sales" columns (including headers), and click "Add"
for each.
* I'd make sure "Top row" and "Left column" checkboxes are ticked so Excel correctly
identifies the headers and products.
* Finally, I'd click "OK" to see the consolidated data showing total sales per product.
2. Using Subtotal for Department-wise Salary Totals
* Step 1: Create Employee List: On a new sheet, I'd create columns for "Employee Name,"
"Department," and "Salary." I'd then list 10 employees with their respective departments and
salaries. For example:
* John Doe, Sales, 50000
* Jane Smith, Marketing, 55000
* Peter Jones, Sales, 48000
* Emily White, HR, 60000
* ... (and so on for 10 employees)
* Step 2: Sort by Department: Before applying Subtotal, it's crucial to sort the data by
"Department." I'd select all my data, go to the "Data" tab, and click "Sort." I'd choose
"Department" as the sort by key.
* Step 3: Apply Subtotal: With the data sorted, I'd keep the entire data range selected. I'd go to
the "Data" tab and click on "Subtotal" in the "Outline" group.
* In the Subtotal dialog box:
* "At each change in": I'd select "Department."
* "Use function": I'd select "Sum."
* "Add subtotal to": I'd check "Salary."
* I'd click "OK." Excel would then automatically insert rows with department-wise salary totals.
3. Using Scenario Manager for Monthly Budget Planning
* Step 1: Set up Budget Structure: On a new sheet, I'd create a basic budget. I'd have cells for
"Income" (e.g., Salary), and several "Expenses" (e.g., Rent, Groceries, Utilities, Entertainment).
I'd also have a "Net Savings" cell calculated as Income - Sum(Expenses).
* Step 2: Define Changing Cells: My changing cells would be "Income" and the various
"Expense" categories, as these are what I'd vary for different scenarios.
* Step 3: Open Scenario Manager: I'd go to the "Data" tab, click "What-If Analysis," and then
"Scenario Manager."
* Step 4: Add Scenarios:
* Best Case: I'd click "Add."
* Scenario Name: "Best Case"
* Changing cells: I'd select the Income and Expense cells.
* In the values dialog box, I'd input optimistic values (higher income, lower expenses).
* Average Case: I'd click "Add."
* Scenario Name: "Average Case"
* Changing cells: Same as above.
* Values: My typical or expected income and expense figures.
* Worst Case: I'd click "Add."
* Scenario Name: "Worst Case"
* Changing cells: Same as above.
* Values: Lower income, higher expenses (pessimistic outlook).
* Step 5: Show Scenarios and Summary: After adding all three, I could select each scenario in
the Scenario Manager and click "Show" to see how my "Net Savings" changes. I could also click
"Summary..." and choose "Net Savings" as the result cell to generate a summary report on a
new sheet.
4. Using Goal Seek to Find Units to Sell for Profit
* Step 1: Set up the Calculation: On a new sheet, I'd set up the following:
* Cell A1: "Selling Price per Unit:" (value: 120)
* Cell A2: "Units Sold:" (This will be my unknown, so I'd put a placeholder value, e.g., 10)
* Cell A3: "Fixed Cost:" (value: 1000)
* Cell A4: "Total Revenue:" (Formula: =A1*A2)
* Cell A5: "Total Profit:" (Formula: =A4-A3)
* Step 2: Open Goal Seek: I'd go to the "Data" tab, click "What-If Analysis," and then "Goal
Seek."
* Step 3: Define Goal Seek Parameters:
* "Set cell": I'd select cell A5 (my "Total Profit" cell).
* "To value": I'd enter 5000 (my target profit).
* "By changing cell": I'd select cell A2 (my "Units Sold" cell).
* Step 4: Run Goal Seek: I'd click "OK." Excel would then calculate and display the number of
units that need to be sold to achieve a total profit of ₹5000.