Share this content

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!


Please login or register to join the discussion.

19th Jul 2010 18:21

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)
19th Jul 2010 18:41

Thank you

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

Thanks (0)