Excel #$1$; Financial Statements & Ratios

There you sit trying to decide whether to actually read the assigned chapter from

your accounting class, or go to a party across campus. After a few moments of

thought you figure that the accounting test is a week from now and that your time

can be better utilized enjoying life. As you lock your door to leave you receive a text

from your cousin Dewey Jr$.$ Dewey Jr$.,$ is about your age. His father has several

businesses and from what your mother says is doing quite well.

Dewey Jr$.$ writes; $$Dad needs some help with some business stuff. Something

about financial statements and some ratios. It sounds like math to me and you

know I don$$t do math. You$$re in business collage, right? Can you help me$?$ Dad

thinks that since I$$m in collage that I should know how to do things, but we don$$t

have to do math at my collage$$ You know that$$s true $$ Dewey Jr$.$ is the Mr$.$ Potato$-$

head of mathematics and you suspect that this characteristic may be genetic.

Somewhat intrigued you text back, $$Yeah$,$ I$$m studying business, but it$$s at a

college not a collage. What are you majoring in now?$$ Knowing that Dewey Jr$.$ has

tried every major from pre$-$med to post$-$modern art you are not that surprised when

he replies, $$I$$m going to be a crypto$-$zoologist and I$$m going to specialize in

studying whether Bigfoot has a chupacabra for a pet like we do with dogs.$$ You lose

a few minutes contemplating this, and finally, not knowing what else to say you

reply, $$Send me an email with what you need doing and maybe I can help.$$

The next day Dewey Jr$.$ finally sends an email stating, $$Dad needs some balance

sheets and income statements for the past couple of years. So here are the

accounts in the business...

The dollar amounts are for this year and last year, respectively :

Accounts Payable; $$160$ and $$2,160$

Accounts Receivable, net; $$3,801$ and $$1,600$

Cash; $$2960$ and $$1,260$

Common Stock, $$1$ par; $$5,000$ and $$5,000$

Cost of Goods Sold; $$15,138$ and $$9,900$

Income Tax rate; $30\%$ and $37\%$

Interest Expense; $$792$ and $$780$

Inventory; $$3,900$ and $$2,700$

Marketable Securities; $$2,454$ and $$300$

Note Payable due in eighteen months; $$6,700$ and $$6,500$

Plant, Property, & Equipment, net; $$9,300$ and $$8,940$

Prepaid expenses; $$200$ and $$480$

Retained Earnings; $($to be calculated$)$ and $$1,320$

Sales; $$30,570$ and $$25,480$

Selling and Administrative expenses; $$1,590$ and $$1,560$

Wages Payable; $$300$ and $$300$

Your job is to create two financial statements: the Income Statement and the

Balance Sheet. You will need to include in your solution a dynamic calculation for

the current year$$s ending Retained Earnings balance. Next, will use vertical analysis

$($common sized financial statements$),$ horizontal analysis $($both the change in dollar

amounts and percentage$),$ and finally calculate the ratios$/$measurements listed

below for both years. The ratios should be labeled and numbered one through ten.

Make sure that dollar signs are used where appropriate, and that other markings

such as percent signs are also applied when needed.

Calculate to the second decimal place the following: Working Capital, Current Ratio,

Acid$-$test Ratio, Accounts Receivable Turnover, Average Collection Period, Inventory

Turnover, Debt$-$to$-$Equity Ratio, Gross Margin Percentage, Net Profit Margin

Percentage, and Return on Assets.

See the back side of this sheet for a bit more detail on the expectations and

requirements.