File Name
Data_Table_Practice.xlsx
Part A — Profit Analysis
Inputs
|
Input |
Value |
|
Quantity |
100 |
|
Selling Price |
₹500 |
|
Cost per Unit |
₹300 |
|
Fixed Cost |
₹5,000 |
Profit Formula
=(B2*B3)-(B2*B4)-B5
Task 1 — One-Variable Data Table
Test:
|
Selling Price |
|
₹400 |
|
₹450 |
|
₹500 |
|
₹550 |
|
₹600 |
|
₹650 |
|
₹700 |
Calculate the resulting profit.
Task 2 — Two-Variable Data Table
Quantity
50, 100, 150, 200
Selling Price
₹400, ₹500, ₹600, ₹700
Create:
|
Quantity ↓ / Price → |
₹400 |
₹500 |
₹600 |
₹700 |
|
50 |
||||
|
100 |
||||
|
150 |
||||
|
200 |
Part B — Loan EMI Data Table
Loan Amount
₹5,00,000
Create this table:
|
Interest Rate ↓ / Years → |
3 |
5 |
7 |
10 |
|
8% |
||||
|
9% |
||||
|
10% |
||||
|
11% |
||||
|
12% |
Use:
=PMT(InterestRate/12,Years*12,-500000)
Final Analysis
Identify:
- Lowest EMI
- Highest EMI
- Effect of interest rate
- Effect of loan period
Final Challenge
Create a What-If Analysis Report containing:
Section 1
One-variable Profit Data Table
Section 2
Two-variable Profit Data Table
Section 3
Loan EMI Data Table
Section 4
Short written analysis:
- How does selling price affect profit?
- How does quantity affect profit?
- How does interest rate affect EMI?
- How does loan period affect EMI?