The attached excel spreadsheet has Operating and Financial ratios for your business. With the fields provided and ratio formulae, determine the missing items from Income and Balance Sheet. You have to insert numbers into the cells with yellow color. Complete all 3 tabs – your current situation, where you’d like to be in 1 year, and where you’d like to be in 5 years. Play around with the numbers, such as sales and operating expenses, and see what amount of sales you need to reach your 1-year and 5-year goals (watch the numbers in the green cells change as you enter different data). Include a narrative in Word document that provides your evaluation of the financial ratios — what kind of shape is the company currently in? What is the best approach you could take to improve your ratios? Is your sales goals achievable? Explain your responses.
Your Business Name Here
RATIOS
NET PROFIT MARGIN #DIV/0!
CURRENT RATIO #DIV/0!
DEBT TO EQUITY RATIO #DIV/0!
INVENTORY TURN OVER RATIO #DIV/0!
TAX RATE (usually a fixed rate; insert what you expect yours to be)
LONG TERM DEBT INTEREST RATE (will be based on your debt; check your papers; if you have no long-term debt, enter 0%)
INCOME STATEMENT BALANCE SHEET
SALES EQUITY $- FIXED ASSETS
COST OF GOODS SOLD
OPERATING EXPENSES LONG TERM DEBT CASH
EARNINGS BEFORE INTEREST AND TAX (EBIT) $- OTHER RECEIVABLES
INTEREST $- ACCOUNTS PAYABLE INVENTORY
PROFIT BEFORE TAX $- CURRENT LIABILITIES $- CURRENT ASSETS $-
TAX $-
PROFIT AFTER TAX $- TOTAL LIABILITIES $- TOTAL ASSETS $-
FORMULAE
CURRENT RATIO = CURRENT ASSETS/CURRENT LIABILITIES
DEBT TO EQUITY RATIO = TOTAL DEBT / TOTAL EQUITY
NET PROFIT MARGIN = PROFIT AFTER TAX / SALES
EBIT = SALES – COST OF GOODS SOLD – OPERATING EXPENSES
INVENTORY TURNOVER RATIO = SALES / INVENTORY ON HAND
CURRENT ASSETS = CASH + RECEIVABLES + INVENTORY
COST OF GOODS SOLD = (BEGINNING INVENTORY + INVENTORY + INVENTORY PURCHASES) – END INVENTORY
