Part III Exercise

Balance Sheet Report

See the companion files (www.wordware.com/files/crystal) for an example of this report. Create this report with the Standard Report Creation Wizard. This report is created with the xtreme.mdb database.

Table(s)	Fields
Financial	Statement Date Cash Account Receivable Inventories Other Current Assets Land Buildings Machinery etc. Accumulated Depreciation Other Assets Accounts Payable Accrued Liabilities Accrued Income Taxes Notes Payable Deferred Income Taxes Preferred Stock Common Stock Retained Earnings

Since there is only one table, you won't have to worry about linking multiple tables. Once you've selected your table and fields within the Standard Report Creation Wizard, perform the following:

1. In the Grouping screen, create a group for every field except the Statement Date field.

   Your screen should look like figure P3-1. Once you've defined these groups, press the Next button. The Summaries screen displays.

   
   Figure P3-1: Defining groups within your Balance Sheet report

2. In the Summaries screen, leave everything as default.

   Press the Next button. The Group Sorting screen displays.

3. In the Group Sorting screen, leave everything as default.

   Press the Next button. The Chart screen displays.

4. In the Chart screen, leave everything as default.

   Press the Next button. The Record Selection screen displays.

5. In the Record Selection screen, press the Next button.

   You do not need to create any record selection statements at the moment. After pressing the Next button, the Template screen displays.

6. In the Template screen, select the No Template option.

   Press the Finish button. Your screen should look like Figure P3-2.

   
   Figure P3-2: Creating a Balance Sheet report with the Standard Report Creation Wizard

Continue with the following steps to continue defining your Balance Statement report:

1. From the Field Explorer, right-click on the Parameter Fields node.

   An options menu displays.

2. From this options menu, select the New option.

   The Create Parameter Field dialog box displays.

3. Define your parameter as follows:

   • In the Name field, type the following: Report Period.

   • In the Prompting text field, type the following: Please define your reporting period:.

   • In the Options section, select the Range value(s) option.

   Your screen should look like Figure P3-3.

   
   Figure P3-3: Defining a report parameter

4. Press the OK button.

   You're returned to the Report Design area.

5. From the menu bar, select Report, Select Expert.

   The Choose Field dialog box displays.

6. Select the Statement Date field within the Choose Field dialog box.

   Once you've done this, press OK. The Select Expert dialog box displays.

7. Define your select statement as follows:

   • In the Comparison operator drop-down field, select Is Equal To.

   • In the Comparison value drop-down field, type the following: {?Report Period}.

   The Enter Parameter Values dialog box displays.

8. Type in the following report period in the Enter Parameter Values dialog box: 03/01/2001 to 03/31/2001.

   This dialog box should look like Figure P3-4.

   
   Figure P3-4: Entering a report date range for your report

Once you've pressed OK, you're asked if you want to use saved data or refresh your data. Select to refresh your data. Your report refreshes, and you're returned to the Report Design area. Let's now focus on the format of your report.

1. Within the Group Headers, align all fields at the 3.5" mark. Stretch these fields so that all data displays.

2. Drag all headings within the Page Headers into their respective Group Header sections.

   For example, the Cash header is placed in Group Header 1, the Accounts Receivable header is placed in Group Header 2, etc.

   Align the right edge of the headers at the 3" mark. Then stretch the left edge of the header to the 1.5" mark.

   Once you've done this, change the alignment so that all headers are left aligned.

   Finally, double-click into the Account Receivable field and rename it Accounts Receivable.

3. Suppress all Group Footer sections. Also, suppress the Report Footer section.

4. Select all headers and fields. Change their font size to 8.

   Your screen should look like Figure P3-5.

   
   Figure P3-5: Formatting the Balance Sheet report

5. From the menu bar, select Report, Formula Workshop.

   The Formula Workshop displays.

6. Right-click on the Formula Fields folder.

   An options menu displays.

7. From this options menu, select New.

   The Formula Name dialog box displays. In this dialog box, type Total Current Assets. Then, press the Use Editor button. The Formula Editor displays.

8. Type the following code within the formula edit window:

      {Financials.Cash} +   {Financials.Account Receivable} +   {Financials.Inventories} +   {Financials.Other Current Assets} 

9. Check your formula for errors.

   If no errors are found, press the Save button.

While you're in the Formula Editor, create the rest of the formulas you'll need for this report. These are summarized in the following table. To create a new formula, right-click on the Formula Fields folder. Make sure you check for any errors, and save your work before creating a new formula.

Table P3-1: Additional formulas of the Balance Sheet report

Formula Name	Formula
Net Fixed Assets	{Financials.Land}+ {Financials.Buildings}+ {Financials.Machinery etc}+ {Financials.Accumulated Depreciation}
Total Fixed and Other Assets	{Financials.Land}+ {Financials.Buildings}+ {Financials.Machinery etc}+ {Financials.Accumulated Depreciation}+ {Financials.Other Assets}
Total Assets	{Financials.Cash}+ {Financials.Account Receivable}+ {Financials.Inventories}+ {Financials.Other Current Assets}+ {Financials.Land}+ {Financials.Buildings}+ {Financials.Machinery etc}+ {Financials.Accumulated Depreciation}+ {Financials.Other Assets}
Total Current Liabilities	{Financials.Accounts Payable}+ {Financials.Accrued Liabilities}+ {Financials.Accrued Income Taxes}
Total Long Term Liabilities	{Financials.Notes Payable} + {Financials.Deferred Income Taxes}
Total Shareholders' Equity	{Financials.Preferred Stock}+ {Financials.Common Stock}+ {Financials.Retained Earnings}
Total Equity and Liabilities	{Financials.Accounts Payable}+ {Financials.Accrued Liabilities}+ {Financials.Accrued Income Taxes}+ {Financials.Notes Payable}+ {Financials.Deferred Income Taxes}+ {Financials.Preferred Stock}+ {Financials.Common Stock}+ {Financials.Retained Earnings}

Once you've created these formulas, add them to the following areas of your report, as defined by Table P3-2. If you have any confusion, check out Figure P3-6 to see what your screen should look like when finished.

Table P3-2: Adding the formulas to your report

Formula Name	Location	Text Object	Formatting
Total Current Assets	Stretch the Group Header 4 section. Place the formula at the 2-inch mark.	Create a text object with the following text: Total Current Assets. Place this object in line with the formula, starting at the 0-inch mark.	Bold, Left Align, and change font color to navy for both text object and formula.
Net Fixed Assets	Stretch the Group Header 8 section. Place the formula at the 2-inch mark.	Create a text object with the following text: Net Fixed Assets. Place this object in line with the formula, starting at the 0-inch mark.	Bold, Left Align, and change font color to navy for both text object and formula.
Total Fixed and Other Assets	Stretch the Group Header 9 section. Place the formula at the 2-inch mark.	Create a text object with the following text: Total Fixed and Other Assets. Place this object in line with the formula, starting at the 0-inch mark.	Bold, Left Align, and change font color to navy for both text object and formula.
Total Assets	Place the formula at the 2-inch mark, slightly under the Total Fixed and Other Assets formula.	Create a text object with the following text: Total Assets. Place this object in line with the formula, starting at the 0-inch mark.	Bold, Left Align, and change font color to maroon for both text object and formula.
Total Current Liabilities	Stretch the Group Header 12 section. Place the formula at the 2-inch mark.	Create a text object with the following text: Total Current Liabilities. Place this object in line with the formula, starting at the 0-inch mark.	Bold, Left Align, and change font color to navy for both text object and formula.
Total Long Term Liabilities	Stretch the Group Header 14 section. Place the formula at the 2-inch mark.	Create a text object with the following text: Total Long Term Liabilities. Place this object in line with the formula, starting at the 0-inch mark.	Bold, Left Align, and change font color to navy for both text object and formula.
Total Shareholders' Equity	Stretch the Group Header 17 section. Place the formula at the 2-inch mark.	Create a text object with the following text: Total Shareholders' Equity. Place this object in line with the formula, starting at the 0-inch mark.	Bold, Left Align, and change font color to navy for both text object and formula.
Total Equity and Liabilities	Place the formula at the 2-inch mark, slightly under the Total Shareholders' formula.	Create a text object with the following text: Total Equity and Liabilities. Place this object in line with the formula, starting at the 0-inch mark.	Bold, Left Align, and change font color to maroon for both text object and formula.

Figure P3-6: Adding formulated totals to the Balance Sheet report

Figure P3-6 displays the results of your work so far.

To complete this report, add a title (Consolidated Balance Sheet) and place it in the Page Header section at the top of your report. This title should be bold and in a font size of around 18.

Also, delete the print date and replace with the Statement Date field. Change the Statement Date field so that it displays in a format of mm/dd/yyyy. Figure P3-7 displays the final version of the Consolidated Balance Sheet report.

Figure P3-7: The final version of the Consolidated Balance Sheet report