22.5 Finance and Accounting
Finance interviews in Pune firms ask variance, not "how many vouchers did you pass". The sign must be written on the page. Here a positive variance means under budget (budget minus actual), the same rule as Module 21.2.
| KPI | What the number means | Who uses it | Action it drives |
|---|---|---|---|
| Variance | Budget minus actual, in ₹. | Accountant, city finance lead | Over budget (negative) needs a name and an account. Under budget is not automatically good. |
| Variance % | Variance divided by budget. | Finance manager | Compare accounts. A company total can hide one account that is over. |
| Gross margin % | (Sales minus cost) divided by sales. Not in the table below; ask for both columns before you compute it. | Commercial finance | A margin drop with flat sales is cost, not "demand". |
Fictional sample rows. One month. No sales column here, so we do not invent a margin. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblBudget (Table Design › Table Name).
| City | Account | Budget | Actual |
|---|---|---|---|
| Pune | Rent | ₹10,000 | ₹8,000 |
| Pune | Wages | ₹20,000 | ₹24,000 |
| Nashik | Rent | ₹8,000 | ₹8,000 |
| Nashik | Wages | ₹16,000 | ₹12,000 |
| Kolhapur | Delivery | ₹6,000 | ₹9,000 |
| Nagpur | Wages | ₹20,000 | ₹16,000 |
Excel formulas for the whole file (type them in empty cells below or beside the table):
| KPI | Excel formula | Result | Number format |
|---|---|---|---|
| Variance | =SUM(tblBudget[Budget])-SUM(tblBudget[Actual]) |
₹3,000 | ₹#,##0 |
| Variance % | =(SUM(tblBudget[Budget])-SUM(tblBudget[Actual]))/SUM(tblBudget[Budget]) |
3.8% | 0.0% |
Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.
| New column | Formula in the first row |
|---|---|
| Variance | =[@Budget]-[@Actual] |
| Variance % | =([@Budget]-[@Actual])/[@Budget] |
Expected result per row:
| Account | Variance | Variance % |
|---|---|---|
| Rent (Pune) | ₹2,000 | 20.0% |
| Wages (Pune) | -₹4,000 | -20.0% |
| Rent (Nashik) | ₹0 | 0.0% |
| Wages (Nashik) | ₹4,000 | 25.0% |
| Delivery | -₹3,000 | -50.0% |
| Wages (Nagpur) | ₹4,000 | 20.0% |
One city with SUMIFS. Variance for Pune only:
=SUMIFS(tblBudget[Budget],tblBudget[City],"Pune")-SUMIFS(tblBudget[Actual],tblBudget[City],"Pune")
Result for Pune: -₹2,000. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.
| Good KPI | Vanity metric | |
|---|---|---|
| From the same rows | Pune wages ₹4,000 over budget | Company variance ₹3,000 under (3.8%) |
| Why | The wage line is the decision. | The company card looks healthy because Nashik and Nagpur underspent. A CFO who stops at 3.8% will miss Pune wages. |
What the manager does. The Pune finance lead asks who approved the extra ₹4,000 of wages, and does not "explain" it with the cheap rent. The Nashik lead asks whether the ₹4,000 wage gap is a vacant seat. Underspend on wages with the same delivery promise is not a saving until someone checks the roster.
Ravindra Bagale's Tip
Variance % is the difference divided by budget. Actual divided by budget (like 120%) is spend %, not variance. Label which one your sheet shows.
Ravindra Bagale's Tip – मराठी
Variance % म्हणजे फरक भागिले budget. Actual भागिले budget (उदा. 120%) म्हणजे spend %, variance नाही. तुमची sheet कोणतं दाखवते ते label करा.
Ravindra Bagale's Tip – हिंदी
Variance % यानी अंतर भाग budget. Actual भाग budget (जैसे 120%) spend % है, variance नहीं. आपकी sheet कौन सा दिखाती है, उसका label लिखें.