Below is an Excel model which forecasts yearly profit for an accounting practice

Question

Below is an Excel model which forecasts yearly profit for an accounting practice Smith & Chua, Chartered Accountants, based upon the following requirements: The model has to show forecast profit for the next 3 years (2011,2012,2013)
The yearly service revenue is $1,000,000 and is forecast to grow by 15% per year
The average salary is $100,000 and isn't expected to change over the next few years. However the company has a strong recruiting drive and expects to increase it's workforce by 20% per year. The company has a rule it only employs full time staff and the currently has 5 full time staff
Other expenses are $250,000 and aren't expected to change in the next 3 years
The model must be contained in cells A1:E24
What is the best formula in E17?

A. $D$17*(1+$B$3)
B. D17*(1+$B$3)
C. D17*(1+B3)
D. $D$17*(1+B3)

INPUT/ASSUMPTIONS 2 Service Revenue 1,000,000 3 Revenue Growth 4 Average Salary 5 Staff Growth 6 Staff numbers 7 15% $100,000 20% 10 11 Service Revenue 12 Revenue Growth 13 Average Salary 14 Staff Growth 15 16 Staff Numbers 17 Service Revenue 18 Expenses 19 20 21 22 Profit 23 REPORT 1,000,000 15% $100,000 20% 2011 2012 2013 1,000,000 $1,150,000 $1,322,500 $500,000 $600,000 $700,000 $250,000 $250,000 $250,000 Total $750,000 $850,000 $950,000 $250,000 $300,000 372,500 Salaries Other

Explanation / Answer

Hi
putting $ makes the cell, row or column absolute, instead of related i.e you copy it does not change.
Hence in this case, we know that B3 is fixed i.e revenue growth, so we need to use $ for B3, before and after,
for D17 however though is relative, because as we move to the next year, D17 will be D18, hence no dollar for D
so the formula would be D17*(1+$B$3)

Thumbs up if this was helpful, otherwise let me know in comments

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.