Financial Spreadsheet - Multi year calculation

[Serene]

Hi everyone,

I have built a worksheet where I have calculated the Profit & Loss and Cash Floor for a model store for 5 years (ie, Year 1, Year 2, Year 3, Year 4, Year 5)

What I need to do is to decide how many stores I should open each year. For each store, each year, I will use the same model store calculation. For eg, Year 1 I have 3 stores, Year 2, I add 3 more stores = 6 stores, and so on.

What is the best way to do the scenario sheet for the multi store scenario?
The output will be a multi year, Profit and Loss as well as Cash Flow.

Do I need to redo the original sheet for eg, reorganising all the information by rows so they are treated as data. Any insight and suggestions would be appreciated.

Thank you
Serene

 

[Faseeh]

Can you share the spread sheet. It is an optimization problem to be solved with SOLVER. I will be glad to help.

 

[Shteven]

Hi everyone,

I have built a worksheet where I have calculated the Profit & Loss and Cash Floor for a model store for 5 years (ie, Year 1, Year 2, Year 3, Year 4, Year 5)

What I need to do is to decide how many stores I should open each year. For each store, each year, I will use the same model store calculation. For eg, Year 1 I have 3 stores, Year 2, I add 3 more stores = 6 stores, and so on.

What is the best way to do the scenario sheet for the multi store scenario?
The output will be a multi year, Profit and Loss as well as Cash Flow.

Do I need to redo the original sheet for eg, reorganising all the information by rows so they are treated as data. Any insight and suggestions would be appreciated.

Thank you
Serene

Without seeing your layout, might I suggest a cell where you would input a number of new stores to open and the scenario sheet's formulas would be based off that. You would then customize each formula as needed: maybe you can reduce the cost of buildout by buying materials in bulk, or maybe your incremental tax rate flattens out, etc.