Typically, there will be some reports that you will print over and over again, perhaps weekly, monthly, or even daily. For these types of reports, it is a good idea to create a template that can be updated with minimal variable data (usually a date), to reprint with new data each time.
The following are just sample steps; you can use the functions in any way that meets your business needs.
Allocate an area of your spreadsheet to contain the variable data. It is a good idea to place this at the beginning of your spreadsheet (or even on its own sheet), to make it easy for this data to be updated, before the report is generated each time. To allow room for future changes, you may want to allocate as many as 20 rows and 4 columns for this data.
Determine what your variable data will be. This could be different for each financial statement; typically the variable data would just be the dates and entity numbers.
Determine which accounts will be used in the financial statement. For example, if you are creating a Balance Sheet, you would want to include all of your balance sheet accounts.
Determine which, if any, of the accounts will be combined on the report. For example, you may combine several payroll liability accounts into a single entry on your balance sheet.
Begin setting up the report in Excel; set up the variable data entry as shown below.
Define the headings that will be displayed on your report. It is easiest to copy these headings directly from a sample financial statement; alternatively, enter any headings that are needed on your report. A heading is any text that displays on the report to identify the figures. An example is shown below.
Note: If your financial report will be more than one page, you can have the headings repeat on each page; see how to in Excel 2003, or in Excel 2007.
For each descriptive data heading, enter the accounts that provide the data. For example, on the sample P & L statement, Food sales includes values from accounts 4000 through 4030, and 4100 through 4130. An example is shown below.
Enter the formulas to pull the data from Compeat for each account. In this example, a template for a profit and loss statement is shown, so PL is the only function used.
In this example, each PL function will get its parameters as follows:
Restaurant # Use an absolute cell reference to cell B3.
Account # Use a relative cell reference to each account # entry.
Start Date Use an absolute cell reference to cell B7.
End Date Use an absolute cell reference to cell B8.
An example of entering the PL is shown below:
Note: If you use absolute references for the data entry values, and a relative reference for the account number, and your spreadsheet is setup similar to the example, you can enter the PL once, then copy it for each of the other accounts.
An example with all of the PL functions copied for each of the account number references is shown below.
Enter mathematical formulas to calculate the other numbers on the financial statement. For example, the Total Sales, Total COGS, and Gross Profit.
Set the print area for your financial statement. In this example, you would exclude the data entry section and the account # entry section.
Your template spreadsheet should be complete at this point. Depending on your business and circumstances, you may also want to add some validation, as described below.
The following validations are optional additions to a template and would only apply to certain business circumstances.
To verify that you have not omitted an account on P & L statements, which by definition should include all of each type of account, enter a formula similar to the following in a non-printing location:
PL(B3,"4000..9999", B7, B8)
This essentially sums the debits and credits in all P & L accounts. If this does not return a value equal to the Gross Profit figure calculated on your financial statement, you have omitted one or more accounts.
First check the total assets using the following formula:
CompeatBAL(B3,"1000..1999",B7)
This number should equal the summed value on your spreadsheet. Next, check your liabilities, and so on.
You should also check that your balance sheet balances to zero using a check similar to the following:
BAL(B3,"1000..1999",B7) - [BAL(B3,"2000..2999",B7) + BAL(B3,"3000..3999",B7) + PL(B3,"4000..9999",B7,B8)].
This sums your assets, then subtracts the sum of the liabilities, retained earnings, and current earnings. This should equal zero.
One additional check is to verify that the current earnings on the balance sheet equals the gross profit value from the profit and loss statement.