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
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
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
, describes this worksheet, and presents step by step
instructions for using it.
Worksheet 92 Trial Balance
, describes this worksheet, and provides information about adding
Worksheet 93 Final Trial
, 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.
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
,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.
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
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
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
, describes how to add the two unique items to the Income
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.
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.
8, Customizing the Financial Statements.xls Workbook and Presenting
This chapter explains how to use various techniques
to customize the Financial Statements.xls workbook according
to your needs, and includes the following sections:
a Custom Menu
, describes how to add Custom Menus to your worksheet.
, describes the advantages of using Custom Views.
the Workbook's File Path into the Title Bar
, describes how you can add the full path of the file to the
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.
Comments to Save and Show Data and Charts
, describes how to add, delete and print Comments in
your worksheet and use the Camera feature.
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:
, 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.
, 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
, 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.
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
- 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
, 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
, 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
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
, 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
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:
, 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
, 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
- 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
- Creating a PivotTable Report
, describes the steps required to create a new PivotTable
- 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
, 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
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
, 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
- Printing Profit Center Income
, describes how to automate the printing of Profit
Center Income Statement reports.