Excel Assignment: Key Ratios

This is an individual assignment, not a team assignment.

In order to complete this assignment, you must download the TEMPLATE, complete the assignment using the template $($fill it out$),$ and turn in a single$$Excel file $($with your name as part of the filename$)$ that includes all$$of the following models $($found under separate tabs $($worksheets$)$ in the template. $$Keep them on separate sheets...$($Just fill out the template. $$Don$$t change the format or the order of the calculations. $$Keep all items within their original cell addresses$)$:

Ratios $($Separate Worksheet$/$Tab$)$

On a separate sheet $($find the tab on the template named $$Ratios$)$ compute the ratios listed below. $$Link your ratio calculations to the Income Statement and Balance Sheet found in the template file. $$Use the data already included in the template $($do not alter the data on the income statement or balance sheet$)....$make sure the Income Statement and Balance Sheet remain on separate tabs $($worksheets$)$ in the template for this assignment.:

Current Ratio

Quick Ratio

Inventory Turnover Ratio

Average Collection Period$($for this calculation, assume Accounts Receivable Turnover $=11\mathrm{.}5)$

Fixed Asset Turnover

Total Asset Turnover

Debt Ratio

Debt to Equity

Time Interest Earned

Gross Profit Margin

Net Profit Margin

Return on Assets

Return on Equity $\backslash$begin$\{$tabular$\}\{|$l$|$l$|$l$|$l$|$l$|\}$

$\backslash$hline Balance Sheet & $\backslash$$US & & & $\backslash \backslash$

$\backslash$hline & & & & $\backslash \backslash$

$\backslash$hline Assets & & Liabilities and Owners Equity & & $\backslash \backslash$

$\backslash$hline Current Assets & & Current Liabilities & & $\backslash \backslash$

$\backslash$hline Cash & $\backslash$$ $20,000\mathrm{.}00$ & Accounts Payable & & $\backslash$$ $20,000\mathrm{.}00\backslash \backslash$

$\backslash$hline Accounts Receivable & $\backslash$$ $20,000\mathrm{.}00$ & Notes Payable & & $\backslash$$ $10,000\mathrm{.}00\backslash \backslash$

$\backslash$hline Inventory & $\backslash$$ $20,000\mathrm{.}00$ & Total Current Liabilities & & $\backslash$$ $30,000\mathrm{.}00\backslash \backslash$

$\backslash$hline Total Current Assets & $\backslash$$ $60,000\mathrm{.}00$ & & & $\backslash \backslash$

$\backslash$hline & & Long Term Debt & & $\backslash$$ $20,000\mathrm{.}00\backslash \backslash$

$\backslash$hline Fixed Assets & & & & $\backslash \backslash$

$\backslash$hline Net Fixed Assets & $\backslash$$ $75,000\mathrm{.}00$ & Total Liabilities & & $\backslash$$ $50,000\mathrm{.}00\backslash \backslash$

$\backslash$hline & & & & $\backslash \backslash$

$\backslash$hline & & Owners Equity & & $\backslash \backslash$

$\backslash$hline & & Owners Equity & & $\backslash$$ $77,655\mathrm{.}00\backslash \backslash$

$\backslash$hline & & & & $\backslash \backslash$

$\backslash$hline & & Retained Earnings & & $\backslash$$ $7,345\mathrm{.}00\backslash \backslash$

$\backslash$hline & & Total Owners Equity & & $\backslash$$ $85,000\mathrm{.}00\backslash \backslash$

$\backslash$hline & & & & $\backslash \backslash$

$\backslash$hline Total Assets & $\backslash$$ $135,000\mathrm{.}00$ & Total Liabilities and Owners Equity & & $\backslash$$ $135,000\mathrm{.}00\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}\backslash$begin$\{$tabular$\}\{|$l$|$l$|$l$|$l$|$l$|$l$|$l$|$l$|\}$

$\backslash$hline $\backslash$multicolumn$\{3\}\{|$l$|\}\{$Income Statement$\}$ & & & & & $\backslash \backslash$

$\backslash$hline & & & & & & & $\backslash \backslash$

$\backslash$hline Revenue & & & & & $\backslash$$ $100,000\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline COGS & & & & & $\backslash$$ $50,000\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{3\}\{|$l$|\}\{$Gross Profit$\}$ & & & $\backslash$$ $50,000\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{3\}\{|$l$|\}\{$Sales$/$General$/$Administrative Expense$\}$ & & & $\backslash$$ $10,000\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{2\}\{|$l$|\}\{$Depreciation$\}$ & & & & $\backslash$$ $15,000\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{2\}\{|$l$|\}\{$EBIT$\}$ & & & & $\backslash$$ $25,000\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{2\}\{|$l$|\}\{$Interest Expense$\}$ & & & & $\backslash$$ $2,400\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline EBT & & & & & $\backslash$$ $22,600\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline Taxes & & & & & $\backslash$$ $7,910\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{2\}\{|$l$|\}\{$Net Income$\}$ & & & & $\backslash$$ $14,690\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{2\}\{|$c$|\}\{\}$ & & & & & & $\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{3\}\{|$l$|\}\{$Number of Shares Outstanding$\}$ & & & $100,000\mathrm{.}00$ & & $\backslash \backslash$

$\backslash$hline & & & & & & & $\backslash \backslash$

$\backslash$hline EPS & & & & & $\backslash$$ $0\mathrm{.}15$ & & $\backslash \backslash$

$\backslash$hline Dividends & & & & & $\backslash$$ $7,345\mathrm{.}00$ & Per share: & $\backslash$$ $0\mathrm{.}07\backslash \backslash$

$\backslash$hline Tax Rate: & $35\mathrm{.}00\backslash \%$ & & & & & & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$