50.1K

Verified Solution

Link Copied!

a. Recreate the income statement and balance sheet using formulas where possible. Each statement should be on a separate worksheet. Try to duplic the formatting exactly. b. On another worksheet, create a statement of cash flows for 2020. Do n enter any numbers directly on this worksheet. All formulas should be link directly to the source on previous worksheets. c. Using Excel's outlining feature, create an outline on the statement of cas flows that, when collapsed, shows only the subtotals for each section. d. Suppose that sales were $425,000 in 2020 rather than $412,500. What is the 2020 net income and retained earnings? e. Undo the changes from part d, and change the tax rate to 20%. What is the 2020 net income and retained earnings? 2. Using the data from the previous problem: a. Create a common-size income statement for 2019 and 2020. This statement should be created on a separate worksheet with all formulas linked directly to the income statement. b. Create a common-size balance sheet for 2019 and 2020. This statement should be created on a separate worksheet with all formulas linked directly to the balance sheet. c. Create a common-size statement of cash flows for 2020 that can be switched between using sales and the 2019 cash balance in the denominator. A B C D Big Rock Candy Mountain Mining Co. Statement of Cash Flows For the Year Ended December 31, 2020 Cash Flows from Operations Net Income Depreciation Expense Change in Marketable Securities Change in Accounts Receivable Change in Inventory Change in Accounts Payable Change in Accrued Expenses Total Cash Flows from Operations Cash Flows from Investing Change in Gross Fixed Assets Total Cash Flows from Investing Cash Flows from Financing Change in Long-term Debt Change in Common Stock (\$2 par) Change in Additional Paid-in-Capital Total Cash Flows from Financing Net Change in Cash Balance 22 Income Statement Balance Sheet Statement of Cash Flows income Stater A BigRockCandyMountainMiningCo.B Income Statements For the Years 2019 and 2020 Sales Cost of Goods Gross Profit Depreciation Selling \& Admin. Expense Other Operating Expense Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income Notes: Tax Rate Shares Earnings per Share A B C Big Rock Candy Mountain Mining Co. Income Statements For the Years 2019 and 2020 2020 2019 Sales Cost of Goods Gross Profit Depreciation Selling \& Admin. Expense Other Operating Expense Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income Notes: Tax Rate Shares Earnings per Share a. Recreate the income statement and balance sheet using formulas where possible. Each statement should be on a separate worksheet. Try to duplic the formatting exactly. b. On another worksheet, create a statement of cash flows for 2020. Do n enter any numbers directly on this worksheet. All formulas should be link directly to the source on previous worksheets. c. Using Excel's outlining feature, create an outline on the statement of cas flows that, when collapsed, shows only the subtotals for each section. d. Suppose that sales were $425,000 in 2020 rather than $412,500. What is the 2020 net income and retained earnings? e. Undo the changes from part d, and change the tax rate to 20%. What is the 2020 net income and retained earnings? 2. Using the data from the previous problem: a. Create a common-size income statement for 2019 and 2020. This statement should be created on a separate worksheet with all formulas linked directly to the income statement. b. Create a common-size balance sheet for 2019 and 2020. This statement should be created on a separate worksheet with all formulas linked directly to the balance sheet. c. Create a common-size statement of cash flows for 2020 that can be switched between using sales and the 2019 cash balance in the denominator. A B C D Big Rock Candy Mountain Mining Co. Statement of Cash Flows For the Year Ended December 31, 2020 Cash Flows from Operations Net Income Depreciation Expense Change in Marketable Securities Change in Accounts Receivable Change in Inventory Change in Accounts Payable Change in Accrued Expenses Total Cash Flows from Operations Cash Flows from Investing Change in Gross Fixed Assets Total Cash Flows from Investing Cash Flows from Financing Change in Long-term Debt Change in Common Stock (\$2 par) Change in Additional Paid-in-Capital Total Cash Flows from Financing Net Change in Cash Balance 22 Income Statement Balance Sheet Statement of Cash Flows income Stater A BigRockCandyMountainMiningCo.B Income Statements For the Years 2019 and 2020 Sales Cost of Goods Gross Profit Depreciation Selling \& Admin. Expense Other Operating Expense Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income Notes: Tax Rate Shares Earnings per Share A B C Big Rock Candy Mountain Mining Co. Income Statements For the Years 2019 and 2020 2020 2019 Sales Cost of Goods Gross Profit Depreciation Selling \& Admin. Expense Other Operating Expense Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income Notes: Tax Rate Shares Earnings per Share

Answer & Explanation Solved by verified expert

See Answer