Cash Flow Statement & Cash Balance

Standard workback procedures. Start where you were balanced/tie last. Then do cash in and cash out totals per month....as compared to your cashflow statement. Correction depends on what went wrong.

Emerson's recommendation is the right approach if it is critical that the cash flow cash balance (reporting and forecast, I assume) agrees to QuickBooks for all historical periods. If QuickBooks is the basis of financial statements and formal reporting for the Company and the Excel sheet is used for forecasting purposes, you can shortcut the potentially arduous process of going through 3+ years of data by adjusting your cash flows balance in Excel to the correct beginning balance on January 1st of this year and then reconciling the cash balance for each week or month to QuickBooks forward to today - then make sure it reconciles each month thereafter.

Hard to answer as explained in the question. May need more details and more info. I assume by "reconciles with quick books" means that your bank statement reconciles. That is a good start. Treat the bank statement as the defining start, since that is what actually happened. Then if QB reconciles, sounds like you have that record right (though not necessarily), then work back into the excel record, which likely has the riskiest accuracy of the lot.

Then when you say you track revenues and inventory outside of QB, makes me wonder how complete QB has been in the past anyway. I agree with the two previous answers. Try checking just the 36 or so month end cash balances in your excel model and compare it to the reconciled QB cash balance (obviously not to the bank statement number). That can't take long. You will either see where the model went astray, or if it never agreed, I would question the whole excel model structure itself.

Hard to forecast using a tool that is inherently flawed.

Thanks all. For some additional clarity, we recently completed a backfill of inventory balances to day 1. The amounts we actually paid to acquire inventory are different from the per unit cost we entered in the system - so that will contribute to some of the difference. Wanted to get some opinions if we could do some sort of adjustment/true up or if we need to go back and reconcile to day 1 (huge resource commitment).

Is the excel model for forecasting or something else? Not sure if I gathered that from the original question. Cash is cash, so you are going to have to restate something somewhere and if you know which data represents your version of truth (your backfill purchase price exercise) and have fully vetted it, it could be more time effective to restate the figures. You can keep a backup of the data as it currently exists prior to undertaking the exercise ready.

Don't know who the constituents / consumers of your financial data are, have you already been audited, etc. which also impacts prospective vs. retrospective treatment (potentially).