Question

2) Since the company has projected growth in same store sales, the average location sales in cell C15 must be increased by the percentage in the parameter cell C7 (Same Store Annual Sales Growth) in each subsequent year. Think of it this way if a year's average store sales was 100 and a 10% increase was assumed, the projected average sales per location in the next year would be 110. In the subsequent year it would be l 21 (1 10 + 10%), 1 331 (121+10%), and so on. Ultimately, you need a formula that increases a previous year's sales by the percentage in cell C7. Create a formula in cell D15 that is copy-able using absolute referencing as necessary. Copy your formula across through Year 5. (Note: There is more than one way to do this mathematically.) Current Year 156 549 Year 1 Year 2 Year 3 Year 4 Year 5 13 14 15 Locations Avg Sales per Location 204 396 573.705 599.521725 626.5002026 654.6927117 684.1538838

Answer 1

The formula to be used is:

The projected average sales per location for year ni =The average sales per location in current year, n0(1+growth rate in decimals)ⁱ.

Using symbols: S(ni) =S(n0)*(1+g)ⁱ​​​​​​

[where, i =0, 1, 2, 3,..... ; g= growth rate in decimals; n0 =current year; n1 =year 1; n2 =year 2 and so on. S(n0) =Avg sales per location for current year; n1 =Avg sales per location for year 1 and so on].

The formula to be entered (in Excel) in cell D15 is: =C15(1+C7)^1

The formula to be entered in cell E15 is: =C15(1+C7)^2

The formula to be entered in cell F15 is: =C15(1+C7)^3

The formula to be entered in cell G15 is: =C15(1+C7)^4

The formula to be entered in cell H15 is: =C15(1+C7)^5

We can do this as follows:

First enter the formula in cell D15 as above. Then copy it to remaining 4 cells (along the row) by using mouse (+ sign) and in each cell, change 1 to 2,3,4 and 5 accordingly.

Other method:

In cell D15, enter the formula: =C15(1+C7)

In cell E15, enter the formula: =D15(1+C7)

In cell F15, enter the formula: =E15(1+C7)

In cell G15, enter the formula: =F15(1+C7)

In cell H15, enter the formula: =G15(1+C7)

We can do this as follows:

First enter the formula in cell D15 as above but press F4 for C15 or add $ symbols for C15 which is as follows: =$C$15(1+C7)

Then copy this to the other cells (i.e., E15 to H15) by using mouse (dragging '+' sign). Now we get the answers.