Difference between revisions of "How to Generate Net Income and Expense Budgets in a Spreadsheet"
Jump to navigation
Jump to search
(17 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
==Budgeted Net Income Spreadsheet== | ==Budgeted Net Income Spreadsheet== | ||
− | To generate this report you should use the Budget Report in GnuCash that was used to produce the Budget Comparison Report. | + | To generate this report you should use the Budget Report in GnuCash that was used to produce the Budget Comparison Report. One thing you may want to change is One Time Income and One Time Expenses. By going to the Budget Report Options you can deselect One Time Income and One Time Expenses because they are not included in the budget. |
*Copy budget report (copy function is located on the Edit menu located on the task bar) and paste on a different spreadsheet in the same workbook as the Budget Comparison Report. | *Copy budget report (copy function is located on the Edit menu located on the task bar) and paste on a different spreadsheet in the same workbook as the Budget Comparison Report. | ||
− | *Change any colored text to the default black, and remove any underlining. | + | *Change any colored text to the default black, and remove any underlining by clicking on the underlining icon twice on the task bar. |
− | *Delete all columns containing actual information, leaving only budgeted numbers. You may also want to delete the columns pertaining to budgeted information that is already included in the Budget Comparison Report. This information is past data that has already occurred and not needed for the budget. | + | *Delete all columns containing actual information, leaving only budgeted numbers. You may also want to delete the any columns pertaining to budgeted information that is already included in the Budget Comparison Report. This information is past data that has already occurred and not needed for the budget. |
*If the report contains subtotals for parent accounts, make sure those subtotals are highlighted in some way to differentiate them from the totals. For example, if you chose to contain the subaccounts for Sales under Income, then you would highlight the subaccounts and the subtotals by either making the background gray (a light gray to make sure the numbers are readable once the report is printed), italicizing the font, making the font smaller, or all of the above. | *If the report contains subtotals for parent accounts, make sure those subtotals are highlighted in some way to differentiate them from the totals. For example, if you chose to contain the subaccounts for Sales under Income, then you would highlight the subaccounts and the subtotals by either making the background gray (a light gray to make sure the numbers are readable once the report is printed), italicizing the font, making the font smaller, or all of the above. | ||
*Delete numerical data in the rows pertaining to “Income” and “Expenses.” This is to help relieve confusion. The totals will be included in the end. | *Delete numerical data in the rows pertaining to “Income” and “Expenses.” This is to help relieve confusion. The totals will be included in the end. | ||
*After the last income account, insert 2 rows. | *After the last income account, insert 2 rows. | ||
− | *In the first row directly under last income account type “Total Budgeted Income” in column A. In the row under the last expense account type “Total Budgeted Expenses” in column A. After that skip a row and type “Total Net Income” in column A. | + | *In the first row directly under last income account type “Total Budgeted Income” in column A. |
− | + | *In the row under the last expense account type “Total Budgeted Expenses” in column A. After that skip a row and type “Total Net Income” in column A. | |
− | **Total Budgeted Income = sum of all income parent accounts. NOTE: do not include the subtotals for the parent accounts. Only use the total in each of the parent accounts. This will ensure that you do not add a number twice in the total. | + | *Rows containing totals, such as Total Budgeted Income and Total Budgeted Expenses and Total Budgeted Net Income, should have bold text (including words and numbers in the row) to make this information stand out. |
− | **Total Budgeted Expenses = sum of all expense parent accounts. NOTE: do not include the subtotals for the parent accounts. Only use the total in each of the parent accounts. This will ensure that you do not add a number twice in the total. | + | *Calculations: |
− | **Total Budgeted Net Income = Total Budgeted Income – Total Budgeted Expenses | + | **'''Total Budgeted Income''' = sum of all income parent accounts. NOTE: do not include the subtotals for the parent accounts. Only use the total in each of the parent accounts. This will ensure that you do not add a number twice in the total. |
+ | **'''Total Budgeted Expenses''' = sum of all expense parent accounts. NOTE: do not include the subtotals for the parent accounts. Only use the total in each of the parent accounts. This will ensure that you do not add a number twice in the total. | ||
+ | **'''Total Budgeted Net Income''' = Total Budgeted Income – Total Budgeted Expenses | ||
*Name sheet "Net Income" or "Budgeted Net Income" and save. | *Name sheet "Net Income" or "Budgeted Net Income" and save. | ||
+ | |||
+ | ==Budgeted Expense Spreadsheets== | ||
+ | *The expense budget spreadsheets will use information generated using GnuCash and then will be placed in spreadsheet form for a more readable budget format. Create sheets in the same workbook as the Budget Comparison Report and the Net Income Budget labeled as the following: | ||
+ | **Action | ||
+ | **C7 | ||
+ | **HR and Kbees | ||
+ | **Outreach and Propagation | ||
+ | **Reuse and Technocrats | ||
+ | *Some committees are paired with other committees for printing purposes. The paired committees are small and if printed individually would waste space and paper. Single committee sheets are larger committees that take up more space. However, if you want to have a separate sheet for each committee just follow the instructions for the single committee budget sheet. | ||
+ | |||
+ | *Open GnuCash | ||
+ | **Open the budget report used to generate the Budget Comparison Report and the Net Income Budget. | ||
+ | **Select Options located on the task bar. | ||
+ | **Click on the "Accounts" tab. | ||
+ | **Click on "Clear All" located at the bottom of the Options window. | ||
+ | **Open up the committee account(s) for the spreadsheet you are making to as many levels as you want. In most cases, because this is supposed to be a detailed report, you want to open up to at least three levels. | ||
+ | **Click on the committee title to highlight. Then press shift+Down arrow key to highlight all remaining information included in the committee. | ||
+ | ***If you are creating a spreadsheet with two committees, the committees should be next to each other. Click on the committee title for the committee on the top, then press shift+Down arrow key to highlight all accounts in both committees. | ||
+ | **Once everything is highlighted select "Apply" located at the bottom of the Options window. | ||
+ | **Check to make sure the Budget Report contains all the information you want and that the format is also as you want. | ||
+ | ***If it is not correct then adjust as necessary using the Options menu. | ||
+ | **Once you are happy with the Budget Report, click on "OK" located at the bottom of the Options window. | ||
+ | **Highlight with the mouse all information in the Budget Report. | ||
+ | **Go to "Edit" on task bar and select "Copy," | ||
+ | |||
+ | *Open workbook containing the Budget Comparison Report and Net Income Budget. | ||
+ | **Open the corresponding sheet for the Budget Report you just copied. | ||
+ | **Right click in cell A1 and paste. | ||
+ | **Leave everything highlighted once pasted. Change all text to default black and undue underling by clicking on the underline button on the task bar twice. | ||
+ | **Now de-highlight everything | ||
+ | **Resize columns A through C to the left and make column D large enough that all text can be read. | ||
+ | **Delete all information containing actual expenses. You only need the budget data for these sheets. | ||
+ | **Resize columns where information can not be read because the columns are not wide enough. | ||
+ | **If you are making a sheet with two committees on it, under the last account category of the first committee on the sheet insert two rows. This is to make a clear boundary that the budgets are separate. | ||
+ | **Highlight entire row with committee title in it and make text bold. | ||
+ | ***The numbers in this row are the total expenses for the committee, so they should stand out from the rest of the numbers. | ||
+ | **Leave the next layer of accounts like they are. | ||
+ | **If the committee has a third layer of subaccounts then those rows need to be italicized and highlighted with a light gray. | ||
+ | ***These are subtotals of the second layer of subaccounts and should stand out to better clarify that they are subtotals of the unhighlighted row above it. | ||
+ | ***Make sure the gray is light so the numbers can be read easily once the sheets are printed out. | ||
+ | **After creating each sheet make sure you save. | ||
+ | |||
+ | [[Category:Budget]] |
Latest revision as of 13:13, 24 December 2009
Budgeted Net Income Spreadsheet
To generate this report you should use the Budget Report in GnuCash that was used to produce the Budget Comparison Report. One thing you may want to change is One Time Income and One Time Expenses. By going to the Budget Report Options you can deselect One Time Income and One Time Expenses because they are not included in the budget.
- Copy budget report (copy function is located on the Edit menu located on the task bar) and paste on a different spreadsheet in the same workbook as the Budget Comparison Report.
- Change any colored text to the default black, and remove any underlining by clicking on the underlining icon twice on the task bar.
- Delete all columns containing actual information, leaving only budgeted numbers. You may also want to delete the any columns pertaining to budgeted information that is already included in the Budget Comparison Report. This information is past data that has already occurred and not needed for the budget.
- If the report contains subtotals for parent accounts, make sure those subtotals are highlighted in some way to differentiate them from the totals. For example, if you chose to contain the subaccounts for Sales under Income, then you would highlight the subaccounts and the subtotals by either making the background gray (a light gray to make sure the numbers are readable once the report is printed), italicizing the font, making the font smaller, or all of the above.
- Delete numerical data in the rows pertaining to “Income” and “Expenses.” This is to help relieve confusion. The totals will be included in the end.
- After the last income account, insert 2 rows.
- In the first row directly under last income account type “Total Budgeted Income” in column A.
- In the row under the last expense account type “Total Budgeted Expenses” in column A. After that skip a row and type “Total Net Income” in column A.
- Rows containing totals, such as Total Budgeted Income and Total Budgeted Expenses and Total Budgeted Net Income, should have bold text (including words and numbers in the row) to make this information stand out.
- Calculations:
- Total Budgeted Income = sum of all income parent accounts. NOTE: do not include the subtotals for the parent accounts. Only use the total in each of the parent accounts. This will ensure that you do not add a number twice in the total.
- Total Budgeted Expenses = sum of all expense parent accounts. NOTE: do not include the subtotals for the parent accounts. Only use the total in each of the parent accounts. This will ensure that you do not add a number twice in the total.
- Total Budgeted Net Income = Total Budgeted Income – Total Budgeted Expenses
- Name sheet "Net Income" or "Budgeted Net Income" and save.
Budgeted Expense Spreadsheets
- The expense budget spreadsheets will use information generated using GnuCash and then will be placed in spreadsheet form for a more readable budget format. Create sheets in the same workbook as the Budget Comparison Report and the Net Income Budget labeled as the following:
- Action
- C7
- HR and Kbees
- Outreach and Propagation
- Reuse and Technocrats
- Some committees are paired with other committees for printing purposes. The paired committees are small and if printed individually would waste space and paper. Single committee sheets are larger committees that take up more space. However, if you want to have a separate sheet for each committee just follow the instructions for the single committee budget sheet.
- Open GnuCash
- Open the budget report used to generate the Budget Comparison Report and the Net Income Budget.
- Select Options located on the task bar.
- Click on the "Accounts" tab.
- Click on "Clear All" located at the bottom of the Options window.
- Open up the committee account(s) for the spreadsheet you are making to as many levels as you want. In most cases, because this is supposed to be a detailed report, you want to open up to at least three levels.
- Click on the committee title to highlight. Then press shift+Down arrow key to highlight all remaining information included in the committee.
- If you are creating a spreadsheet with two committees, the committees should be next to each other. Click on the committee title for the committee on the top, then press shift+Down arrow key to highlight all accounts in both committees.
- Once everything is highlighted select "Apply" located at the bottom of the Options window.
- Check to make sure the Budget Report contains all the information you want and that the format is also as you want.
- If it is not correct then adjust as necessary using the Options menu.
- Once you are happy with the Budget Report, click on "OK" located at the bottom of the Options window.
- Highlight with the mouse all information in the Budget Report.
- Go to "Edit" on task bar and select "Copy,"
- Open workbook containing the Budget Comparison Report and Net Income Budget.
- Open the corresponding sheet for the Budget Report you just copied.
- Right click in cell A1 and paste.
- Leave everything highlighted once pasted. Change all text to default black and undue underling by clicking on the underline button on the task bar twice.
- Now de-highlight everything
- Resize columns A through C to the left and make column D large enough that all text can be read.
- Delete all information containing actual expenses. You only need the budget data for these sheets.
- Resize columns where information can not be read because the columns are not wide enough.
- If you are making a sheet with two committees on it, under the last account category of the first committee on the sheet insert two rows. This is to make a clear boundary that the budgets are separate.
- Highlight entire row with committee title in it and make text bold.
- The numbers in this row are the total expenses for the committee, so they should stand out from the rest of the numbers.
- Leave the next layer of accounts like they are.
- If the committee has a third layer of subaccounts then those rows need to be italicized and highlighted with a light gray.
- These are subtotals of the second layer of subaccounts and should stand out to better clarify that they are subtotals of the unhighlighted row above it.
- Make sure the gray is light so the numbers can be read easily once the sheets are printed out.
- After creating each sheet make sure you save.