Difficulty: Medium
Issue: Working backwards from the answer to calculate an input to a formula
Process: Using "What If Analysis" function. This can be found in Data (ribbon) > What-If-Analysis (Forecast section)'
Example: Calculating the effective life of an asset by working backwards from the depreciation expense1In this example, I have a depreciation calculation. I know the answer that needs to be calculated (Depreciation expense), but I don't have one of the inputs (The effective life). I could spend time trying to work backwards or use trial and error to calculate the answer. Alternatively, I could use the What-If-Analysis need to calculate the effective life of the plant & equipment that calculates a depreciation expense of $10,363.
First step is to include my calculation of depreciation expense in the last cell. The formula (DV) for this is below.
Using "What If Analysis" function. This can be found in Data (ribbon) > What-If-Analysis (Forecast section)' > Goal Seek.
Excel will ask you for three items:
i) Which cell we want to set. We want to set Depreciation expense (Q7) to a fixed value.
ii) What value to set depreciation (Q7) to. We want this to equal $10,363.i
ii) We want to get to this value by changing cell $P$7, which is the effective life of the asset.
Click OK. Excel will now calculate every value possible by trial and error to make the formula work.
Excel has calculated a value - 6.667 years, which is another way of writing a DV depreciation rate of 30%.
Excel Masterclass Course
If you want to learn more about Excel, regardless of your skill level, we offer free and premium Excel training courses on our website, www.meritexcel.com. Our Excel masterclass covers:
- all the key formulas you need (sumif, index, match, offset, eomonth, concatenate, lookup, if, or, sumproduct, date and time formulas, trim and much more)
- How navigate and use Excel effectively (viewing in 2 windows, paste special, locking cells, removing duplicates, autofill, freezing rows, fill blanks)
- Best practice techniques in Excel (formatting, checks, grouping, formula auditing, structure)
- How to present and manipulate data (Pivot tables, charts, editing charts, filtering and sorting)
- How to create engaging and dynamic spreadsheets (drop-downs, protect data, goal seek and data tables)
- And much, much more!
We offer a free, limited course which you can view here OR you can purchase the full, Excel masterclass here, which has over 20+ hours of amazing Excel content.
Comments