I have questions regarding my excel vba class on how to create a functions with ratio formula if you were given a financial statement. 
Balance Sheet Income Statement Current Year Prior Year Current Year Prior Year $ 32,000,000 $ 30,400,000 773,500 $ 1,200,000 Cash Sales 2,748,493 Cost of Goods Sold Accounts Receivable 2,748,493 15,360,000 14,592,000 Inventory 2,432,000 2,432,000 Gross Profit 16,640,000 15,808,000 6,380,493 Current Assets 5,953,993 Expenses 6,080,000 Wages 6,400,000 Furniture Fixtures and Equipmen 45,000,000 Travel 320,000 38,000,000 304,000 Accumulated depreciation (15,900,000) (11,400,000) 1,200,000 Rent 1,200,000 Maintenance & Repairs Other long term assets 400,560 400,560 640,000 608,000 $35,454,553 $33,381,053 Total Assets Other Operating expenses 320,000 304,000 Total Operating Expenses 8,496,000 8,880,000 EBITDA 7,760,000 7,312,000 Accounts Payable $ 1,924,000 $ 1,924,000 (4,500,000) (3,800,000) Depreciation Accrued Liabilities 384,800 Interest expense (70,000) (70,000) 384,800 Income Taxes Payable 3,190,000 3,442,000 1,204,700 1,204,700 Earnings Before Taxes Income Taxes (1,116,500) $ 2,073,500 $ (1,204,700) Current Liabilities 3,513,500 3,513,500 Debt 1,000,000 1,000,000 Net Income 2,237,300 Total Liabilities 4,513,500 4,513,500 Common Shares 1000 outstanding 100,000 100,000 400,560 Maintenance & Repairs 640,000 608,000 Other long term assets 400,560 $33,381,053 $35,454,553 Total Assets Other Operating expenses 320,000 304,000 Total Operating Expenses 8,880,000 8,496,000 EBITDA 7,312,000 7,760,000 Accounts Payable $ 1,924,000 $ 1,924,000 (3,800,000) (4,500,000) Depreciation 384,800 (70,000) (70,000) Accrued Liabilities 384,800 Interest expense Income Taxes Payable 1,204,700 1,204,700 Earnings Before Taxes 3,190,000 3,442,000 Current Liabilities 3,513,500 (1,116,500) (1,204,700) 3,513,500 Income Taxes 2,073,500 $ Debt 1,000,000 1,000,000 2,237,300 Net Income Total Liabilities 4,513,500 4,513,500 Common Shares 1000 outstanding 100,000 100,000 Paid in Capital 1,000,000 1,000,000 Current Year Income 2,073,500 2,237,300 Retained Earnings 27,767,553 25,530,253 Total Liabilities 30,941,053 28,867,553 $35,454,553 $33,381,053 Total Assets and Liabilities 400,560 Maintenance & Repairs 640,000 608,000 Other long term assets 400,560 $33,381,053 $35,454,553 Total Assets Other Operating expenses 320,000 304,000 Total Operating Expenses 8,880,000 8,496,000 EBITDA 7,312,000 7,760,000 Accounts Payable $ 1,924,000 $ 1,924,000 (3,800,000) (4,500,000) Depreciation 384,800 (70,000) (70,000) Accrued Liabilities 384,800 Interest expense Income Taxes Payable 1,204,700 1,204,700 Earnings Before Taxes 3,190,000 3,442,000 Current Liabilities 3,513,500 (1,116,500) (1,204,700) 3,513,500 Income Taxes 2,073,500 $ Debt 1,000,000 1,000,000 2,237,300 Net Income Total Liabilities 4,513,500 4,513,500 Common Shares 1000 outstanding 100,000 100,000 Paid in Capital 1,000,000 1,000,000 Current Year Income 2,073,500 2,237,300 Retained Earnings 27,767,553 25,530,253 Total Liabilities 30,941,053 28,867,553 $35,454,553 $33,381,053 Total Assets and Liabilities Net Income Return on Assets (ROA) : %3D (Beginning Total Assets + Ending total Assets)/2 Taxes - Interest (EBIT) Net Income Return on Capital employed(ROCE) TUBeg Assets - Beg Cur. Liab. ) + (End Assets End Cur. Liab.)/2 2 Use the functions you created to calculate the ratios for the financial statements in this workbook 3 Your function should use proper macro etiquette where applicable 4 When creating the ROCE function define two variables one for EBIT and One for Averag Assets. Add a line of code to calculate EBIT and store it in the EBIT varaible Add another line of code to calculate Average assets and store in the Average Assets variable Then add a line of code to calculate ROCE by dividing EBIT by Average Assets. Part 1- Balance Sheet Ratios 1 Create 4 functions, one each to calculate each of the ratios below in a module called "StudentMacros" Current Assets Current Ratio = Current Liabilities Current Assets Inventory Quick Ratio Current Liabilities Net Income Return on Assets (ROA) (Beginning Total Assets + Ending total Assets)/2 Net Income Taxes - Interest (EBIT) Beg Cur. Liab.) + (End Assets End Cur. Liab.)/2 Return on Capital employed(ROCE) ((Beg Assets 2 Use the functions you created to calculate the ratios for the financial statements in this workbook 3 Your function should use proper macro etiquette where applicable Balance Sheet Income Statement Current Year Prior Year Current Year Prior Year $ 32,000,000 $ 30,400,000 773,500 $ 1,200,000 Cash Sales 2,748,493 Cost of Goods Sold Accounts Receivable 2,748,493 15,360,000 14,592,000 Inventory 2,432,000 2,432,000 Gross Profit 16,640,000 15,808,000 6,380,493 Current Assets 5,953,993 Expenses 6,080,000 Wages 6,400,000 Furniture Fixtures and Equipmen 45,000,000 Travel 320,000 38,000,000 304,000 Accumulated depreciation (15,900,000) (11,400,000) 1,200,000 Rent 1,200,000 Maintenance & Repairs Other long term assets 400,560 400,560 640,000 608,000 $35,454,553 $33,381,053 Total Assets Other Operating expenses 320,000 304,000 Total Operating Expenses 8,496,000 8,880,000 EBITDA 7,760,000 7,312,000 Accounts Payable $ 1,924,000 $ 1,924,000 (4,500,000) (3,800,000) Depreciation Accrued Liabilities 384,800 Interest expense (70,000) (70,000) 384,800 Income Taxes Payable 3,190,000 3,442,000 1,204,700 1,204,700 Earnings Before Taxes Income Taxes (1,116,500) $ 2,073,500 $ (1,204,700) Current Liabilities 3,513,500 3,513,500 Debt 1,000,000 1,000,000 Net Income 2,237,300 Total Liabilities 4,513,500 4,513,500 Common Shares 1000 outstanding 100,000 100,000 400,560 Maintenance & Repairs 640,000 608,000 Other long term assets 400,560 $33,381,053 $35,454,553 Total Assets Other Operating expenses 320,000 304,000 Total Operating Expenses 8,880,000 8,496,000 EBITDA 7,312,000 7,760,000 Accounts Payable $ 1,924,000 $ 1,924,000 (3,800,000) (4,500,000) Depreciation 384,800 (70,000) (70,000) Accrued Liabilities 384,800 Interest expense Income Taxes Payable 1,204,700 1,204,700 Earnings Before Taxes 3,190,000 3,442,000 Current Liabilities 3,513,500 (1,116,500) (1,204,700) 3,513,500 Income Taxes 2,073,500 $ Debt 1,000,000 1,000,000 2,237,300 Net Income Total Liabilities 4,513,500 4,513,500 Common Shares 1000 outstanding 100,000 100,000 Paid in Capital 1,000,000 1,000,000 Current Year Income 2,073,500 2,237,300 Retained Earnings 27,767,553 25,530,253 Total Liabilities 30,941,053 28,867,553 $35,454,553 $33,381,053 Total Assets and Liabilities 400,560 Maintenance & Repairs 640,000 608,000 Other long term assets 400,560 $33,381,053 $35,454,553 Total Assets Other Operating expenses 320,000 304,000 Total Operating Expenses 8,880,000 8,496,000 EBITDA 7,312,000 7,760,000 Accounts Payable $ 1,924,000 $ 1,924,000 (3,800,000) (4,500,000) Depreciation 384,800 (70,000) (70,000) Accrued Liabilities 384,800 Interest expense Income Taxes Payable 1,204,700 1,204,700 Earnings Before Taxes 3,190,000 3,442,000 Current Liabilities 3,513,500 (1,116,500) (1,204,700) 3,513,500 Income Taxes 2,073,500 $ Debt 1,000,000 1,000,000 2,237,300 Net Income Total Liabilities 4,513,500 4,513,500 Common Shares 1000 outstanding 100,000 100,000 Paid in Capital 1,000,000 1,000,000 Current Year Income 2,073,500 2,237,300 Retained Earnings 27,767,553 25,530,253 Total Liabilities 30,941,053 28,867,553 $35,454,553 $33,381,053 Total Assets and Liabilities Net Income Return on Assets (ROA) : %3D (Beginning Total Assets + Ending total Assets)/2 Taxes - Interest (EBIT) Net Income Return on Capital employed(ROCE) TUBeg Assets - Beg Cur. Liab. ) + (End Assets End Cur. Liab.)/2 2 Use the functions you created to calculate the ratios for the financial statements in this workbook 3 Your function should use proper macro etiquette where applicable 4 When creating the ROCE function define two variables one for EBIT and One for Averag Assets. Add a line of code to calculate EBIT and store it in the EBIT varaible Add another line of code to calculate Average assets and store in the Average Assets variable Then add a line of code to calculate ROCE by dividing EBIT by Average Assets. Part 1- Balance Sheet Ratios 1 Create 4 functions, one each to calculate each of the ratios below in a module called "StudentMacros" Current Assets Current Ratio = Current Liabilities Current Assets Inventory Quick Ratio Current Liabilities Net Income Return on Assets (ROA) (Beginning Total Assets + Ending total Assets)/2 Net Income Taxes - Interest (EBIT) Beg Cur. Liab.) + (End Assets End Cur. Liab.)/2 Return on Capital employed(ROCE) ((Beg Assets 2 Use the functions you created to calculate the ratios for the financial statements in this workbook 3 Your function should use proper macro etiquette where applicable
