Scribd
Upload

Financial Statements and Solutions

193 views11 pages
The document discusses creating and analyzing financial statements including the income statement, balance sheet, and statement of cash flows using Excel. It provides sample data and formula…

Uploaded by

Lealith Cañete
  • Income Statement Solutions
  • Balance Sheet Solutions
  • Cash Flow Statement Solutions
  • Scenario Analysis Problems
  • Common-Size Statement Exercises

CHAPTER 2: THE BASIC FINANCIAL STATEMENTS

Text Problem Solutions

1. Using the data presented below:


a. Recreate the income statement and balance sheet using formulas wherever possible. Each
statement should be on a separate worksheet. Try to duplicate the formatting exactly.

Original Data:

33
34 CHAPTER 2: THE BASIC FINANCIAL STATEMENTS
Text Problem Solutions

Formulas:
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS 35
Text Problem Solutions

Original Data:
36 CHAPTER 2: THE BASIC FINANCIAL STATEMENTS
Text Problem Solutions

Formulas:

b. On another worksheet, create a statement of cash flows for 2009. Do not enter any numbers on
this worksheet. All formulas should be linked directly to the source on previous worksheets.
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS 37
Text Problem Solutions

Worksheet:
38 CHAPTER 2: THE BASIC FINANCIAL STATEMENTS
Text Problem Solutions

Formulas:
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS 39
Text Problem Solutions

c. Using Excel’s outlining feature, create an outline on the statement of cash flows that, when
collapsed, shows only the subtotals for each section.

Worksheet:

Collapsed Worksheet:
40 CHAPTER 2: THE BASIC FINANCIAL STATEMENTS
Text Problem Solutions

d. Suppose that sales were $3,200,000 in 2009 rather than $2,900,000. What is the 2009 net
income and retained earnings?
Net Income: cell B15 Retained Earnings: cell B13

e. Undo the changes from part d, and change the tax rate to 40%. What is the 2009 net income
and retained earnings?
Net Income: cell B15 Retained Earnings: cell B24
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS 41
Text Problem Solutions

2. Using the data from the previous problem:


a. Create a common-size income statement for 2008 and 2009. This statement should be created
on a separate worksheet with all formulas linked directly to the income statement.

Worksheet:

Formulas:
42 CHAPTER 2: THE BASIC FINANCIAL STATEMENTS
Text Problem Solutions

b. Create a common-size balance sheet for 2008 and 2009. This statement should be created on a
separate worksheet with all formulas linked directly to the balance sheet.

Worksheet:
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS 43
Text Problem Solutions

Formulas:

Common questions

Powered by AI

Financial statement formulas facilitate scenario testing by designing models that dynamically adjust based on input changes. Modifying variables like the tax rate or sales figures automatically recalibrates dependent outcomes, such as net income or retained earnings, allowing analysts to explore various hypothetical impacts efficiently .

Income statements and balance sheets can be reconstructed using formulas by creating models that compute each line item based on underlying economic events and assumptions. This method is beneficial as it ensures cohesion and automatic updates throughout financial analytics processes while reducing the potential for manual errors .

An increase in sales typically leads to higher net income, assuming costs and expenses do not increase at a comparable rate. Consequently, this would also raise retained earnings, as calculated in cells B15 and B13, reflecting greater profitability and potentially more capital for reinvestment or dividend distribution .

Linking formulas directly to source data ensures accuracy and integrity in financial reporting by automatically updating calculations when source values change. This practice minimizes errors and maintains consistent data flow across worksheets .

Common-size financial statements convert financial data to percentages of a common number, providing a standardized approach to analyzing trends over time. They allow for easy comparison by highlighting proportional relationships, making it clearer to identify shifts in cost structures or revenue distribution .

Separating different sections of financial statements into individual worksheets aids in organization, making it easier to manage and analyze specific datasets. This structure improves clarity, enabling focused analysis on distinct financial components while maintaining linked relationships for comprehensive review .

Replicating the formatting exactly when recreating financial statements ensures consistency and comparability with standard templates or company reports, enhancing readability and professionalism. It also helps stakeholders quickly interpret financial data in expected formats .

Increasing the tax rate to 40% reduces the company’s net income since taxes directly decrease profitability. Specifically, net income and retained earnings would decline from their originally calculated values based on the higher tax expense, as visible in cell B15 and B24 .

Excel's outlining feature allows users to organize data into a hierarchical view so that sections of the statement of cash flows can be expanded or collapsed. This is useful for summarizing information, as it enables users to initially view only the subtotals for each section, providing a cleaner, more comprehensible overview .

Showing only subtotals in a collapsed statement of cash flows simplifies the overview presentation, allowing stakeholders to focus on the aggregate impacts of operational, investment, and financing activities without getting bogged down in details. This clarity helps quickly assess comprehensive cash flow positions .

33 CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 1. Using the data presented below: a. Recreate th
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 34 Formulas:
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 35 Original Data:
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 36 Formulas: b. On another worksheet, create a
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 37 Worksheet:
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 38 Formulas:
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 39 c. Using Excel’s outlining feature, create an ou
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 40 d. Suppose that sales were $3,200,000 in 2009 ra
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 41 2. Using the data from the previous problem: a.
CHAPTER 2: THE BASIC FINANCIAL STATEMENTS Text Problem Solutions 42 b. Create a common-size balance sheet for 2008 a

You might also like