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 dont do math. Youre in business collage, 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 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,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 financial statements: 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), 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 more detail on the expectations and requirements.

What I am looking for from your Excel Assignment #1.

Your solution(Excel workbook) will need to have at least two (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 will present the horizontal analysis in both dollars and percent change.

Use this second sheet to calculate the asked for financial ratios for 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 that when you change a value on the data sheet all other sheets will update.

To reiterate: All numerical cells must either contain an input amount (first sheet), reference another cell, or contain a calculation formula. Your work is expected to be dynamic, and it is expected to be easily readable. You have examples in your textbook.