Importing a budget from excel into QuickBooks is not a simple task. However, once you try it and understand the concept, you will be doing the importation yourself.
Budgets are usually based on some historical information. If you are already using QuickBooks, go to reports, then accounting and taxes, click on the trial balance, and then choose the last fiscal year option. You can build your budget based on your numbers from the previous year and add a reasonable inflation rate, such as 20%.
You can export the prior year’s budget numbers into excel. On QuickBooks, go to the excel option, create a worksheet, and then click on export. When importing, remember that QuickBooks has a chart of accounts that has your expenses codes.
We started with a trial balance because before building the excel spreadsheet randomly for budgets, there is a possibility that the chart of accounts may not be entirely similar to the one on QuickBooks. If you notice some accounts are missing, you can go back to QuickBooks, click the customize report option, click on advanced, click on all accounts, then hit okay. This will help you to have all complete active accounts.
How to Put the Excel Sheet Into an Importable Format
Go to the excel budget template gotten from QuickBooks. You can delete the accounts that you don’t need, especially the balance sheet accounts. The deletion should leave you with your expenses and income accounts, which are debits and credits. It would help if you converted the credits into negatives and debits into positives. To do this, you will come up with a formula (debits-credits) then drag it to the entire excel sheet.
Your new calculated annual budget will be the debits less the credits, which will be your starting point. You have a yearly budget, but it’s easier to do a month on month projection for all the months of the year, i.e., January to December.
For example, if your annual budget for a specific service, e.g., cleaning service was $ 7,200, you can divide it by 12 months to get $ 600 for every month then multiply it by 1.2 (to cater for inflation) to get a new budget of $720 for every month. You can round the numbers to 2 digits for uniformity.
Once you do this for all your numbers, your data is ready to be imported into QuickBooks. However, before importing, you should ensure that your excel worksheet is in an importable format.
To set up an importable template, go back to the QuickBooks application, click on File, then select utilities, choose the export option, click on lists to IIF files, click budgets, and then okay.
QuickBooks will give you an opportunity of saving the data on your computer. You can choose to save it on the desktop. You should see the IIF file that you saved on the desktop.
Understanding What Will Be Imported
Open the saved IIF file in excel by dragging and dropping the IIF file into a blank excel worksheet. It will open a different excel worksheet. You will have to fill the new excel with information from the excel worksheet with your budget details.
To do this, select all the account lists from the excel worksheet that has your budget. Copy, then paste them into your IIF excel file. If, after copying you, note that any extra account lists were copied from your budget excel worksheet, you can drag the first column in the IIF file that contains the word BUD header.
You then have to replace the zeros in the IIF excel worksheet with actual numbers from your excel budget worksheet. Copy the numbers from the original excel budget to the IIF excel. The IIF excel file will have a start date. Ensure the start date for all the rows is right. It should be the first day of the fiscal year you are working with. Clear the classes option on the excel worksheet because you are not working with that.
Doing the Actual Importation
Save the updated IIF file. Your saved File is ready for importation into QuickBooks. To import the File, open QuickBooks, go to file, then utilities, select Import, and then click on IIF file. Select your saved IIF file and import it into QuickBooks.
After successfully importing it, go to the company option on the QuickBooks application, then click on planning and budgeting, set up budgets, then you should see your entire imported budget. You can keep the template, modify it in excel, and keep importing it back into QuickBooks when necessary.
