Part 2: Individual Data Entry In Excel Template Financial Ac

Part2individualdataentryinexceltemplatefinancialaccounting

Part 2: Individual – Data Entry In Excel Template Financial Accounting Workshops related to Part 2 will be held as announced. Note : Before you start this part of the project, make sure you are using the complete Form 10-K for your company. Secondary information that has been summarized or condensed on financial sites such as Hoovers, Yahoo, or MSN will NOT provide you with the details needed to prepare this project. 1. Retrieve the project Excel template from Blackboard.

Enter your identifying information on the first tab of the workbook and save the file with your first and last names. 2. In the “Introduction” text box outlined in blue on the first tab of the template, in your own words, summarize your company’s business activities, products, and the primary industry or industries in which it operates (200 to 300 words). If you do incorporate any language that is not your own, make sure to use quotation marks and clearly indicate the source. You may find preparing this summary in Word and pasting it into the text box provided on the template an efficient way to accomplish this task.

Resize the text box so that it fits the text you’ve placed in it. This will not entail adding any rows or columns to this tab. 3. Copying your financial statements: a. Using the company’s pdf version of the financial statements, enter your company’s balance sheet and income statement on the appropriate “Balance Sheet” and “Income Stmt” tabs on the Excel template provided.

You should have two years’ of balance sheet data and three years’ of income statement and cash flow data. Use all of the exact account names and amounts, headings and subheadings, and years of data contained in your company’s statements. To simplify your data entry, use the same ARIAL 11 font throughout your statements. However, follow the formatting given in your primary document for indents, capitalization, underlining, dollar signs, decimal points, etc. b. For all differences, subtotals, and totals in the statements, insert formulas to perform the appropriate calculations.

Make sure that all account and line descriptions are in column A and use the “wrap text” feature to keep the column width of these items reasonable. Delete empty columns. Make sure all items have the same font and size. 4. Your work will be evaluated, in part, on the following criteria.

Please use this checklist to review your document to see that: a. All headings, account names, and dollar amounts are the same as on the audited financial statements included in the Form 10-K. b. All statements are consistent with the Form 10-K as to text alignment, alignment of amounts, indentations, use of $ and underlines. (The objective here is a consistent, professional presentation of the data, and replicating the published version is usually a good way to achieve that.) c. Empty rows and columns are deleted. d. Each account and line description is in a single cell, using “wrap text” to control the column width. e. All columns showing “####” have been made wide enough to display the full amount. f. There are three years of data for the income statement and two years of data for the balance sheet g. An ARIAL 11 font is used throughout. (The Form 10-K may use multiple fonts, but your project file should be as uncluttered and consistent as possible.) h. Every difference, subtotal, and total in every financial statement is the result of a formula using cell references and/or Auto Sum. i. Appropriate headings, including indication of year ends and the denomination of amounts used in the statement (thousands, millions, etc.) are used on all statements.

5. Save your work as an Excel file. Consider making an optional appointment to have your work reviewed by a Learning Assistant. After your work is reviewed, make all corrections necessary. 6. Submit your file to Blackboard no later than the Part 2 due date listed at the top of this document. DO NOT Email your file – it will not count as being submitted. Part 3: Individual – Data Analysis · Make all necessary revisions and corrections to the Part 2 before beginning Part 3. · Financial Accounting Workshops related to Part 3 will be held as announced. 1. Read Chapter 12 in your text.

2. Make a copy of your completed Balance Sheet and then a copy of your Income Statement using the Edit/Move or Copy Sheet command. Be sure to check the “Create a copy” box indicating you want to make a copy. Insert the copy of the Balance Sheet before the Income Statement tab. Insert the copy of the Income Statement Data Summary tab.

3. Review Illustration 12-2 on page 655 of a horizontal analysis of an income statement. Rename the original “Income Stmt” tab “IS Horiz” and prepare a horizontal analysis of your income statement. Because your income statement contains three years of data, you will have to add a “difference” column and a “percentage change” column for the first two years, and then add another two columns that compare the middle year to the current year. Properly label each set of columns.

Use formulas similar to those used for your balance sheet for calculations, formatting, and copying/pasting. Check for appropriate labels, headings, etc. 4. Based on Illustration 12-2, prepare a horizontal analysis of your balance sheet. Rename the “Balance Sheet” tab “BS Horiz”. Add two columns to the right of your balance sheet amounts for a “horizontal analysis,” comparing amounts across years. Label the first “difference” and the second “percentage change”. Prepare formulas for both, formatting percentages with the % sign and one decimal place. Copy and paste formulas to remaining cells. Adjust formatting as needed. Check labels and headings. 5. Review Illustration 12-3 on page 656 of a vertical analysis of income statements. Rename “Income Stmt (2)” tab “IS Vert” and prepare a vertical analysis of your income statement. Insert a column between each pair of amount columns, use an absolute reference to net sales/revenue in formulas, and copy formulas for each year. Ensure proper formatting and labels. 6. Review Illustration 12-3 on page 656 of a vertical analysis of balance sheets. Rename “Balance Sheet (2)” tab “BS Vert”. Calculate component percentages as per your textbook’s section on vertical analysis, using an absolute reference to total assets for each year. Insert and copy formulas, format percentages appropriately, and check labels. 7. Fill in the amounts requested on the Data Summary tab by referencing your balance sheet and income statements. Include the actual years, use cell references wherever possible, and manually enter data as needed from Form 10-K. Leave cells blank if amounts are zero. Ensure all amounts are positive unless representing net loss or negative cash flow. Use cell references creatively for calculations like averages. 8. On the Charts tab, create separate charts for each of these items over five years: a. Net Revenue or Net Sales b. Gross Profit Margin c. Net Income d. Free Cash Flow Use cell references for data, select appropriate chart types, and ensure chronological order from earliest to latest. 9. On the Ratio tab, input the actual years as column headings; calculate ratios using data from your Data Summary tab, referencing cells for formulas. 10. Review your entire document to ensure adherence to previous checklist items, including proper formulas, formatting, labels, and accurate data representation. 11. Schedule a review appointment with a learning assistant to verify your work. Make any necessary corrections. Your submission is not complete without this review. 12. Submit your completed work via Blackboard by the due date.