Waren Sports Yearend Worksheet

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:

Download the Waren Sports Supply Yearend Workbook from BlackBoard. Save the file as: lastname_firstname_WSS2016fall. You will note that the December 31, 2016 Post Closing Trial Balance and December 31, 2017 Unadjusted Trial Balance are populated with numbers.Do debits equal credits? Use the SUM function to total each column of the spreadsheet.Should the 2017 allowance for doubtful accounts be a debit or credit balance?How could this happen? You will make an adjustment later.Why are 2017 retained earnings the same as 2016?When is net income or loss recorded in retained earnings?We will not close out the temporary accounts at this time.Year-end adjustments: Adjustments will be recorded on the Adjustment worksheet in the Excel workbook. First, type the account number in the Account # column, then use the VLOOKUP function to populate account nameDO 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. 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. 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.ADJUSTMENTS:Depreciation Expense: The amount of depreciation expense is $35,023.64. Input this figure in the adjustments worksheet.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. 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. 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.)

• 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.

• 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.)
• 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%.
• To record this entry you will need to add a new account, 10500 Notes Receivable, to the yearend worksheet.
• 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.

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. 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.Place the SUMIF formula in every cell under the debit and credit Adjustment columns on the yearend worksheet. 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.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.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 havent created the income statement yet.)The Federal income tax rates are as follows: 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 taxyour formula needs to work at all levels.)After calculating tax expense, you can determine Net Income. The Net Income check figure is provided. 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. The SUMIF function you previously used will bring the adjustment to the Yearend Worksheet.

• 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 creditswhy? The same conditions apply to the Income Statement. Calculate the difference at the bottom of the columns and compare these amounts to Net Income.
• Complete the Financial Statements on the appropriate worksheets.
• The Statement of Cash Flows uses the indirect method.
• 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:
• 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 hasnt been adjusted for the amount of 2017 net income.
• Calculations in the financial statements should be limited to sum, plus, and minus to aggregate or subtract accounts.
• 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.
• Compare the ending cash balance on the Statement of Cash Flows to the Balance Sheet cash. The amounts should equal.
• Remove the gridlines from the financial statements.

• Youre done! Submit your work in EXCEL format. Google Docs, Open Office, Numbers, etc. are not acceptable formats.
• 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 Yearend Worksheet

Introduction:

Accountants use Excel to analyze transactions and accounts,prepare financial statements, calculate budgets, create invoices, and many moretasks. Mastering the basics of Excel iscritical to your success. You shouldalready know how to create spreadsheets using common mathematical formulas andExcel Functions. In AIS, you willdevelop or improve your skills linking multiple spreadsheets; creating formulasusing Excel functions IF, VLOOKUP, ROUND, SUMIF, Ranges; and creating PIVOTtables.

For the Excel assignment you will create a yearend workbookwith multiple worksheets to convert the unadjusted trial balance of Waren SportsSupply to a complete set of financial statements. You will follow the steps in the accountingcycle to 1) prepare unadjusted trial balance, 2) determine and recordadjustments, 3) create adjusted trial balance, and 4) create financialstatements: Balance Sheet, Income Statement, Statement of Cash Flows (Indirectmethod).

You will begin with the Yearend Worksheet template found onBlackBoard that includes all the account numbers and names along withunadjusted trial balance figures. Theinstructions below give you the information needed to calculate and recordadjustments in Excel, and to add these adjustments to the Yearend Worksheet tocreate the adjusted trial balance. Youwill 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 from15% to 17% should automatically update your tax expense/accrual adjustment, theadjusted 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 separatinginputs, calculations and reports into separate sections or sheets. Keeping these separate makes it easier toupdate the spreadsheets with new data, especially if the spreadsheet is givento another user to update. In thisassignment, all inputs are limited to the Yearend Worksheet and Adjustmentsworksheet. Calculations are made onthese two sheets. The Balance Sheet,Income Statement and Statement of Cash Flows are reports with limited functionsfor adding and subtracting figures.

Waren Sports Yearend Worksheet

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:

Download the Waren Sports Supply Yearend Workbook from BlackBoard. Save the file as: lastname_firstname_WSS2016fall. You will note that the December 31, 2016 Post Closing Trial Balance and December 31, 2017 Unadjusted Trial Balance are populated with numbers.Do debits equal credits? Use the SUM function to total each column of the spreadsheet.Should the 2017 allowance for doubtful accounts be a debit or credit balance?How could this happen? You will make an adjustment later.Why are 2017 retained earnings the same as 2016?When is net income or loss recorded in retained earnings?We will not close out the temporary accounts at this time.Year-end adjustments: Adjustments will be recorded on the Adjustment worksheet in the Excel workbook. First, type the account number in the Account # column, then use the VLOOKUP function to populate account nameDO 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. 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. 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.ADJUSTMENTS:Depreciation Expense: The amount of depreciation expense is $35,023.64. Input this figure in the adjustments worksheet.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. 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. 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.)

• 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.

• 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.)
• 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%.
• To record this entry you will need to add a new account, 10500 Notes Receivable, to the yearend worksheet.
• 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.

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. 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.Place the SUMIF formula in every cell under the debit and credit Adjustment columns on the yearend worksheet. 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.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.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 havent created the income statement yet.)The Federal income tax rates are as follows: 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 taxyour formula needs to work at all levels.)

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

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. The SUMIF function you previously used will bring the adjustment to the Yearend Worksheet.

• 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 creditswhy? The same conditions apply to the Income Statement. Calculate the difference at the bottom of the columns and compare these amounts to Net Income.
• Complete the Financial Statements on the appropriate worksheets.
• The Statement of Cash Flows uses the indirect method.
• 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:
• 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 hasnt been adjusted for the amount of 2017 net income.
• Calculations in the financial statements should be limited to sum, plus, and minus to aggregate or subtract accounts.
• 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.
• Compare the ending cash balance on the Statement of Cash Flows to the Balance Sheet cash. The amounts should equal.
• Remove the gridlines from the financial statements.

• Youre done! Submit your work in EXCEL format. Google Docs, Open Office, Numbers, etc. are not acceptable formats.
• 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 Yearend Worksheet

Introduction:

Accountants use Excel to analyze transactions and accounts,prepare financial statements, calculate budgets, create invoices, and many moretasks. Mastering the basics of Excel iscritical to your success. You shouldalready know how to create spreadsheets using common mathematical formulas andExcel Functions. In AIS, you willdevelop or improve your skills linking multiple spreadsheets; creating formulasusing Excel functions IF, VLOOKUP, ROUND, SUMIF, Ranges; and creating PIVOTtables.

For the Excel assignment you will create a yearend workbookwith multiple worksheets to convert the unadjusted trial balance of Waren SportsSupply to a complete set of financial statements. You will follow the steps in the accountingcycle to 1) prepare unadjusted trial balance, 2) determine and recordadjustments, 3) create adjusted trial balance, and 4) create financialstatements: Balance Sheet, Income Statement, Statement of Cash Flows (Indirectmethod).

You will begin with the Yearend Worksheet template found onBlackBoard that includes all the account numbers and names along withunadjusted trial balance figures. Theinstructions below give you the information needed to calculate and recordadjustments in Excel, and to add these adjustments to the Yearend Worksheet tocreate the adjusted trial balance. Youwill 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 from15% to 17% should automatically update your tax expense/accrual adjustment, theadjusted 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 separatinginputs, calculations and reports into separate sections or sheets. Keeping these separate makes it easier toupdate the spreadsheets with new data, especially if the spreadsheet is givento another user to update. In thisassignment, all inputs are limited to the Yearend Worksheet and Adjustmentsworksheet. Calculations are made onthese two sheets. The Balance Sheet,Income Statement and Statement of Cash Flows are reports with limited functionsfor adding and subtracting figures.