1
$\begingroup$

I am writing up a cost sheet for a product and I basically suck at math. Didn't know who else to turn to, so trying out Math exchange.

So, I am planning to spend 1,100 every month on advertising that will bring me 130 of additional cumulative revenue every month. Basically, it will bring me new clients that I charge $130 every month in addition to existing clients. So cost is fixed monthly, but revenue is cumulative.

How do I calculate when my cost v/s revenue breaks even, and my profit from thereon?

If I put this up on a spreadsheet, it looks something like this:

Month | Revenue | Cost

1 | 130 | 1100

2 | 260 | 1100

3 | 390 | 1100

4 | 520 | 1100

5 | 650 | 1100

6 | 780 | 1100

Total After 6 months:

Revenue: 2,730

Cost: 66,000

  • 0
    You're right. But it's just an extra zero. How much difference could one measly zero make?! =D2011-10-13

2 Answers 2

2

The total cost after $n$ months is $C_{n}=1100n$ and the total revenue is $ R_{n}=130\times \frac{n(n+1)}{2}$, because

$\begin{eqnarray*} R_{n} &=&130\times 1+130\times 2+130\times 3+\ldots +130\times n \\ &=&130\left( 1+2+3+\ldots +n\right) \\ &=&130\times \frac{n(n+1)}{2}, \end{eqnarray*}$

where I used the value of the sum $1+2+3+\ldots +n=\frac{n(n+1)}{2}.$

Equating $C_{n}=R_{n}$

$1100n=130\times \frac{n(n+1)}{2},$

simplifying

$1100=130\times \frac{n+1}{2}$

and solving for $n$ yields $n=\frac{207}{13}\approx 15.92$. And so, the breakeven month is $n=16$. Confirmation:

$C_{16}=1100\times 16=17\,600,$

$R_{16}=130\times \frac{16(16+1)}{2}=17\,680.$

The accumulated profit is $R_n-C_n$.

Here is a plot of $R_n$ (blue) and $C_n$ (sienna) versus $n$ (month)

enter image description here

  • 1
    I use SWP (Scientic Wor$k$Place) from MacKichan Software. In Excel you select data from rows and columns and choose the graph type.2011-10-13
1

You just need to extend your spreadsheet downwards.

By month 9 ($\approx \frac{1100}{130}$) you should find revenues exceeding advertising costs, and after about twice as long you should find cumulative revenues exceeding cumulative advertising costs.

You can also consider other costs (such as manufacture), discounted cash flow, and the sustainability of your model at higher levels of sales.

  • 0
    I need a formula I can use in excel to play around with different investment options. Can you elaborate how sustainability of my model at higher level of sales will be affected in terms of advertising costs?2011-10-13