I am working on a budget for next year need to figure out what is the revenue requirement for each GM and net income. For example, operating expense % over revenue is at 27% and GM is at 27%. For Net income of $1000, how much revenue require? If the GM is 1% lower which is at 26%, operating expense % of revenue is the same at 27%. How much revenue require to have net income at $1000. Thanks, Lindsay
Does any one know how to calculate the revenue need for net income on a certain GM%
Answers
I'm not sure I completely understand your question, but "Target Profit" = (Target Profit $ + Fixed Cost $) / (Contribution Margin Ratio), where:
>Contribution Margin = Sales - Variable Costs, and
>Contribution Margin Ratio = Contribution Margin/Sales
I think I understand your question. I have done something similar, but with fixed operating costs, trying to find what level of sales we need at a given GM to cover expenses.
In your case I created a simple spreadsheet you can try. I believe it's correct but please check the numbers.
Column A is revenue, column B = GM, column C = operating costs, and Column D = net income. Columns A and B are manual inputs. Column C = Column A * .27 (your operating costs as a percentage of revenue). Column D is the formula =ABS((A1*B1)-A1)-C1 where the first part calculates the COGS and then we subtract the operating expenses. If you then do trial and error in Column A, you'll see that at .26 GM, you need revenue of 2128 to get NI of 1000 (1000.16 to be exact).
At 27% GM, you need 2175 of revenue to get NI of 1000 (1000.50).
Those variable operating costs are the tricky part as they go up as revenue does.
2
Yep. aka breakeven analysis?
Yes, it is breakeven analysis, and then we added in the desired NI of 1000
It's "Target Profit" analysis, which is a derivative of breakeven analysis. If you know your fixed vs variable costs, you can skip the trial and error and divide [Target Profit + Fixed costs] by [Contribution margin ratio], which is [Sales - Var.Costs] / Sales.
Filed Under:
Budgeting & Forecasting