ferekids.blogg.se - Function microsoft excel 2007

Secondly, we apply VLOOKUP function to forward the basic data in the "account" worksheet into "worksheet". The account numbers are stored in the 1 st column of the range, as shown in the following illustration. For instance, you have a list of accounts contained in the range A3:B66 in "Account" worksheet. You can use the VLOOLUP function to search the 1 st column of a range of cells, and then return a value from any cell on the same row of the range. Now, we are going to describe how to use VLOOKUP formula in Microsoft Office Excel. For the various overdue periods, we apply IF, year ( ), month ( ), date ( ) function to sort company's vendors by the overdue period. Whenever you enter a vender's name in the corresponding blank cell, it will show the vendor's whole info on the right hand column.įor the Summery worksheet, we use pivot table to combine certain info into one table to generate A/P aged summary.

We use the V-Lookup function in the "Purchase" worksheet as well.

So the system can record a list of all vendors' info in "Ventdetl". We can do this by clicking "confirm" button.

In the "Purchase invoice" worksheet, we apply the macro function to automatically post vendor's info into the "Ventdetl" worksheet.

The line freezes the vendor name column so that the input cannot be changed.

As you can see, the black solid line between the vendor name column and street column has a special purpose.

For the "Vendor" worksheet, all data are entered directly form original resources.

how the company's Financial Statement will change if the major expenses decrease by :.

how the company's Financial Statement will change if the revenue increase by 10%.

By choosing different scenarios, you can know They are "Original", "Increase revenue by 10%" and "Decrease expenses". In the work sheet, you can have 3 scenarios.In the cell B37 (Work sheet), it will tell whether the company has "Net Income", "Net Loss" or just at "break even" in this year.In the " Balance Sheet " (Work sheet), the "Debits" Column and "Credits" Column will automatically show the amount of Asset Accounts, Liability Accounts and Owner's Equity Accounts.

These cells use the IF function to work out the results.

In the " Income Statement " (Work sheet), the "Debits" Column and "Credits" Column will automatically shows the amount of Revenue Accounts and Expense Accounts.If the account number is not legal, the column B will warn you that the input is illegal. In "Work sheet", when you input the "Account No" in column A, the column B will automatically show the matching account name.You can get 10 results from this module by inputting numbers into the worksheet: This is a simple module designed for A/P (account payable). *If Internet Explorer is in use, please change the file extension "zip" into "xlsx". To open this file, MS office 2007 is required and it is highly recommended you can watch THIS VIDEO on how to enable Macro to work in Excel.