Formula to Calculate Projected Sales

Well-known Member
Hello,

I want to use formulas to project sales for next years and maintain a gross profit Margin of (11.25%, 13.67% , 15.26%). Can anyone suggest me the best method to use in the below example? Can I use the Solver - add in?

 Particulars Year-1 Year-2 Year-3 Year-4 Sales 10,000.00 Rent 2,000.00 Wages 2,050.00 Printing 3,050.00 Salaries 1,600.00 Interest 260.00 Gross Profit 1,040.00 Gross Profit (%) 10.40% 11.25% 13.67% 15.26%

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>
Thank you

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Tetra201

MrExcel MVP
Is this what you need?

=SUM(C\$4:C\$8)/(1-C\$11)

joeu2004

Banned user
[.... deleted; over-complicated ....]

Last edited:

joeu2004

Banned user
[.... deleted; misunderstood what you want to project: sales; sorry ....]

Last edited:

Well-known Member
Thank you very much for the detailed explanation.

joeu2004

Banned user
Thank you very much for the detailed explanation.

You're welcome. I'm glad you got something out of my misdirections. Just to tie up lose ends....

As I noted, you do not need to use Solver. But there are an infinite number of solutions, depending on assumptions.

First, decide how costs might change. For example, suppose they all change by the same inflation rate of 3%.

Then one solution might be the following.

 A B C D E 1 Y1 Y2 Y3 Y4 2 sales 10,000.00 10,398.65 11,010.85 11,553.97 3 4 rent 2,000.00 2,060.00 2,121.80 2,185.45 5 wages 2,050.00 2,111.50 2,174.85 2,240.09 6 printing 3,050.00 3,141.50 3,235.75 3,332.82 7 salaries 1,600.00 1,648.00 1,697.44 1,748.36 8 interest 260.00 267.80 275.83 284.11 9 10 gross prof 1,040.00 1,169.85 1,505.18 1,763.14 11 %gross prof 10.40% 11.25% 13.67% 15.26% 12 13 check %prof 11.25% 13.67% 15.26%

<tbody>
</tbody>
Code:
``````Formulas:
C2:  =SUM(C4:C8)/(1-C11)
C4:  =B4*(1+3%)
C13: =C10/C2
B10: =B2-SUM(B4:B8)
Copy C2 to D2:E2
Copy C4 to C4:E8
Copy B10 to C10:E10
Copy C13 to D13:E13``````

Note that the formula in C2 is essentially the same as the one that Tetra201 provided.

Since %profit = (totalSales - totalCosts) / totalSales = 1 - totalCosts/totalSales,
then totalSales = totalCosts / (1 - %profit).

Last edited:

Replies
2
Views
416
Replies
0
Views
441
Replies
2
Views
2K
Replies
4
Views
5K
Replies
3
Views
426

1,191,348
Messages
5,986,142
Members
440,005
Latest member
tombamber

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back