Forecasts using excel, circular references and formulae | AccountingWEB

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!

There are 2 comments. Login or register to view them.

Try this

paulwakefield1 |

Thank you

tugwilson |