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,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 year’s ending retained earnings balance.
Make certain that when you change
a value on the data sheet all other sheets will update.
• You
may not use another’s workbook for this assignment, nor may you use one from
another class or from our in-class work. Create a new Excel workbook.
• 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.
• This
assignment is worth fifteen points.
• You will be allowed three submissions
for this assignment.
Name your file: “Excel 1, Your
Name”.
