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 DeweyJr. 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 dont do math. Youre in businesscollage,right? Can you help me? Dad thinks that since Im in collage that I should know how to do things, but we dont have to do math at my collage You know thats 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, Im studying business, but its 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, Im going to be a crypto-zoologist and Im going to specialize in studying whether Bigfoot has a chupacabrafora pet like we do with dogs. You lose a few minutes contemplating this, andfinally,not knowing what else to say you reply, Send me an email with what you need doing and maybe I can help. The next dayDewey 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,901 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,580 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; unknown and $1,320

Sales; $30,750 and $25,480

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

Wages Payable; $400 and $300

Your job is to create two financialstatements:the Income Statement and the Balance Sheet.Next,will use vertical analysis(common sized financial statements),horizontal analysis (both the change in dollar amounts and percentage),andfinallycalculate the ratios/measurementslisted belowfor both years.The ratios should be labeled and numbered one through ten. Make sure that dollar signs are used where appropriate, andthatother markings such as percent signs are also applied when needed.

Calculateto the second decimal placethe 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 more detailon theexpectationsand requirements.

What Iam looking for from your Excel Assignment #1.

Yoursolution(Excel workbook) will need to haveat leasttwo(2)sheets.

The first sheet will have the given input data.

Numerical data in the other sheets will be fed from this input data.

The second sheet will contain your balance sheet and income statements. On this sheet you willpresent the horizontal analysis in both dollars and percent change.

Use thissecondsheet to calculate the asked for financialratiosfor the current year.

The numerical information on the second will be dynamic. Meaning, each cell on those sheets will either be calculated using values from another cell or directly reference another cell. The use of hard numbers on those sheets may result in your work not being scored this includes the calculation of the current years ending retained earnings balance.

Make certain thatwhenyou change a value on thedatasheet all other sheets will update.