I have no idea how to do this homework. I need some help.

Verhovek Co. December 31, 2011 ACCT NO. ACCOUNT TITLE 12-31-2010 POST CLOSING TRIAL BALANCE DEBIT CREDIT 12-31-2011 UNADJUSTED TRIAL BALANCE DEBIT CREDIT # ADJUSTMENTS DEBIT # CREDIT ADJUSTED TRIAL BALANCE DEBIT CREDIT INCOME STATEMENT DEBIT CREDIT BALANCE SHEET DEBIT CREDIT CLOSING ENTRIES DEBIT CREDIT 12/31/20 POST CLOS TRIAL BALA DEBIT ASSETS 10100 10200 10300 10400 10410 10500 10600 10700 10800 10900 11000 Cash Accounts receivable Allowance for doubtful accounts Inventory Allowance to reduce inventory to LCM Prepaid expenses Marketable securities Interest Receivable Fixed assets Accumulated depreciation Notes receivable 20100 20200 20300 20400 20500 20600 20700 20800 20900 21000 LIABILITIES Accounts payable Wages and salries payable Federal income taxes withheld State unemployment taxes payable Federal unemployment taxes payable F.I.C.A. taxes payable Federal incomes taxes payable Dividends payable Interest payable Notes payable 26000 27000 28000 29000 STOCKHOLDERS' EQUITY Common stock Paid-in capital in excess of par Income summary Retained earnings 30100 30200 30300 30400 30500 30600 30700 30800 30900 31000 31100 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 Gain/Loss on sale of fixed assets Gain/Loss on sale of marketable securities Interest/dividend income Miscellaneous revenue 40100 40200 40300 40400 40500 40600 40700 40800 40900 41000 EXPENSES Rent expense Advertising expense Office supplies expense Depreciation expense Wages and salaries expense Payroll tax expense Federal income tax expense Interest expense Bad debt expense Other operating expense Sub-totals Net Income (Loss) TOTALS CHECK FIGURES ERROR CHECKING 16,000.00 32,500.00 33,745.50 36,500.00 812.50 42,000.00 500.00 42,000.00 2,500.00 36,460.00 165,000.00 165,000.00 14,400.00 14,400.00 5,000.00 86,000.00 64,245.50 520.00 70.00 18.00 422.00 3,180.00 900.00 50.00 15.00 1,180.00 30,000.00 20,000.00 20,000.00 130,077.50 130,077.50 932,000.00 34,500.00 4,800.00 720,000.00 16,000.00 9,000.00 9,200.00 0.00 6,500.00 600.00 300.00 3,000.00 35,000.00 72,562.50 7,500.00 18,000.00 255,500.00 255,500.00 ----------------- --------------------------------- ----------------255,500.00 255,500.00 1,225,768.00 --------------------------------1,225,768.00 1,225,768.00 ----------------- ------ ----------------- ------ --------------------------------- ------ ----------------- ------ ----------------1,225,768.00 --------------------------------1,232,616.75 --------------------------------1,232,616.75 -----------939,400.00 ------------ 939,400.00 337,205.50 -------------------- -------------------- --------------------------------------- -------------------- -------------------337,205.50 939,400.00 939,400.00 337,205.50 1/2011 LOSING ALANCE CREDIT --------------------------------------337,205.50 Verhovek Co. - Chart of Accounts 10100 10200 10300 10400 10410 10500 10600 10700 10800 10900 11000 20100 20200 20300 20400 20500 20600 20700 20800 20900 21000 26000 27000 28000 29000 30100 30200 30300 30400 30500 30600 30700 30800 30900 31000 31100 31200 40100 40200 40300 40400 40500 40600 40700 40800 40900 41000 Cash Accounts receivable Allowance for doubtful accounts Inventory Allowance to reduce inventory to LCM Prepaid expenses Marketable securities Interest Receivable Fixed assets Accumulated depreciation Notes receivable Accounts payable Wages and salries payable Federal income taxes withheld State unemployment taxes payable Federal unemployment taxes payable F.I.C.A. taxes payable Federal incomes taxes payable Dividends payable Interest payable Notes payable Common stock Paid-in capital in excess of par Income summary Retained earnings Sales Sales returns and allowances Sales discounts taken Cost of goods sold Purchases Purchases returns and allowances Purchases discounts taken Freight-in Gain/Loss on sale of fixed assets Gain/Loss on sale of marketable securities Interest/dividend income Miscellaneous revenue Rent expense Advertising expense Office supplies expense Depreciation expense Wages and salaries expense Payroll tax expense Federal income tax expense Interest expense Bad debt expense Other operating expense Techniques for documenting & structuring your worksheet 1.00 The techniques described in this section will improve the quality of your project and make it far easier for you to find and repair mistakes. 1.01 Lookup tables Lookup tables ensure that your spreadsheet will use a consistent set of values. The requirement in this project to use a VLOOKUP formula is intended to familiarize you with the VLOOKUP function. The list of accounts on the ten column worksheet contains account numbers for some accounts, but not titles. In the HELP facility look up the VLOOKUP function and see how you could use this function and the chart of accounts to supply the account titles. Note the use of a range name (LittleChart) for the block of cells from E10 to F13). What happens if you change the contents of cell E13 to another value (e.g. 11000)? 10800 Fixed assets 10600 Marketable securities 10800 Fixed assets 10900 Accumulated depreciation 11000 Notes receivable In this project the chart of accounts is maintained in a lookup table keyed to a set of account codes. This ensures a consistent use of names and account titles. 1.02 Color-coding Using a disciplined and consistent approach to color-coding the contents of your worksheet allows the user to instantly comprehend the kind of information stored in a cell. Your copy of Verhovek uses the following color-code (this is good to do but will not be graded): Black Navy Blue Labels & Formulas Cell reference formulas Data and assumptions 70.00 70.00 200.00 1.03 Range names Providing a descriptive range name to a cell or a block of cells is an excellent way to document the contents of a worksheet. Open the Name Box (lower lefthand corner of the toolbars at the top of the screen) and click on the name TaxRate. What happens? Why? Tax Rate 35.00% 1.04 Freezing panes On the View tab click Freeze Panes and select the option you want. Scroll down to the bottom of the page, or over to the balance sheet columns. 1.05 Documenting assumptions and calculations Open a separate page in your file for all adjusting entry calculations, and neatly lable your data, key assumptions and any intermediate calculations. This will make it much easier to troubleshoot any errors. 1.06 Comments Insert / Comment 1.07 Graphical objects The Draw menu provides many graphical tools that can be used to document links and flows in the worksheet. The formulas are taken from an exercise in projected financial statements. (1+rSALESGROWTH)*Last Year Sales) Sales CoGS CoGS = Sales - GP Gross Profit GP = GPM * Sales 1.08 Conditional formatting & Error Flags Examine the two approaches to flagging and error condition. What happens if you change the value of total assets? Examine the contents of the two error-check cells in column G. Total Assets Total Liabilities & Shareholders' Equity Check Check 150.00 150.00 0.00 Writing formulas 2.00 In general, you should separate data & assumptions from calculations, especially for worksheets that will be periodically updated. Examine the following approaches to calculating tax expense: Best practice: Income before tax Tax rate Tax expense 1,000.00 35.00% 350.00 Advantages: Easy to understand calculation Easy to update values Dangerous Tax expense Disadvantages: Have to examine cell in order to understand calculation Easy to overlook when updating worksheet Bad practice Tax expense Disadvantages: No way to understand calculation Easy to overlook when updating worksheet 350.00 350.00 Techniques for structuring your worksheet 3.00 Minimize the number of chained cell-reference formulas, and maximize the number of opportunities to compare independently-calculated amounts. For example, the best practice in calculating tax expense would be to (a) compute the tax expense on the calculations page and bring it onto the worksheet via a cell reference formula, and to (b) recalculate the tax expense as the next-to-last step on the income statement. This affords you an opportunity to compare the two tax expense amounts - clearly, if they're not equal, you need to fix something. # ACCT NO. ACCOUNT TITLE example 40500 Wages and salaries expense 20200 Wages and salaries payable ADJUSTMENTS DEBIT CREDIT 1,000.00 1,000.00 # ACCT NO. ACCOUNT TITLE example 40500 Wages and salaries expense 20200 Wages and salaries payable ADJUSTMENTS DEBIT CREDIT 1,000.00 1,000.00 Verhovek Co. Income Statement and Statement of Retained Earnings For the Year Ended 12/31/2011 Sales Less: Sales returns and allowances Sales discounts taken Net Sales Cost of Goods Sold Gross Profit Operating Expenses Rent expense Advertising expense Office supplies expense Depreciation expense Wages and salaries expense Payroll tax expense Bad debt expense Other operating expense Total Operating Expenses Operating Income Other Income Interest Revenue (Expense) Income Before Taxes Income Tax Expense Net Income Beginning Retained Earnings Ending Retained Earnings Earnings Per Share ($1 Par Value Common Stock) Verhovek Co. Balance Sheet At December 31, 2011 and 2010 Assets Current Assets Cash Accounts receivable (Net) Inventory (Net) Prepaid expenses Marketable securities Total Current Assets Property Plant and Equipment (Net) Long Term Investments Total Assets Liabilities Current 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 Total Current Liabilities Notes payable Total Liabilities Stockholder's Equity Common stock Retained earnings Total Stockholder's Equity Total Liabilities and Stockholder's Equity Verhovek Co. Statement of Cash Flows For the Year Ended 12/31/2011 Cash Flows from Operating Activities Net Income Adjustments to reconcile net income to net cash from operating activities Depreciation Gain on Sale of Fixed Assets Gain on Sale of Marketable Securities Decrease (Increase) in Current Assets Accounts receivable (Net) Inventory (Net) Prepaid expenses (Decrease) Increase in Current 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 Net Cash Used by Operating Activities Cash Flows from Investing Activities Sale (Purchase) of fixed assets Sale of marketable securities Purchase of marketable securities Sale (Purchase) of note receivable Net Cash Used by Investing Activities Cash Flows from Financing Activities Cash Proceeds (Payments) on Loans Net Cash Used by Financing Activities Net Decrease in Cash Cash - Beginning of Year Cash - End of Year Verhovek Co. - Articulated Worksheet 1 In this project you will be completing a 12-column worksheet. If you don't know what that is, see page 18 of your SUA Reference book. The file titled \"Verhovek Co - Public File 2016.xlsx\" contains a partially complete worksheet. By completing the instructions set out below you will modify the worksheet and begin using a worksheet similar to the one in your Systems Understanding Aid. This project guides you through several important Financial Accounting and AIS topics, including: structured spreadsheet design, with proper separation of data and formulas advanced spreadsheet techniques, including formulas which use specialized functions documentation techniques, including range names, color coding, named pages and a clearly labeled assumptions page Before you begin the project, take a few minutes to acquaint yourself with the worksheet and, especially, the page labeled \"Support Material.\" 1 As you work on the worksheet you will find it convenient to freeze the title and header information, so that you can readily identify the contents of each cell. Set your cursor on cell C9 of the worksheet and click on Freeze Panes (View tab). 2 On the page labeled \"Chart of Accounts\" there is a boxed range that contains numbers and titles for accounts used in this project. Name the range ChartOfAccounts. (Note that a named range in Excel operates as an absolute cell reference.) If you are not familiar with naming cells, you should research this topic in the on-line help menu by clicking Help and then Contents and Index. Select the Index tab and type in \"name\". 3 Supply titles for all the worksheet accounts, using the VLOOKUP functions. Refer to section 1.01 in the \"Support Material\" page for an example of how this function works. Pay special attention to the three required arguments (pieces of information) for this function. Once you write a formula for one account, you can copy the formula to other cells. 4 Use the SUM function to total each column in the trial balance where appropriate: sub-totals, totals, and the Net Income row where there are blank cells. Set up error checking to highlight when the column sums do not equal the check figures. 5 Use the information provided below to make adjusting entries. Imagine that a co-worker will sometimes need to use this worksheet: make life easy for her by placing key amounts and rates in separate and clearly labeled cells, such that changing the value in any of these cells will result in the correct updating of all dependent cells. See the illustrative example on the page headed \"Support Material\" under the heading \"Writing Formulas\" if you are not familiar with this principle. Using separate cells for data and calculations is a best practice in spreadsheet design. The adjusting entry material comes from the first half of Intermediate Financial Accounting. You may find it useful to review various topics covered in that course. First make all of the Adjusting Journal Entries on the page labeled \"Adjusting Entries\" then post the amounts to the worksheet using cell reference formulas. Use the \"#\" columns to the left of the debit and credit columns to reference the 1 Thanks to Harry Howe of SUNY - Geneseo for developing this project and allowing me to use it. 1 problem below that results in an entry. For example, put a lowercase \"a\" in cell G57 to show that the debit in cell H57 (depreciation expense) is due to problem \"a\" below. a) Verhovek's Fixed Assets consists of its office and warehouse facility at TJ Park. Verhovek purchased the building at the beginning of 2008 (i.e., four calendar years prior to 12/31/2011). At the time of purchase Alice Verhovek (the controller) estimated a twenty-five-year life and $45,000 salvage value. Verhovek uses straight-line depreciation. Name the cells containing values for cost, salvage and life Cost, Salvage, and Life, respectively. Write the formula = SLN(Cost,Salvage,Life). b) Verhovek calculates the required balance in Allowance for Doubtful Accounts as a percentage of Accounts Receivable. Alice Verhovek expects that the company will collect on the same proportion of its receivables as it has in the past. c) Verhovek uses a periodic inventory system. A physical inventory count revealed $58,000 of inventory at cost. Close all appropriate accounts to account 30400, Cost of Goods Sold. Complete this work before you adjust inventory for LCM. (See the SUA reference guide for an example of an adjusting entry for COGS). d) The $30,000 note payable was issued on 9/1/2011 and bears simple interest at 9.50%, payable annually on the anniversary of the note. (Use a 30/360 year.) e) Verhovek uses accounts 10410 and 41000 to record inventory at Lower of Cost or Market (LCM). On 12/31/2011 the net realizable value of the inventory on hand was $56,000, and the estimated replacement cost was $57,000. Additional information: Normal Profit Margin = 30% Ceiling = NRV = Selling price minus normal selling costs Floor = NRV - Normal Profit Margin (NRV 70%) Use a MEDIAN function to compute Market Value, and a MINA function for LCM. f) Verhovek pays income tax at the statutory 35% rate; Income Before Taxes = 67,675.00 6 Write formulas for the adjusted trial balance, income statement and balance sheet columns in the worksheet. (For instance, account 30100, Sales, should appear in the ATB column as the sum of its TB amount, plus any credits in the adjustments column and minus any debits.) These formulas should allow changes to either the trial balance or adjustments column to flow through to the correct columns cells - in other words, if you change the amounts posted from an adjusting entry, the values in the appropriate (Balance Sheet or Income Statement) columns of the worksheet should automatically change. 7 When you have finished the worksheet, open new pages in the file for the Income Statement, Balance Sheet and Statement of Cash Flows, and use cell reference formulas that refer back to the appropriate cell in the worksheet page. For example, the cell on the balance sheet that contains the value of Cash for the year 2011 should contain the formula \"=Worksheet!O10\". Format the statements to print on one page in portrait orientation Review the discussion of financial statements in the SUA reference guide, and consider the difference between Statements and the Worksheet. Follow these stylistic guidelines in creating your statements: Show balance sheet accounts for both 2010 and 2011, with the current year to the left of the earlier year. Note that (a) this will make it easier for you to prepare your statement of cash flows and (b) that virtually all GAAP-compliant financial statements present multi-year information. 2 Use two columns for the figures in your Income Statement and Statement of Cash Flows. Place the Statement of Retained Earnings at the bottom of the Income Statement. Classify the Note Receivable as a long-term investment. Consider that it was purchased at the end of the year 2011 and that you should not accrue interest income for it in the current year. On your Statement of Cash Flows use an IF statement to control the labeling of the net increase or decrease in cash. See section 1.08 for an example of an IF statement. Test your worksheet by adding $10,000 to sales and cash in the unadjusted trial balance. Net Income should change to $50,488.75. If it does not, then adjust your formulas on the calculations page, adjusting entries page, and worksheet page using cell references instead of typing numbers. 8 Make all of the Closing Journal Entries for the year on the page labeled \"Closing Entries\" then post the amounts to the worksheet using cell reference formulas. After you are done write formulas for the post closing trial balance in the worksheet. Additional Information: Verhovek's Common Stock has a par value of $1/share. On Feb 21 the company bought 500 shares of LQM Corp. for $20,000, and sold all shares for a total of $26,500 on September 15. On December 31 it purchased $100,000 face value of zero-coupon bonds from the American Superconductor Corporation for $36,460. Time saving tips: Build formulas by pointing and left clicking; do not waste time and encourage errors by typing in lengthy cell references. You can considerably reduce the amount of formula writing that you need to do by copying. Pay careful attention to the debit and credit balances when you do this! All accounts in this problem should have normal balances. Check Figures: Net Income Ending Retained Earnings Earnings Per Share Total Current Assets Total Assets Total Current Liabilities Total Liabilities Total Stockholder's Equity Net Cash Provided by Operating Activities Net Cash Used by Investing Activities Net Cash Provided by Financing Activities 2011 43,988.75 174,066.25 2.20 164,293.00 315,093.00 91,026.75 121,026.75 194,066.25 22,705.50 (34,960.00) 30,000.00 2010 89,687.50 240,287.50 90,210.00 90,210.00 150,077.50 When you are done, upload the completed project to Blackboard. Make sure your file has an appropriate name, such as \"Last_name Verhovek Project.xlsx\". 3