Tutorial A-1.Designing a Balance Sheet
Now that you’ve learned the important aspects of the balance sheet, let’s get started with creating one. This is going to be more involved than the other tutorials in the book. Each step will have more in-depth explanations listed after it before moving to the next step. Please make sure that you have plenty of time available so that you can go through each step slowly and fully comprehend it before moving to the next step.
- Before creating the report, you need to fully understand the database you are reporting from. Identify the tables which hold account information, account classifications and the journal entries. This may require talking to the database administrator or someone in the accounting department.
Earlier in the chapter, we looked at three examples of accounting databases and discussed their pros and cons. For this tutorial, we are going to use the updated version of the Xtreme.mdb database installed with Crystal Reports XI R2. From the analysis we did previously, we can identify the following tables and fields which will be used on the balance sheet. They are listed in Table A-4.
Table A-4. Balance sheet tables and fields.
| Table | Field Name | Description |
|---|---|---|
| Account Type | Account Type ID | Primary key for table. |
| Account Type | “Asset”, “Liability”, etc. Used as the main grouping field. | Account Heading |
| Account Heading Number | Primary key for table. | Account Heading Name |
| The account sub-category. “Current Assets”, “Sales Revenue”, etc. | Account | Account Number |
| Primary key. Will also be used for sorting. | Account Name | The account name. |
| Journal Entry | Date | Journal entry date. |
| Amount | Transaction amount. | Debit or Credit |
| Classifies the account as a debit or credit. |
The majority of these tables use their fields for creating the report groups. The Journal Entry table has the detailed information for creating the main content of the report. The Date field is used for filtering the records. The Amount field will be summed for the account balance.
- Now that you’re familiar with the database and which tables we’ll be reporting from, let’s create the report. Open Crystal Reports and choose Blank Report.
- A new report opens and the Database Expert is displayed. Under the Create New Connection folder, locate the Access/Excel (DAO) option and click it. This opens the Access/Excel (DAO) dialog box.
- For the Database Name, enter the path where you have the new Xtreme.mdb database file located. Click the Finish button to return to the Database Expert.
The majority of the balance sheet is going to be based on formulas and you can’t create formulas until after the report wizard finishes. Right now we’ll specify the tables used in the report, and wait till later to create the formulas.
- Select the following tables for the report: Account, Account Heading, Account Type, Account Class and Journal Entry.
- Click on the Links tab and the defaults links should be correct. Check yours against the next figure to see if they match. Correct them if necessary.
- Once the table links are correct, click the OK button to close the Database Expert. You are now in design mode with an empty report. The next step is to build the formulas used in the report.
