ACCT 308 - Accounting Information Systems Excel Project Feral Wetsuits Company Year-End 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.

Feral Wetsuits Excel Assignment:

For this Excel assignment you will create a year-end workbook with multiple spreadsheets to convert the unadjusted trial balance of Feral Wetsuits to a set of complete financial statements and account analysis. You should recall the steps in the accounting cycle to 1) prepare an unadjusted trial balance, 2) determine and record adjustments, 3) create an adjusted trial balance, and 4) create financial statements: Balance Sheet, Income Statement, Statement of Cash Flows (Indirect method).

You will begin with the Year-end Worksheet template found on Cougar Courses that includes all the accounts and 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 Year-end Worksheet to create the adjusted trial balance. You will use the adjusted trial balance data to create financial statements in Excel.

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 and all data should be referenced from one cell to another.

Instructions:

Download the Feral Year-end Workbook from Cougar Courses. Save the file as: lastname_firstname_FW2022SPRING.

You will note that the December 31, 2020 Post Closing Trial Balance and December 31, 2021 Unadjusted Trial Balance are populated with numbers.

a.Do debits equal credits?(These are just questions to make you think. No submission is necessary)

i. Use the SUM function to total each column of the spreadsheet. b.Should the 2021 allowance for doubtful accounts be a debit or credit

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

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

3. ADJUSTMENT SHEET: Adjustments will be recorded on the Adjustment worksheet in the Excel Workbook.

Use theVLOOKUPfunction to populate account name after you input the account number. (Click on the hyperlink for support from Microsoft.com.) You might find using Labels/Range Namesis helpful to define the account numbers and titles.

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.

2021 Depreciation Expense is $35,109.50. Input this figure in the adjustments worksheet. Use the = to link the credit and debit cells.

Interest Expense: An $80,000, two-year note was signed and funded on December 16, 2020 with annual stated interest of 6% (that means interest starts accruing on December 17, 2021). Create a formula to calculate the adjustment amount based on a 365-day year. UseROUNDfunction to round to nearest penny.DO NOT TYPE IN A CALCULATED AMOUNT. Use the = to link the credit and debit cells.

Bad debt expense: Bad debt expense is 0.2% of net sales. Create a formula to calculate the adjustment amount. UseROUNDfunction to round to nearest penny.DO NOT TYPE IN A CALCULATED AMOUNT. Use the = to link the credit and debit cells.

Inventory and cost of sales adjustment: Feral Wetsuits Company uses the periodic inventory method. A physical inventory was taken at midnight on 12/31/21. The cost basis of the inventory on hand is $198,246.00. Adjust the inventory balance and close out the purchase and related accounts to cost of sales.

Income tax expense: Income tax expense cannot be calculated until all other adjustments are posted and the income before income taxes

reflects all adjustments. This will be calculated later in the

assignment. Use the = to link the credit and debit cells. h. Link the adjustments to the Year-end Worksheet in the Adjustments

columns. Since there is only one adjustment per account, you can do this by using the = or + functions. (In a more complex company with multiple adjustments for each account, you might use theSUMIFfunction to bring adjustments forward to the Year-end Worksheet.)

Use formulas to combine the Unadjusted Trial Balance and Adjustments to create the Adjusted Trial Balance. Many accounts can be either a net debit or a net credit in the Adjusted Trial Balance. Consequently,IF statementsmust be used to determine if the balance is a debit or credit. The IF statement should place a blank if the criteria are not met, for example, =IF (debits- credits>0, debits-credits, "")replace debits and credits with cell references for the unadjusted trial balance and the adjustment columns. Copy the formulas to all cells in the total columns.

After completing steps 3 and 4, you are ready to calculate income tax expense. A table is included on the Year-end Worksheet to help you calculate income tax expense.

Calculate income before income taxes using the Adjusted Trial Balance data. Income is equal to revenues minus expenses. In the accounting system, income is equal to credits minus debits. Create a formula in cell F59 on the Year-end Worksheet to calculate income before taxes, e.g., SUM(credits)-SUM(debits). (Do not bring income before tax from the income statement because you haven't created the income statement yet.)

The marginal income tax rates are as follows: Income before Tax Rate < $50,000 15% $50,000 to $75,000 25% >$75,000 36%

Use anIF statementto 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 reference from cell F61 on the Year-end Worksheet. Bring the adjustment to the Year-end Worksheet in the adjustment columns.

All adjustments are now complete.

Populate the Balance Sheet and Income Statement debit and credit columns on the Year-end 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 below to compare to Net Income.

Complete the Financial Statements on the appropriate worksheets. The Statement of Cash Flows uses the indirect method.

a. Use formulas to insert the correct figures into the Financial Statements worksheets from the Year-end Worksheet balance sheet or income statement columns.

i. December 31, 2021 retained earnings in the Balance Sheet will pull from the Income Statement because the temporary accounts haven't been closed.

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

All amounts in the financial statements should be rounded to the nearest whole number(I.e., NO DECIMALS SHOWN)

On the Statement of Cash Flows, you can bring the balances from the Year-end 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.

8. The assignment is due by the start of your class time onFEBRUARY 14, 2022. Your electronic submissionMUST BE in EXCEL format. Google Docs, Open Office, Numbers, etc. are not acceptable formats.

a. Upload using the Turnitin submission link in the Excel Project section of the Cougar Courses website. Turnitin will separate the pages, but I will be able to see and download your work.