Formula to Calculate Projected Sales

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
[.... deleted; over-complicated ....]
 
Last edited:
Upvote 0
[.... deleted; misunderstood what you want to project: sales; sorry ....]
 
Last edited:
Upvote 0
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
Y2Y3Y4
2
sales10,000.0010,398.6511,010.8511,553.97
3





4
rent2,000.002,060.002,121.802,185.45
5
wages2,050.002,111.502,174.852,240.09
6
printing3,050.003,141.503,235.753,332.82
7
salaries1,600.001,648.001,697.441,748.36
8
interest260.00267.80275.83284.11
9





10
gross prof1,040.001,169.851,505.181,763.14
11
%gross prof10.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:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Back
Top