Could you please take a look at these assignments, and give me a suggestion for your help. They are a little complicated and difficult, and I really need your help. Thank you

Part.Page # 1 1 1 1 Write a formula that would indicate if the sum of the first column of numbers does not equal the sum of the second column by displaying either Equal or Not Equal and if not equal show Not equal by { and enter the amount of the difference } 1 2000 1 1000 2345.5 1 500 1 2500 1 2 2 On the sheet called Income Statement, in cell E14 create the formula for the Income Tax. 2 2 3 3 3 On the sheet called Income Statement, make cell C14 is the only cell that the enduser can type in. Protect the sheet without a password. 3 4 4 4 Freeze column A of the spreadsheet to make sure it does not scroll off the left side of the screen. 4 5 5 The only valid entry is an account number from the chart of accounts 5 chart of accounts 1110 cash 1130 prepaid 1211 office equip 5 5 5 5 6 6 Custom format cell to display "The report is due on Tuesday, February 28, 2012" 6 6 2/28/2012 6 6 7 7 Display an error if there is a value in the Current and Late cells for the same invoice. 7 7 7 7 7 Invoice AM1254 AM1255 Amount 120.00 200.00 Current Late 100.00 20.00 200.00 7 7 7 7 8 8 8 8 8 8 8 8 8 8 8 8 8 9 Write a formula that will finds the code for the color or return #N/A if the color is not on the list. Color Code Red Blue Yellow Orange Green Black White 101 102 103 104 105 106 107 Green 9 Write a formula that sums the assets 9 9 Amount 9 9 1110 Cash 1500 9 1130 Prepaid Insurance 1000 9 5100 Telephone 200 9 5120 Rent 1140 Accounts Receivable 400 600 9 9 9 9 10 10 Format the cell to show the amount owed after the words "You owe $100.00." 10 10 Amount 10 100 10 10 10 11 11 Indicate if the balance is different than the normal balance. 11 11 1110 1130 3100 2200 11 11 11 11 Cash Prepaid Ins Capital Stock Dividends Debit Debit Credit Debit Account 1110 3100 Balance -$100.25 $200.00 11 11 11 11 11 11 12 12 12 12 On the sheet called Invoice name the range A2:A27 , Dates and the range B2:B27, Amount. Write a formula using the named range to determine the last date and the largest amount. Last Date Largest Amount 12 12 13 13 Roundup to the next number of cans and use cans in the format. 13 13 2,123.60 2,124 cans 13 13 14 14 14 14 Write a formula that will indicate that ALL the values are " All Greater than 10" or that they are "Not all greater than 10". 12.00 14.00 45.00 14 14 14 15 15 15 15 15 15 15 15 Displays "Markdown" if the product is a Marker, or if the product is a Red Pen that costs more than $6. Displays "Regular" if not. Product Color Price Pen Red 2.00 Marker Black 6.00 Pen Red 7.00 Marker Black 4.00 15 Pen Red 5.00 15 Marker Black 3.00 15 15 16 16 Indicate the sum of the prices of the product selected 16 . Marker 16 16 17 17 Follow the rules in the following table and write "Discount" or "No Discount" 17 17 RED and >10 Discount 17 GREEN and >15 Discount 17 Blue and > 20 Discount 17 17 17 17 RED 12.00 Byte of Accounting Income Statement For the Period Ending June 30, 2009 Revenue 4100 Computer Revenue $ 600.67 Expenses 5090 Interest Expense 5100 Insurance Expense Operating Income 5120 Tax Expense Net Income After Taxes $ 100.00 200.00 200.00 $ 400.67 40% $ 400.67 Dates Amount 12/5/2012 500.00 10/4/2012 400.00 12/9/2012 550.00 4/5/2012 600.00 9/8/2012 520.50 11/11/2012 420.50 9/10/2012 570.50 11/15/2012 620.50 3/12/2012 541.00 8/15/2012 441.00 10/18/2012 591.00 8/17/2012 641.00 10/22/2012 561.50 2/17/2012 461.50 7/22/2012 611.50 9/24/2012 661.50 7/24/2012 582.00 9/28/2012 482.00 1/24/2012 632.00 6/28/2012 682.00 8/31/2012 602.50 6/30/2012 502.50 9/4/2012 652.50 6/4/2012 702.50 8/7/2012 623.00 6/6/2012 523.00 On the sheet name Depreciation Schedule Create a Double declining Depreciation Schedule that restricts: Cost restricted to positive numbers Scrap restricted to positive numbers Less than the cost Life restricted to between 3 and 10 years And shows only years that are equal to or less than life Use Named Ranges The Accounting System An Accounting System By Mark Friedman Part 1 Getting Started .........................................................................................2 Part 2 Application Controls ................................................................................7 Part 3 Catching Errors ......................................................................................11 Part 4 Data Validations .....................................................................................18 Part 5 The Trial Balance....................................................................................28 Part 6 The Worksheet........................................................................................38 Part 7 The Financial Statements ......................................................................50 The Accounting System Part 1 Getting Started Start on a new Worksheet and type the headings. Select cells A1,B1,C1,F1,G1 by holding down the CTRL key, and change the appearance to 45 degrees. Change the format by clicking: Format, Format Cells, Alignment, and Orientation to 45 degrees. Transaction #1: On June 1st, Lauryn invested $90,000 in cash and received shares of stock. Enter the date, transaction, and account. Version O Part 1 Getting Started Page 2 The Accounting System Rename Sheet 1, \"General Journal\" and Sheet 2, \"Chart of Accounts\" by double-clicking on the tab and entering the new name. Select the "Chart of Accounts" sheet, enter the data, and select A2:B12. In the Name Box, type chart_of_accounts. Select the "General Journal" sheet. In cell D2, type in the lookup formula, =VLOOKUP(C2,Chart_of_Accounts,2). Version O Part 1 Getting Started Page 3 The Accounting System In cell C2, change the account number to 2202. But what happens when we enter 1111 in cell C2? In cell D2 type: =VLOOKUP(C2,chart_of_accounts,2,FALSE) But, now it shows a system error, which does not explain what caused the problem. In cell D2 type: =IFERROR(VLOOKUP(C2,chart_of_accounts,2,FALSE),"Not a valid Account") Version O Part 1 Getting Started Page 4 The Accounting System Type in the description, the debit, and the account number for Capital Stock. A line will be skipped between transactions. If an account number is entered in column C, repeat the previous Date, Transaction, and Description. In cell A3 type: =IF(C3>0,A2,"") In cell B3 type: =IF(C3>0,B2,"") In cell E3 type: =IF(C3>0,E2,"") For cells A2:A3, change the format to show Jun 01 by clicking Format Cells, Number, Custom. Then, type mmm dd. For cells B2:B3, change the format to show 01 by clicking Format Cells, Number, Custom. Then, type 00. Copy cell D2 to D3, and enter the credit of 90000. Version O Part 1 Getting Started Page 5 The Accounting System Copy A3:E3 thru row 5, and delete cells C4:C5. Select cell D4, change the formula and copy it to D2, D3 and D5: =IF(C4>0,IFERROR(VLOOKUP(C4,chart_of_accounts,2,FALSE),"Not a Valid Account"),"") Version O Part 1 Getting Started Page 6 The Accounting System The General Journal Part 2 Application Controls We are going to skip a line between each of the four line journal entries. Holding down the ctrl key, select each of the cells highlighted below (A2, C2:C5, E2, F2:G5), fill them with yellow, and select the border. Copy from A2:G6 to A7, and delete all the values in the yellow filled cells except for the date. Version O Part 2 Application Controls Page 7 The Accounting System Enter the formula in B7 so that the transaction automatically increases by one: =IF(C7>0,B2+1,"") Enter the formula in A7 so that the date remains the same as the previous entry: =IF(C7>0,A2,"") Transaction #2: On June 1st, Jeremy invested computer equipment with a fair market value of $37,000 and cash of $28,000. He received 2,600 shares. Enter the transaction as presented below: The debits do not equal the credits but the end user is unaware of this error. Insert two rows at the top of the sheet. Type \"General Journal\" in cell A1, then select cells A1:G1, Format Cells, Alignment, Center Across Selection. In cell E2, enter a formula that will indicate that the debits are equal to the credits. Change the font color to red and change the font style. Version O Part 2, Application Controls Page 8 The Accounting System But how much are we out of balance by? Change the formula in E2 to: =IF(SUM(F:F)=SUM(G:G),"","NOT IN BALANCE BY "&TEXT(SUM(F:F)-SUM(G:G), "$###,###.00")) To change values to a charter string use the function: =Text(value,format) To indicate which entries are not in balance, copy the formula to cell E8. Change the formula to indicate that the transaction number not in balance: =IF(SUM(F9:F12)=SUM(G9:G12),"","Transaction #"&B9&" NOT IN BALANCE BY "&TEXT(SUM(F9:F12)-SUM(G9:G12),"$###,###.00")) . Version O Part 2, Application Controls Page 9 The Accounting System Copy the balance verification to Transaction #1. Copy row 8 (right click on the 8, copy) to row 4 (right click on the 4, insert copied cells). Enter a 1 in cell F7. In cell G11, enter the correct credit, $65000, and the error message disappears. Also, remove the 1 from cell F7. Version O Part 2, Application Controls P a g e 10 The Accounting System Part 3 Catching Errors To make sure the titles and the error message does not scroll off the top of the screen as we are entering transactions, select row 4 (click on the 4), then click View, and then Freeze Panes. Copy Transaction #2. Copy A9:G13 to A14. Delete the values in the cells highlighted in yellow (C15:G18) but not the date. Transaction #3: On June 1st, Courtney invested computer equipment with a fair market value of $15,000, office equipment with a fair market value of $1,250, and cash of $53,750. She received 2,800 shares. Enter the transaction as presented below: Version O Part 3 Catching Errors P a g e 11 The Accounting System To notify the user that there are two numeric values on the same line, go to cell I17 and type the following formula: =IF(AND(F170,G170),\"Debit and Credit on Same Line \