Do My Essay!
Do not waste time. Get a complete paper today.
Our leading custom writing service provides custom written papers in 80+ disciplines. Order essays, research papers, term papers, book reviews, assignments, dissertation, thesis or extensive dissertations & our expert ENL writers will easily prepare a paper according to your requirements.
You’ll get your high quality plagiarism-free paper according to your deadline! No Bullshit!!
Waren Sports Yearend Worksheet
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
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: 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 for December 31, 2017. The instructions below give you the information
needed to calculate and record adjustments in Excel, and to add these adjustments
to the unadjusted figures 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 last minute
adjustment to accrue legal expenses should automatically update the legal expense
and liability, the 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. 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 necessary to summarize the
information. Excel Instructions:
1. Download the Waren Sports Supply Yearend Workbook from BlackBoard.
Save the file as: lastnamefirstnameSpring2017.
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
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-GENERAL: 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 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.
b. Calculate the adjustment amount. See the instructions below for each
adjustment. If the formula is in the debit column, you can use the =
function to insert the calculated amount in the credit column. (you
should not duplicate the calculation)
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, =VLOOKUP(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 new formula becomes =VLOOKUP(C6,A4.B53,2,false). The
range changed, but you wanted it to stay A3.B52. 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. (you can
also select the cell reference and click on F4) 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, lets define the account number
and account name as ACCOUNT, which is A3.B52. The formula is now
VLOOKUP(C5,ACCOUNT,2,false). When the absolute cell reference or
the named range is copied, the original cell references are retained.
4. ADJUSTMENTSexcluding income tax expense:
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
3. Bad debt expense: Bad debt expense is estimated at 0.7% 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 $25,000
on December 21, 2017. They financed 100% of the purchase
from Bank of America under a 5-year, $25,000 note at 4%
interest. The first payment is due January 31, 2018. (Do not
record depreciation for 2017.) Record the purchase of the
truck and accrue interest.
ii. Waren Sports sold $12,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
1. To record this entry you will need to insert a new row
and add a new account, 10500 Notes Receivable, to the
2. Because the bad debt expense adjustment (#3 above)
did not include this item, you need to include an
adjusting entry to add 0.7% of this item to the bad debt
3. No inventory or Cost of Goods Sold adjustment is
necessary because Waren uses the periodic inventory
method and the items were excluded from the physical
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
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. =SUMIF(B:B,A13,D:D) has the criteria account number
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. Most of these are
zero, but the formula in needed if a late adjustment is added.
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 argument is true and a blank if the
argument is false, for example, =IF(debits-credits>0, debits-credits, ).
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 havent
created the income statement yet.)
b. The Federal income tax rates are as follows:
Pre-tax income is
75,000 But not over:
.. Tax is:
$7500 + 20%
$13,750 + 25% Of amount over:
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
taxyour 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 the Income Tax Expense calculation on the Yearend
f. The SUMIF function you previously used will bring the adjustment to
the Yearend Worksheet in the appropriate column.
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 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.
9. Complete the Financial Statements on the appropriate worksheets. You must
properly format the financial statements with $ signs, underscore, double
underscore, and use the same font for all items.
a. 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: b.
f. 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 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.
The Statement of Cash Flows uses the indirect method. At the end of
this document you will find extracts from ASC 230, Statement of Cash
Flows, to help you classify the note receivable from SDSU and the bank
loan for the truck.
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. Ensure the
statements are properly formatted. ASC 230, Statement of Cash Flows
Loan and trade receivables:
Loans and trade receivables The appropriate classification in the statement of cash
flows of a commercial entity for cash flows related to loans and trade receivables
requires a determination of the source of the loans or receivables. If the loans or
receivables result from the sale of the entitys goods or services to its customers, the
resulting cash receipts are always operating cash inflows, as cash receipts from
accounts and both short- and long-term notes receivable from customers, whether
by collection from the customer or sale of the notes or receivables, are operating
cash flows (ASC 230-10-45-16).
——————————————————————————————-Seller financed Purchases and third-party financed purchases:
Seller financed purchases Seller financed purchases are considered to be noncash
investing (or operating depending on the asset acquired) and financing transactions
for the buyer to the extent of the initial seller financing. These transactions are
excluded from the statement of cash flows but are disclosed in accordance with ASC
230-10-50-3 through 50-6. In the basis of conclusions for Statement 95, the Board
described its view that excluding noncash investing and financing transactions from
the statement of cash flows and disclosing them separately achieves cash flow
reporting objectives without increasing implementation difficulties. For example,
the purchase of equipment that is wholly financed by a seller is recognized as a
noncash investing and financing transaction. However, if only a portion of the
purchase is financed through the seller, the amount paid before, at the time of, or
soon after the purchase is reported as an investing cash outflow. In both of these
cases, the amount of seller financing is disclosed by the purchaser as a noncash
investing and financing transaction.
In contrast, if a purchase of equipment is financed through a third-party (i.e., not the
seller), the entire amount of cash paid for purchase of the equipment is an investing
activity while the cash received from borrowing is a financing cash inflow. Whether
financing is obtained from the seller or from a third party, subsequent principal
payments against the financing are reported as cash outflows from financing