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!
- Interest Free Loan - Salay Sacrifice? 264 5
- Share premium or Directors Loan 780 32
- Rollover relief - when does gain crystallise in this situation? 131 2
- Capital allowances anti-avoidance 394 13
- HMRC Money Laundering Visit 568 3
- Limited Company - Personal Bank Account 3,665 74
- Dividend Payments 489 12
- Employed clients and expenses 893 18
- More HMRC on gov.uk 669 12
- Payroll experience for beginning AAT student 156 3
- Interest on HMRC contract settlement 197 4
- Providing registered office address to clients 158 2
- Enquiry into tax return - how co-operative would you be? 432 8
- Halloween - do you celebrate it? 1,464 47
- Goodwill gesture for providing work 482 12
- VAT reconciliation and VAT return on Sage 150 3
- What would you do in this situation? 250 5
- Filing a P87 (professional fees) before year end. 166 2
- Recommendations for payroll/AE software? 636 13
- Offshore company transferring assets to a UK individual. 359 14
- Bonus Bonds 396
- Insolvency and whistle blowing 391
- Transfer of funds into recently set up company 300
- TPR imposes first penalty notice fines 264
- Husband proposes to buy the car as Director unable to acess finance 199
- Box 8 VAT Return - deposit payment 193
- Company Car Lease 171
- Finance Lease 161
- Form 42 154
- Demerger Reserve 135