Solution
Sandeep answered on
Mar 28 2022
Excel Online Structured Activity: Statement of cash flows
You have just been hired as a financial analyst for Ba
ington Industries. Unfortunately, company headquarters (where all of the firm's records are kept) has been destroyed by fire. So, your first job will be to recreate the firm's cash flow statement for the year just ended. The firm had $100,000 in the bank at the end of the prior year, and its working capital accounts except cash remained constant during the year. It earned $5 million in net income during the year but paid $700,000 in dividends to common shareholders. Throughout the year, the firm purchased $5.4 million of machinery that was needed for a new project. You have just spoken to the firm's accountants and learned that annual depreciation expense for the year is $450,000; however, the purchase price for the machinery represents additions to property, plant, and equipment before depreciation. Finally, you have determined that the only financing done by the firm was to issue long-term debt of $1 million at a 7% interest rate. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below.
Open spreadsheet
What was the firm's end-of-year cash balance? Recreate the firm's cash flow statement to a
ive at your answer. Write out your answer completely. For example, 5 million should be entered as 5,000,000. Round your answer to the nearest dollar, if necessary.
$Â Â fill in the blank 2
Statement of cash flows
Cash at the Beginning of Yea
$100,000.00
Change in Net Operating Working Capital Except Cash
$0.00
Net Income
$5,000,000.00
Common Dividends
$700,000.00
Machinery Equipment Purchases
$5,400,000.00
Depreciation Expense
$450,000.00
Long-Term Debt
$1,000,000.00
Interest Rate on Long-Term Debt
7.00%
Statement of Cash Flows
I. Operating Activities
Formulas
Net income
$5,000,000.00
#N/A
Depreciation expense
$450,000.00
#N/A
Change in net operating working capital except cash
$0.00
Net cash provided by (used in) operations
$5,450,000.00
#N/A
II. Long-Term Investing Activities
Additions to property, plant and equipment
-$5,400,000.00
#N/A
Net cash used in investing activities
-$5,400,000.00
#N/A
III. Financing Activities
Increase in long-term debt
$1,000,000.00
#N/A
Payment of common dividends
-$700,000.00
#N/A
Net cash provided by financing activities
$3,00,000.00
#N/A
IV. Summary
Net increase (decrease) in cash
$3,50,000.00
#N/A
Cash at beginning of yea
$100,000.00
Cash at end of yea
$4,50,000.00
#N/A
The Berndt Corporation expects to have sales of $12 million. Costs other than depreciation are expected to be 70% of sales, and depreciation is expected to be $1.8 million. All sales revenues will be collected in cash, and costs other than depreciation must be paid for during the year. Brendt's federal-plus-state tax rate is 40%. Berndt has no debt. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.
Open spreadsheet
a. Set up an income statement. What is Berndt's expected net...