I need help doing an income statement (Part A) and cash flow statement (Part B) showing relevant subtotals in Excel. The answer can be in Excel or a Word document that I will transfer to Excel so long as the formulas are explained step-by-step. I have attached the assignment and course reading for additional information.
PART A
| Prepare a formal income statement that contains the relevant subtotals. Show values in whole dollars (no cents). |
| Data Block |
Amount |
|
|
|
|
|
Cash (Beginning of year) |
$503,500 |
|
|
|
|
|
Quantity Sold |
35,200 |
|
|
|
|
|
Quantity Produced |
63,000 |
|
|
|
|
|
Sales Price |
$45.00 |
|
|
|
|
|
Cost per unit to produce |
$25.40 |
|
|
|
|
|
Staff Expenses |
$426,400 |
|
|
|
|
|
Facility Expenses |
$278,200 |
|
|
|
|
|
Asset Sale |
$1,755,300 |
|
|
|
|
|
Asset Purchase |
$2,205,000 |
|
|
|
|
|
Tax rate |
25% |
|
|
|
|
|
Depreciation |
$28,000 |
|
|
|
|
|
Interest Paid on Loan |
$63,000 |
|
|
|
|
|
Loan Principle payment |
$152,000 |
|
|
|
|
|
Dividends |
$108,000 |
|
|
|
|
|
|
|
|
|
|
|
PART B
| Assignment Week 05 |
|
|
|
Score |
|
|
|
| Prepare a cash flow statement using the following data. Assume that no taxes are due on asset purchases or sales. Prepare a formal cash flow statement that contains the relevant subtotals. Show values in whole dollars (no cents). Indicate the ending cash balance. |
|
| Data Block |
Amount |
|
|
|
|
|
|
|
Cash (Beginning of year) |
$360,600 |
|
|
|
|
|
|
|
Net Income |
$598,000 |
|
|
|
|
|
|
|
Staff Expenses |
$764,200 |
|
|
|
|
|
|
|
Facility Expenses |
$887,200 |
|
|
|
|
|
|
|
Asset Sale |
$156,530 |
|
|
|
|
|
|
|
Asset Purchase |
$1,240,400 |
|
|
|
|
|
|
|
Tax rate |
22.5% |
|
|
|
|
|
|
|
Depreciation |
$89,500 |
|
|
|
|
|
|
|
Interest Paid on Loan |
$76,000 |
|
|
|
|
|
|
|
Loan Principle payment |
$50,000 |
|
|
|
|
|
|
|
New Loan |
$500,000 |
|
|
|
|
|
|
|
Dividends Paid |
$100,000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Working Capital Worksheet |
|
|
|
|
|
|
Beginning |
Ending |
Change |
|
|
|
|
|
Inventory |
$84,000 |
$87,000 |
|
|
|
|
|
|
Accounts Receivable |
$78,000 |
$73,000 |
|
|
|
|
|
|
Accounts Payable |
($75,000) |
($78,000) |
|
|
|
|
|
|
|
|
Total |
|
|
|
|
|