Blogger
Share this content
0
2
2434

Forecasts using excel, circular references and formulae

Currently doing monthly forecasts. Cost of sales in based on a percentage of turnover. Cost of sales consists of opening stock, purchases, carriage, packaging less closing stock. Closing stock is a manually entered figure, that becomes the opening stock for the next month etc, using + 'closing stock of previous month' cell.

I want a formula for purchases, that would leave the cost of sales as the stated figure based on % of turnover, that can accomodate any changes I make in the closing stock figure. (The purchases figure then copies into my creditor control account).

I am having problems here with circular references, but wonder if anyone know a way round it please?

I note you can allow the calculation in Excel 2007 by going into formulae and tools, but feel uncomfortable with this.

Thanks for everyones help in advanace. Again!

Replies

Please login or register to join the discussion.

Try this

Assuming the signing convention you are using is that all constituent elements of the costs of sales figure are positive except closing stock then the formula you want is Turnover * COS% - all other COS entries (Opening stock, carriage, closing stock, etc.).

 

 

Thanks (0)

Thank you

Simple really. (When you know how!). Thanks a lot

Thanks (0)