ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
ACCTG 211 - SEMESTER FINANCIAL STATEMENT ANALYSIS PROJECT - FALL 2018 -- QUARTER 4
2
Name(s):
Steven Walker
3
4
Student Guidelines:
5
This project will be completed in several phases. Use the following guidelines to walk through the project. There are 'check figures' shown below that you can use to make sure you are on the right track as you go. This company has been in business for many years, and the prior years' (2014 and 2015) financial statements are provided for you. The first tab shows the financial statements from prior years. Review these before you start, just to familiarize yourself with where you are starting. Read carefully. Ask questions!
6
7
A secondary goal of the project is to encourage you to expand your knowledge, understanding, use and application of Excel as a business tool. As such, you should use "sum" instead of manually adding numbers on your calculator and plugging the total in to the spreadsheet, and you should maintain all formatting in the spreadsheet such as font and size, column width, number of decimal places, shading, line placement, etc. If you can see that you have altered the formatting in any way and cannot get it to return to the original, ask for help. The most basic of features are used in this spreadsheet and this is a great opportunity for you to learn to use them.
8
9
If you find that you have completed all your transactions and you do not match any of the check figures, try the following: 1) Look to see what the difference is between what your calculation comes up with, and the check figure. Do you see any entries that are for that amount? Is it divisible by 9? (You may have transposed two numbers). Is it divisible by 2? You may have debited when you should have credited (or vice versa), so browse for entries that are half or twice the calculated difference. 3) Walk through your entries a second time and look for errors. 4) Walk through your posting and double-check to make sure you used each journal entry. 5) Seek help from a tutor. These are all steps you can take on your own, and if none of these work, you should email your faculty and explain to them which check figure is off and by how much, and they will tell you how to proceed from there.
10
11
PART 1 (transactions analysis and financial statement preparation)
12
This part of the project will require you to prepare journal entries for the current year transactions. The transactions are listed by quarter and you will do one quarter at a time. The "Transactions" worksheet contains the list of transactions. After you prepare the journal entries, you will post them, so that you can determine each account balance to prepare a trial balance (listing of all accounts). Then you will prepare a set of financial statements. For each quarter, follow the instructions below. You must complete each quarter before moving on to the next quarter.
13
14
Q1Q2Q3Q4
15
____X___
____X___
____X___
________
The transactions are divided into four quarters, and progress from knowledge you will acquire early in the semester, to knowledge that will come later in the semester. Look at the top of each quarter's transactions to see what chapter you should have completed to be prepared to record the transactions for that quarter. Once you have completed those chapters, you should be ready to post the transactions for the quarter. Each entry will fit on a set of shaded (or unshaded) lines in the general journal. Put the transaction number in the "Post Reference" columns. Quarter One has some entries already completed for you for reference. Some of the transactions will give you guidance about what account you should use. NOTE: If no entry is required, post the date and reference number, but leave the account name and dollar amount columns empty -- this will let me know that you didn't just skip the entry, but that you knew that no entry was required for that transaction.
16
17
18
19
20
21
22
23
____X___
____X___
____X___
________
Make sure that your debits = credits at the bottom of the general journal entry columns. These columns should total:
24
Qtr 1 = 9,415 ΓΌQtr 2 = 10,782 ΓΌQtr 3 = 10,629 ΓΌQtr 4 = 13,018 ΓΌ
25
26
____X___
____X___
____X___
________
Post each transaction from the General Journal to the trial balance for the quarter. Some of the transactions of Q1 are already done for you, so use those as an example. Notice that when you have a trial balance, the total debits will equal the total credits at the bottom of the columns. Your entries will be made in the shaded area, with debits in one column and credits in the other. The ending balance will then be the beginning balance, adjusted by the debit and credit entries that you made. The accounts with debit balances will be increased by debit entries and decreased by credit entries. The accounts with credit balances will be decreased by debit entries and increased by credit entries. Review the formulas provided in the first quarter (columns N and O) to understand how these adjustments will impact the account balances. Make sure your trial balance balances by showing the debits = credits (cell N39 must equal cell O39).
27
28
29
30
31
32
33
34
____X___
____X___
____X___
________
Prepare an income statement, statement of changes in retained earnings, and balance sheet. Some lines in the first quarter are done for you. Review the formulas used to understand which accounts on the trial balance go to each line, and note how formulas were used to subtotal and total the various parts of the financial statements. To be consistent in our formatting, we will put items to be subtracted (expenses or dividends) in brackets to show they are being subtracted. Remember that the income statement and statement of changes in retained earnings are both statements that cover a period of time. The balance sheet is at a point in time. Therefore, to prepare an annual income statement or statement of changes in retained earnings, you will have to add each of the four quarters together to get a total. The balance sheet is the same whether you are talking about the end of the quarter or the end of the year-to-date, since the "as of" date is the same. If you are having trouble, ask questions! Net income for each quarter is:
35
36
37
38
39
40
41
42
Qtr 1 = 250 ΓΌQtr 2 = 199 ΓΌQtr 3 = 327 ΓΌQtr 4 = 31 ΓΌ
43
Total Assets as of the end of the quarter is:
44
Qtr 1 = 8,366 ΓΌQtr 2 = 9,053 ΓΌQtr 3 = 11,000 ΓΌQtr 4 = 8,113 ΓΌ
45
46
47
ADDITIONAL 4TH QUARTER REQUIREMENT (horizontal and vertical analysis)
48
You now have three full sets of financial statements: the statements that were provided for 2014 and 2015, plus a set of statements that you prepared for 2016 based on transactions.
49
50
Using the financial statements for the three years, you will prepare an analysis of the company's performance. The first step is to lay out the income statement and the balance sheet in a horizontal analysis and a vertical analysis. This will provide you with comparative numbers an analyst would use to determine trends and areas of strength or concern for a company.
51
52
53
54
The 2014 vertical analysis is done for you so that you can see how to do the subsequent two years, and the change from 2014 to 2015 horizontal analysis is done so that you have a guide for how to do the 2015 to 2016 analysis. Click on the Horizontal Analysis and Vertical Analysis tabs below to complete this analysis
55
56
57
PART 5 (ratio analysis and financial statement review) -- COMPLETED ON "CONNECT" -- CHECK WITH YOUR INSTRUCTOR AND ON CONNECT FOR DUE DATE TIMING
58
This part of the project is on Connect. Before you begin this portion on Connect, compare your statements to the ones provided in the textbook appendix. You will see that you have just completed the preparation of the financial statements for the Campbell's Soup Company. While your project completed the financial statements for '2016' you actually completed the Campbell's Soup Company results for 2014. Now you can answer some questions about this company using their financial statements, and prepare financial statement ratios that lenders and investors might use to determine the financial stability and potential of a real company. Complete the selected questions on Connect.
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100