Part One: Creating the Financial Statements.xls Workbook

This part contains ten chapters. It starts with an explanation of what each worksheet in the Financial Statements.xls workbook contains, and then moves on to discussing adding and automatically updating adjustments to Trial Balance, updating the Trial Balances data worksheet and creating Financial Statements worksheets reports. It also provides information regarding automating Notes numbers, customizing the Financial Statements.xls workbook by adding custom menus for easier navigation, operating macros that print and mail the Financial Statements reports while protecting the financial data, and much more.

Chapter 1 - Introducing Financial Statements.xls Worksheets

This chapter introduces the structure of the Financial Statements.xls workbook and its worksheets, and includes the following sections:

•   Financial Statements.xls Worksheets, describes the different categories into which the various worksheets are sorted, and provides details about each of them.
•   Sorting Worksheets in Ascending Order, provides macros that enable you to sort the worksheets to organize them in ascending order in the Financial Statements.xls workbook.

Chapter 2, Adjusting the Trial Balance

This chapter introduces techniques to add or update adjustments to the account balances of the General Ledger Trial Balance, and prepare a final adjusted Trial Balance to be used for preparing the financial statements. It includes the following sections:

•   Worksheet 91 Trial Balance (Original) , describes this worksheet, and presents step by step instructions for using it.
•   Worksheet 92 Trial Balance Adjustments , describes this worksheet, and provides information about adding new adjustments.
•   Worksheet 93 Final Trial Balance , describes this worksheet, and provides two ways of consolidating the Trial Balance account figures with the adjustment account figures.
•   Worksheet 94 Trial Balance Audit , describes this worksheet, and discusses how to summarize the data in it.

Chapter 3, Updating the Trial Balances Data Worksheet

This chapter describes how to add the account balances of the final Trial Balance  to the appropriate column in worksheet 21 Trial Balances Data, and includes the following sections:

•   Finding and Adding New Account Details ,describes how to find and add new account detail information that appears in the final Trial Balance and is not in worksheet 21 Trial Balances Data.
•   Entering Final Trial Balances to a New Column in Worksheet 21 – Trial Balances Data, describes how to add the account balances of the new final Trial Balance to a new column in the worksheet.

Chapter 4, Balance Sheet

This chapter provides step-by-step instructions for creating a Balance Sheet report, which is the major report in the Financial Statement, and includes the following sections:

•   Creating a Balance Sheet Report in an Excel Worksheet , provides step-by-step instructions for creating the Balance Sheet report using an Excel worksheet.
•   Presenting Two Different Periods , describes how to add a Combo Box to the worksheet to enable comparing different periods.
•   Auditing the Balance Sheet Figures , describes how to audit and balance the figures in the completed Balance Sheet.

Chapter 5, Income Statement

This chapter explains how to create the Income Statement report using Excel's worksheet, and includes the following sections:

•   Creating an Income Statement Report in an Excel Worksheet , describes how to create the Income Statement report  using Excel's worksheet.
•   Adding Unique Items to the Income Statement Report , describes how to add the two unique items to the Income Statement report.

Chapter 6, Cash Flow

This chapter explains how to create Cash Flow reports using an Excel worksheet, and includes the following sections:

•   Sources and Uses of Cash , presents the sources and uses of cash.
•   Creating a Cash Flow Report in an Excel Worksheet , describes the steps needed to create a Cash Flow report using an Excel worksheet.

Chapter 7, Notes

This chapter explains the Notes worksheet, and includes the following sections:

•   Creating the Notes Worksheet , describes the steps needed to create and structure the Notes worksheet, as well as how to add or update Notes.

Chapter 8, Customizing the Financial Statements.xls Workbook and Presenting Information

This chapter explains how to use various techniques to customize the Financial Statements.xls workbook according to your needs, and includes the following sections:

•   Creating a Custom Menu , describes how to add Custom Menus to your worksheet.
•   Using Custom Views , describes the advantages of using Custom Views.
•  Inserting the Workbook's File Path into the Title Bar , describes how you can add the full path of the file to the title bar.

•   Using the Watch Window , describes the new and exciting feature in Excel 2002 and higher that enables you to view cell location, formula, value or data in any cell in any open workbook, as well as move between cells in any open worksheet.

•  Using Comments to Save and Show Data and Charts , describes how to add, delete and print Comments in your worksheet and use the Camera feature.

Chapter 9, Protecting Financial Data

This chapter reviews all of the various options you can use to protect your financial information data and files, and includes the following sections:

•  Security , describes the new Security tab in Excel 2002 and later, as well as how to add a password or digital signature to a file.
•  Protecting Workbooks , describes how to protect a workbook so that its structure cannot be changed, as well as how to hide worksheets.

•   Protecting Worksheets/Cells , describes how to protect the content in cells from being changed, as well as hide the formulas in them from being viewed.

•  Protecting Cells That Contain Formulas or Text , describes how to protect only those cells in the workbook that contain text or formulas.

•  Allowing Multiple Users to Edit Ranges , describes a new functionality in Excel 2002 and later, in which you can enable multiple users to update data in a well‑defined and private area of the worksheet.

Chapter 10, Printing and Mailing Financial Statements Reports

This chapter discusses the techniques you need to print or mail full Financial Statements reports, and includes the following sections:

•  Changing and Customizing the Default Settings in a Workbook , describes various techniques you can use to customize your Excel workbook for printing purposes, for example, by adding the date and time into the header, adding a company logo and scaling the print out so that all columns appear on one page.
•  Other Important Printing Techniques , describes other important techniques that may be useful when printing workbooks, for example, selecting a print area, printing Comments and hiding data.
•  Custom Views , describes Excel's Custom Views technique, which enables you to save a set of print setup options that is unique for each print area in the worksheet, as well as create a menu of Custom Views.
•   Report Manager , describes the Report Manager add-in, which lets you create and save as many reports as you need for future printing, as well as how to create an Custom Report Manager by adding a VBA macro.
•  Mailing Financial Statements Reports , describes how to e-mail full Financial Statements reports from a new saved and formatted workbook.
  

Part Two: Analyzing Financial Statements and Creating Management Financial Reports

This part contains five chapters. It provides detailed explanations regarding how to create and present five-year comparison Balance Sheet and Income Statement reports and prepare Income Statements for monthly or quarterly ending reporting periods while dealing with fiscal year-end reporting periods. It also discusses how to use PivotTable reports to prepare and analyze Income Statement reports by Profit Centers, and to prepare the ratio analysis figures for managements needs and decision makers, both inside and outside the firm.

Chapter 11, Balance Sheet Five-year Comparison Reports

In this chapter, you will learn how to use the reports already created in the previous chapters to create the Balance Sheet five-year comparison report, and includes the following sections:

•  Five-year Balance Sheet Comparison Report , describes the five-year Balance Sheet comparison report, as well as how to round the numbers to the thousands and troubleshoot calculation errors due to the rounding.

Chapter 12, Income Statement Five year and Quarterly Comparison Reports

In this chapter, you will learn how to use the Income Statement report already created in Chapter 5, Income Statement to create the Income Statement five-year comparison report and quarterly comparison reports, and includes the following sections:

•  Overview , presents the purpose of and need for creating an Income Statement report that compares results from five years of business activities and compares results over the last four quarters.
•  Five-year Income Statement Comparison Reports , describes the creation of a five-year Income Statement report.
•  Quarterly Income Statements , describes the creation of comparison quarterly Income Statements, as well as how to deal with the calendar or fiscal year end reporting period.
•  Creating Charts Easily and Quickly , describes how to quickly create charts to display the comparison balances of the Income Statement report.

Chapter 13, Analyzing Financial Statements Using PivotTable and PivotChart Reports

This chapter describes how to use PivotTable and PivotChart reports to analyze the company's Financial Statements. It contains examples and step-by-step instructions on how to create PivotTable and PivotChart reports, and then analyze them while summarizing the balances of the General Ledger Trial Balance accounts. The following sections are included:

•  Saving Trial Balance Accumulated Balances in Different Structures , describes how to create PivotTable reports from Trial Balances data stored in Excel worksheets, using two different storage structures.
•  Creating a PivotTable Report , describes the steps required to create a new PivotTable report.
•  Using a PivotTable Report to Summarize Balances Used in Financial Statements Reports , describes how to create and save a PivotTable report that retains a fixed structure of PivotTable Fields, which is useful in preparing Financial Statements.

Chapter 14, Analyzing Financial Statements and Calculating the Ratio Analysis

This chapter introduces techniques for Financial Statement Analysis, and includes the following sections:

•  Overview , introduces the need for analyzing Financial Statements and calculating the Ratio Analysis.
•  Analyzing Financial Reports , provides examples of the Two year Balance Sheet and Two-year Income Statement Comparison Reports.
•  Ratio Analysis , describes the four different categories of Ratio Analysis, and provides formulas for each ratio included in them.

Chapter 15, Analyzing Profit Centers

This chapter describes how to create the company's Profit Centers' Income Statement reports, and includes the following sections:

•  Adding Profit Center Details to the Trial Balances Data Worksheet , describes how to add a Profit Center details in a new column to the Trial Balances Data.
•  Analyzing Profit Centers Using PivotTable Reports , describes how to create PivotTable reports to analyze Profit Center by Profit Center while presenting summary balances for Account Name and Account Type.
•  Creating an Income Statement to Present Each Profit Center's Results , describes how to create Profit Center Income Statement reports.
•  Printing Profit Center Income Statement Reports , describes how to automate the printing of Profit Center Income Statement reports.