Excel 2007

EXCEL BUSINESS MODEL
- 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. *If Internet Explorer is in use, please change the file extension "zip" into "xlsx".

This is a simple module designed for A/P (account payable).

You can get 10 results from this module by inputting numbers into the worksheet:

In "Work sheet", when you input the "Account No" in column A, the column B will automatically show the matching account name. If the account number is not legal, the column B will warn you that the input is illegal.
In the "Income Statement" (Work sheet), the "Debits" Column and "Credits" Column will automatically shows the amount of Revenue Accounts and Expense Accounts. These cells use the IF function to work out the results.
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 areas also use the IF function.
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. This is worked out by IF function.
In the work sheet, you can have 3 scenarios. They are "Original", "Increase revenue by 10%" and "Decrease expenses". By choosing different scenarios, you can know

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

5% for COGS
10% for Wages
10% for Salaries

For the "Vendor" worksheet, all data are entered directly form original resources.
As you can see, the black solid line between the vendor name column and street column has a special purpose. The line freezes the vendor name column so that the input cannot be changed.
In the "Purchase invoice" worksheet, we apply the macro function to automatically post vendor's info into the "Ventdetl" worksheet. We can do this by clicking "confirm" button. So the system can record a list of all vendors' info in "Ventdetl".
We use the V-Lookup function in the "Purchase" worksheet as well. 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.

For the Summery worksheet, we use pivot table to combine certain info into one table to generate A/P aged summary. For the various overdue periods, we apply IF, year ( ), month ( ), date ( ) function to sort company's vendors by the overdue period.

Now, we are going to describe how to use VLOOKUP formula in Microsoft Office Excel. 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. For instance, you have a list of accounts contained in the range A3:B66 in "Account" worksheet. The account numbers are stored in the 1^st column of the range, as shown in the following illustration.

Secondly, we apply VLOOKUP function to forward the basic data in the "account" worksheet into "worksheet". If you know the account's number, you can use the VLOOKUP function to return the account name. To obtain the name of account number 1080 in cell B4, you can use the formula= VLOOKUP (A4, Account! A6:B66, 2). See the following picture:

This formula searches for the value 1080 in the 1^st column of the range A3:B66 in "Account" worksheet, and then returns the value that is contained in the 2^nd column of the range and on the same row as the lookup value ("Cash in Bank")

You may ask what if the number typed in is not included in the account number of "account" worksheet. In order to solve this problem, we introduce the combination of IFERROR and VLOOKUP functions.