Please help me with this in Excel
Use the following Big Rock Candy Mountain Mining financial statements and copy the data into a new Excel workbook. Recreate the income statement and balance sheet using formulas wherever possible. Big Rock Candy Mountain Mining Co. Income Statements For the Years 2019 and 2020 2020 2019 Sales $412,500 $398,600 Cost of Goods 318,786 315,300 Gross Profit 93,714 83,300 Depreciation 29,800 29,652 Selling & Admin. Expense 26,250 24,550 Other Operating Expense 1,210 1,245 Net Operating Income 36,454 27,853 Interest Expense 8,582 8,457 Earnings Before Taxes 27,872 19,396 Taxes 6,968 4,849 Net Income 20,904 14,547 Notes: Tax Rate Shares Earnings per Share 25.00% 52,100 $0.40 25.00% 52,100 $0.28 Big Rock Candy Mountain Mining Co. Balance Sheet For the Year Ended December 31, 2020 2020 2019 Assets Cash $16,435 $11,596 Marketable Securities 3,656 619 Accounts Receivable 45,896 47,404 Inventory 52,397 54,599 Total Current Assets 118,384 114,218 Gross Fixed Assets 436,573 397,023 No1 Accumulated Depreciation Net Plant & Equipment Total Assets 87,450 57,650 349,123 339,373 467,507 453,591 Liabilities & Owner's Equity Accounts Payable Accrued Expenses Total Current Liabilities Long-term Debt Total Liabilities Common Stock ($1.00 par) Additional Paid-in-Capital Retained Earnings Total Owner's Equity Total Liab. & Owner's Equity $37,752 $36,819 3,183 3,085 40,935 39,904 170,562 178,581 211,497 218,485 58,664 58,664 136,807 136,807 60,539 39,635 256,010 235,106 467,507 453,591 Exhibit 3-6 Ratio 2020 2019 Liquidity Ratios Current XXX Quick XXX Efficiency Ratios Inventory Turnover A/R Turnover Average Collection Period days days Fixed Asset Turnover Total Asset Turnover Leverage Ratios Total Debt Ratio % Long-term Debt Ratio % % LTD to Total Capitalization Debt to Equity LTD to Equity Coverage Ratios Times Interest Earned Cash Coverage Ratio Profitability Ratios Gross Profit Margin Operating Profit Margin Net Profit Margin Return on Total Assets Return on Equity Return on Common Equity Answer the following questions: 1. On another worksheet, create a statement of cash flows for 2020. Do not enter any numbers directly on this worksheet. All formulas should be linked directly to the source of data from income statements and balance sheet worksheets. 2. Set up a ratio worksheet similar to the one in Exhibit 3-6 and calculate all of the ratios in Exhibit 3-6 for the firm. Do not enter any numbers directly on this worksheet. All formulas should be linked directly to the source of data from income statements and balance sheet worksheets. 3. Write an executive summary and clearly state the areas of concern, if any, using the ratios. Also, identify areas that have shown improvement, if any. Project 1 is due by 11:59 p.m. (CT) on Sunday of Module/Week 1. Use the following Big Rock Candy Mountain Mining financial statements and copy the data into a new Excel workbook. Recreate the income statement and balance sheet using formulas wherever possible. Big Rock Candy Mountain Mining Co. Income Statements For the Years 2019 and 2020 2020 2019 Sales $412,500 $398,600 Cost of Goods 318,786 315,300 Gross Profit 93,714 83,300 Depreciation 29,800 29,652 Selling & Admin. Expense 26,250 24,550 Other Operating Expense 1,210 1,245 Net Operating Income 36,454 27,853 Interest Expense 8,582 8,457 Earnings Before Taxes 27,872 19,396 Taxes 6,968 4,849 Net Income 20,904 14,547 Notes: Tax Rate Shares Earnings per Share 25.00% 52,100 $0.40 25.00% 52,100 $0.28 Big Rock Candy Mountain Mining Co. Balance Sheet For the Year Ended December 31, 2020 2020 2019 Assets Cash $16,435 $11,596 Marketable Securities 3,656 619 Accounts Receivable 45,896 47,404 Inventory 52,397 54,599 Total Current Assets 118,384 114,218 Gross Fixed Assets 436,573 397,023 No1 Accumulated Depreciation Net Plant & Equipment Total Assets 87,450 57,650 349,123 339,373 467,507 453,591 Liabilities & Owner's Equity Accounts Payable Accrued Expenses Total Current Liabilities Long-term Debt Total Liabilities Common Stock ($1.00 par) Additional Paid-in-Capital Retained Earnings Total Owner's Equity Total Liab. & Owner's Equity $37,752 $36,819 3,183 3,085 40,935 39,904 170,562 178,581 211,497 218,485 58,664 58,664 136,807 136,807 60,539 39,635 256,010 235,106 467,507 453,591 Exhibit 3-6 Ratio 2020 2019 Liquidity Ratios Current XXX Quick XXX Efficiency Ratios Inventory Turnover A/R Turnover Average Collection Period days days Fixed Asset Turnover Total Asset Turnover Leverage Ratios Total Debt Ratio % Long-term Debt Ratio % % LTD to Total Capitalization Debt to Equity LTD to Equity Coverage Ratios Times Interest Earned Cash Coverage Ratio Profitability Ratios Gross Profit Margin Operating Profit Margin Net Profit Margin Return on Total Assets Return on Equity Return on Common Equity Answer the following questions: 1. On another worksheet, create a statement of cash flows for 2020. Do not enter any numbers directly on this worksheet. All formulas should be linked directly to the source of data from income statements and balance sheet worksheets. 2. Set up a ratio worksheet similar to the one in Exhibit 3-6 and calculate all of the ratios in Exhibit 3-6 for the firm. Do not enter any numbers directly on this worksheet. All formulas should be linked directly to the source of data from income statements and balance sheet worksheets. 3. Write an executive summary and clearly state the areas of concern, if any, using the ratios. Also, identify areas that have shown improvement, if any. Project 1 is due by 11:59 p.m. (CT) on Sunday of Module/Week 1
