Introduction:

Accountants use Excel to analyze transactions and accounts, prepare financial statements, calculate budgets, create invoices, and many more tasks. Mastering the basics of Excel is critical to your success. You should already know how to create spreadsheets using common mathematical formulas and Excel Functions. In AIS, you will develop or improve your skills linking multiple spreadsheets; creating formulas using Excel functions IF, VLOOKUP, ROUND, SUMIF, Ranges; and creating PIVOT tables.

For the Excel assignment you will create a yearend workbook with multiple worksheets to convert the unadjusted trial balance of Waren Sports Supply to a complete set of financial statements. You will follow the steps in the accounting cycle to 1) prepare unadjusted trial balance, 2) determine and record adjustments, 3) create adjusted trial balance, and 4) create financial statements: Balance Sheet, Income Statement, Statement of Cash Flows (Indirect method).

You will begin with the Yearend Worksheet template found on BlackBoard that includes all the account numbers and names along with unadjusted trial balance figures. The instructions below give you the information needed to calculate and record adjustments in Excel, and to add these adjustments to the Yearend Worksheet to create the adjusted trial balance. You will use the adjusted trial balance data to create financial statements. NOTE: Do not follow the year-end instructions in the SUA Booklets.

Your Excel workbook should be fully integrated. For example, a change in the tax rate from 15% to 17% should automatically update your tax expense/accrual adjustment, the adjusted trial balance, and the financial statements. Therefore, all calculations must be in Excel, not on a piece of paper or in your calculator.

Another important design concept in Excel is separating inputs, calculations and reports into separate sections or sheets. Keeping these separate makes it easier to update the spreadsheets with new data, especially if the spreadsheet is given to another user to update. In this assignment, all inputs are limited to the Yearend Worksheet and Adjustments worksheet. Calculations are made on these two sheets. The Balance Sheet, Income Statement and Statement of Cash Flows are reports with limited functions for adding and subtracting figures.

Excel Asssignment Instructions:

1. Download the Waren Sports Supply Yearend Workbook from BlackBoard. Save the file as: lastname_firstname_WSS2016fall.

2. You will note that the December 31, 2016 Post Closing Trial Balance and December 31, 2017 Unadjusted Trial Balance are populated with numbers.

a. Do debits equal credits?

i. Use the SUM function to total each column of the spreadsheet.

b. Should the 2017 allowance for doubtful accounts be a debit or credit balance?

i. How could this happen? You will make an adjustment later.

c. Why are 2017 retained earnings the same as 2016?

i. When is net income or loss recorded in retained earnings?

ii. We will not close out the temporary accounts at this time.

3. Year-end adjustments: Adjustments will be recorded on the Adjustment worksheet in the Excel workbook.

a. First, type the account number in the Account # column, then use the VLOOKUP function to populate account name?DO NOT TYPE THE ACCOUNT NAME. (NOTE: Click on the hyperlink for support from Microsoft.com.) The selection range for your VLOOKUP formula will be all account numbers and names on the Yearend Worksheet.

b. Calculate the adjustment amount. See the instructions below for each adjustment. If the formula is in the debit column, you can use the cell to insert the calculated amount in the credit column.

c. After each adjustment, write an explanation for the adjustment. Include any assumptions or calculation figures, such as bad debt expense rate or interest rate. This explanation is important documentation to support and justify the adjustment.

A note about ranges/table arrays in Excel: The simplest method to define a range in Excel is to select the cells in the formula. For example, (C5,A3.B52,2,false). A problem occurs, however, if you copy this formula to another cell. When you copy to a row below the current row, the formula automatically adjusts by one row and the previous formula becomes (C6,A4.B53,2,false). There are 2 methods to prevent the range from changing when the formula is copied. One is to use absolute cell references. To lock a cell reference, type a $ in front of the column letter and row number--$A$3.$B$52. Another method to lock the range is to use the Named Range function. A range is defined and a name is given to the range. The name is used instead of the cell references in a formula. For example, let's define the account number and account name as ACCOUNT, which is A3.B52. The formula is now VLOOKUP(C5,ACCOUNT,2,false).

4. ADJUSTMENTS:

1. Depreciation Expense: The amount of depreciation expense is $35,023.64. Input this figure in the adjustments worksheet.

2. Interest Expense: A $60,000, two-year note was signed and funded on December 18, 2017 with annual stated interest of 5%. The note was recorded, but no interest was paid or accrued. Create a formula to calculate the adjustment amount based on a 365 day year and starting on the day after the loan was signed and funded. Use ROUND function to round to nearest penny. DO NOT TYPE IN A CALCULATED AMOUNT.

3. Bad debt expense: Bad debt expense is estimated at 0.2% of net sales. Bad debt expense is recorded at the end of the year. Create a formula to calculate the adjustment amount. Use ROUND function to round to nearest penny. DO NOT TYPE IN A CALCULATED AMOUNT.

4. Ending Inventory: Inventory and cost of sales adjustment: Waren uses the periodic inventory method. A physical inventory was taken at midnight on December 31, 2017. The cost basis of the inventory on hand is $191,967.00. Adjust the inventory balance and close out the purchase and related accounts to cost of sales. (see pages 77-79 in the SUA Reference Manual.)

5. Audit Adjustments: The auditors found 2 transactions that were not recorded at December 31, 2017. Make adjusting entries for each of these transactions using the appropriate Excel functions.

i. Waren Sports purchased a new Ford F-150 truck for $30,000 on December 21, 2017. They financed 100% of the purchase from Bank of America under a 5-year, $30,000 note at 4% interest. The first payment is due January 31, 2018. (Do not record depreciation for 2017.)

ii. Waren Sports sold $10,000 of football inventory to San Diego State University on December 31, 2017. SDSU signed a promissory note to pay for the items in six months plus interest at 6%.

1. To record this entry you will need to add a new account, 10500 Notes Receivable, to the yearend worksheet.

2. Because the bad debt expense adjustment (#3 above) did not include this item, you need to include an adjusting entry to add 0.2% of this item to the bad debt expense.

5. Add the adjustment balances to the Adjustments columns on the Yearend Worksheet using the SUMIF function. The SUMIF function looks at all values in a range to select the values that meet the selection criteria, then aggregates (sums) the values to determine the total. For example, if there are 2 adjustments to interest payable on the Adjustments worksheet, we need to add the 2 adjustments together on the Yearend Worksheet. SUMIF will find the two entries based on the account numbers that match 20900 and sum the adjustment amounts.

a. The SUMIF formula needs to work when we add more adjustments. Consequently, if we define a specific range we will limit the rows included in the SUMIF formula. For example, if the range for debits is C5.C40 and we add an adjustment amount to row C45, the new amount will be excluded. The best way to avoid this problem is to define the range as the entire column instead of a range of cells. To do this, click on the column letter header when writing the SUMIF formula. (B:B,A13,D:D) has the criteria defined as all of column B and the values to sum as all of column D.

b. Place the SUMIF formula in every cell under the debit and credit Adjustment columns on the yearend worksheet.

6. Use IF formulas to combine the Unadjusted Trial Balance and Adjustments in the Adjusted Trial Balance columns. Many accounts can be either a net debit or a net credit in the Adjusted Trial Balance. (Can you think of a situation where the Cash balance is a credit?) Consequently, IF statements must be used to determine if the balance is a debit or credit. The IF statement should return the calculated amount if the criteria is true and a blank if the criteria is false, for example, (debits-credits>0, debits-credits, ""). Use absolute cell references or range names and copy the formulas to all cells in the total debit and total credit columns for all accounts.

7. After completing steps 4 through 7, you are ready to calculate federal income tax expense. Federal Income tax expense cannot be calculated until all other adjustments are posted and the income before income taxes reflects all adjustments. However, the formula you create in the worksheet should be flexible to allow for another adjustment to be recorded that changes income before taxes and automatically updates the tax calculation.

a. Calculate income before income taxes using the Adjusted Trial Balance data. Income before tax is equal to revenues minus expenses excluding Federal income tax expense. Create a formula in cell F59 on the Yearend Worksheet to calculate income before taxes. (Do not bring income before tax from the income statement because you haven't created the income statement yet.)

b. The Federal income tax rates are as follows:

Pre-tax income is over:	But not over:	Tax is:	Of amount over:
$0	$50,000	15%	$0
50,000	75,000	$7500 + 25%	50,000
75,000	.....	$13,750 + 34%	75,000

c. Use an IF statement to calculate income tax expense. The IF statement must consider income at 3 different levels. This requires an embedded or nested IF statement, i.e., 2 IF statements in one formula. (Be sure to test your IF statement at different levels of income before tax?your formula needs to work at all levels.)

d. After calculating tax expense, you can determine Net Income. The Net Income check figure is provided.

e. Create an adjustment for income tax expense on the Adjustment worksheet. The amount on the adjustment worksheet will be referenced from Income Tax Expense calculation on the Yearend Worksheet.

f. The SUMIF function you previously used will bring the adjustment to the Yearend Worksheet.

8. Populate the Balance Sheet and Income Statement debit and credit columns on the Yearend Worksheet using IF statements similar to step 4. above. Total Balance Sheet debits will not equal total credits?why? The same conditions apply to the Income Statement. Calculate the difference at the bottom of the columns and compare these amounts to Net Income.

9. Complete the Financial Statements on the appropriate worksheets.

a. The Statement of Cash Flows uses the indirect method.

b. Use formulas to insert the correct figures into the Financial Statements worksheets from the Yearend Worksheet balance sheet or income statement columns, except as follows:

i. On the Balance Sheet, the December 31, 2017 retained earnings will pull from the Income Statement because the retained earnings account in the Yearend Worksheet hasn't been adjusted for the amount of 2017 net income.

c. Calculations in the financial statements should be limited to sum, plus, and minus to aggregate or subtract accounts.

d. On the Statement of Cash Flows, you can bring the balances from the Yearend Worksheet or the Balance Sheet and Income Statement, whichever you prefer.

e. Compare the ending cash balance on the Statement of Cash Flows to the Balance Sheet cash. The amounts should equal.

f. Remove the gridlines from the financial statements.

10. You're done! Submit your work in EXCEL format. Google Docs, Open Office, Numbers, etc. are not acceptable formats.

a. Upload using Turnitin: BB> Assignments > Excel Assignment. Turnitin will separate the pages, but we will download your work to grade the Excel formulas and formats.

WAREN SPORTS SUPPLY, YEAR-END WORKSHEET, DECEMBER 31, 2017 ACCT NO. ACCOUNT TITLE 10100 10200 10300 10400 10600 10800 10900 ASSETS Cash Accounts receivable Allowance for doubtful accounts Inventory Marketable securities Fixed assets Accumulated depreciation 20100 20300 20400 20500 20600 20700 20900 21000 LIABILITIES Accounts payable Federal income taxes withheld State unemployment taxes payable Federal unemployment taxes payable F.I.C.A. taxes payable Federal incomes taxes payable Interest payable Notes payable 26000 29000 STOCKHOLDERS' EQUITY Common stock Retained earnings 30100 30200 30300 30400 30500 30600 30700 30800 31200 REVENUE AND GROSS PROFIT Sales Sales returns and allowances Sales discounts taken Cost of goods sold Purchases Purchases returns and allowances Purchases discounts taken Freight-in Miscellaneous revenue 12/31/2016 POST CLOSING TRIAL BALANCE DEBIT CREDIT 11,025.19 11,065.00 3,250.81 101,681.00 320,665.00 81,559.50 11,279.35 1,326.76 281.17 83.31 1,593.30 29,797.00 225,000.00 90,264.99 40100 40200 40300 40400 40500 40600 40800 40900 41000 40700 EXPENSES Rent expense Advertising expense Office supplies expense Depreciation expense Wages and salaries expense Payroll tax expense Interest expense Bad debt expense Other operating expense Federal income tax expense ----------------- ----------------- TOTALS Income before Taxes Income Tax Expense Net Income Tax rates for income before taxes $75,000 Net Income Check 15% 25% 34% 12/31/2017 UNADJUSTED TRIAL BALANCE DEBIT CREDIT 93,304.29 48,149.00 1,149.19 101,681.00 24,000.00 331,731.40 81,559.50 7,952.01 1,450.00 110.44 18.67 2,229.32 0.00 60,000.00 225,000.00 90,264.99 1,590,883.00 61,106.00 15,405.82 1,128,159.00 19,445.00 16,554.48 24,506.44 825.00 YEAR-END ADJUSTMENTS DEBIT CREDIT 12/31/2017 ADJUSTED TRIAL BALANCE DEBIT CREDIT 57,600.00 22,275.00 5,664.91 140,663.35 11,611.26 29,285.75 ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- NET INCOME me before Taxes NOTE: me Tax Expense ncome Check 139,462.53 ADJUSTED BALANCE SHEET DEBIT CREDIT ADJUSTED INCOME STATEMENT DEBIT CREDIT ----------------- ----------------- ----------------- ----------------- Debits and Credits in the Balance Sheet and Income Statement will not be equal. The difference is Net Income which hasn't been closed to retained earnings. Calculate the differences between debits and credits on the NET INCOME row for each statement. The amounts should equal and also equal the net income check figure WAREN SPORTS SUPPLY YEAR-END ADJUSTING ENTRIES DECEMBER 31, 2017 Entry # Account # 1 1 Account Title 40400 10900 Record depreciation expense for 2017 2 2 Debit Credit WAREN SPORTS SUPPLY BALANCE SHEET AT DECEMBER 31, 2017 AND 2016 ASSETS 2017 CURRENT ASSETS Cash Accounts receivable - net Inventory Notes Receivable Marketable securities Total current assets FIXED ASSETS - Net of accumulated depreciation Total assets LIABILITIES AND STOCKHOLDERS' EQUITY CURRENT LIABILITIES Accounts payable Payroll taxes withheld and payable Federal income taxes payable Interest payable Total current liabilities NOTE PAYABLE Total liabilities STOCKHOLDERS' EQUITY Common stock Retained earnings Total stockholders' equity Total liabilities and stockholders' equity LY D 2016 RS' EQUITY 2016 WAREN SPORTS SUPPLY STATEMENT OF INCOME AND RETAINED EARNINGS FOR THE YEAR ENDED DECEMBER 31, 2017 REVENUE (Net Sales) COST OF GOODS SOLD GROSS MARGIN OPERATING EXPENSES Rent expense Advertising expense Office supplies expense Depreciation expense Wages and salaries Payroll taxes Bad debt expense Other operating expense Total operating expenses Operating income OTHER INCOME - Miscellaneous income OTHER EXPENSE - Interest expense INCOME BEFORE TAXES FEDERAL INCOME TAXES NET INCOME RETAINED EARNINGS - Beginning of year RETAINED EARNINGS - End of year WAREN SPORTS SUPPLY STATEMENT OF CASH FLOWS FOR THE YEAR ENDED DECEMBER 31, 2017 CASH FLOWS FROM OPERATING ACTIVITIES CASH FLOWS FROM INVESTING ACTIVITIES CASH FLOWS FROM FINANCING ACTIVITIES NET INCREASE IN CASH CASH - Beginning of year CASH - End of year