Business Check Register - Excel

Document Sample
Business Check Register - Excel Powered By Docstoc


                                                       Personal and Business Finance
                                               Filters, Macros & Advanced Functions
Monthly Check Register:
        Check#                      Date                     Payee                 Amount                  Category               Tax
         1001                            9/1/2006    Kroger                   $          16.98       Food                No
         1002                            9/2/2006    City of salem                       88.15       Utilities           Yes
         1003                            9/2/2006    Visa                              232.79        Loan                Yes
         1004                            9/2/2006    Food Lion                           43.20       Food                No
         1005                            9/5/2006    Blockboster                          8.45       Entertainment       No
         1006                            9/7/2006    Pizza Hut                           17.15       Food                No
         1007                            9/7/2006    Bell Atlantic                       34.45       Utilities           Yes
         1008                            9/7/2006    Inspection                          10.00       Auto                Yes
         1009                            9/9/2006    Blockboster                         10.37       Entertainment       No
         1010                           9/10/2006    RC Bookstore                        78.34       Education           Yes
         1011                           9/15/2006    Kroger                              22.25       Food                No
         1012                           9/15/2006    Mastercard                        176.29        Loan                Yes
         1013                           9/15/2006    Roanoke Gas                         23.45       Utilities           Yes
         1014                           9/15/2006    First Union                       133.67        Loan                Yes
         1015                           9/15/2006    Blockboster                          8.45       Entertainment       No
         1016                           9/16/2006    Library                             54.17       Food                No
         1017                           9/17/2006    Kroger                              12.90       Food                No
         1018                           9/23/2006    Books-a-million                     28.90       Education           Yes
         1019                           9/19/2006    Mill Mt. Theater                    34.89       Entertainment       No
         1020                           9/28/2006    Pillis Brothers                   112.25        Auto                Yes

Using Data Base Functions:

Getting a Loan:
                                         Loan Information:                                                                     Condition:    Description   % Financed
                            Item to Be Purchased:            Car                                                                   1        New                    100%
                           Cost of Item:             $              15,000                                                         2        Used Good               80%
                           Condition:                                     2                                                        3        Used Fair               70%
                           Number of years:                               5                                                        4        Used Poor               60%
                           Interest Rate:                              10%
                           Payment Limit:            $              250.00

                                                     VLookup()                Choose()
                                Percent Financed:
                                Amount Financed:
                           Down Payment Needed:
                                                                                                                RATE() Calculated Rate:
                 PMT()            Annual Payment:                                                               NPER() # of Periods(Mon):
                 PMT()           Monthly Payment:
               PPMT()           Principle Payment:
                IPMT()           Interest Payment:

Investing in Ventures:
                           Interest Rate:                               10%

                                 Venture A               Venture B                Venture C
     Initial Investment      #################        ###############          ###############

     Year 1 Cashflow                        20,000                  28,000                  35,000
     Year 2 Cashflow                        15,000                  18,000                  25,000
     Year 3 Cashflow                        15,000                  10,000                  25,000
     Year 4 Cashflow                        10,000                  10,000                  18,000
     Year 5 Cashflow                        10,000                   5,000                  15,000

Internal Rate or Return:                                                                             IRR()
     Net Present Value:                                                                              NPV()

Putting Money Away for Future:
    Monthly Payment:         #################                                                           Future Value:                      FV()
              Years:                         10                                                         Present Value:                      PV()
       Interest Rate:                      10%

Depreciating Assets:
                                         Item Information
                           Item Purchased:                   Van
                           Purchase Price:                       $ 15,000
                           Useful Life:                                  5
                           Salvage Value:                        $ 3,000

                                                             Year                    Year                    Year                Year              Year
                                                              1                       2                       3                   4                 5
                                    Straight Line:                                                                                                         SLN()
                                Double Declining:                                                                                                          DDB()
                                Sum of The Years:                                                                                                          SYD()

                                 Spreadsheet Class Exercise #3
                                BUAD 258 - Information Systems I

                                 Personal and Business Finance

For this exercise, copy file FINANCE.XLS from DATA directory in drive Y to your Z drive.


1. Use Auto-filter and/or Advanced Filter commands to filter out the following information
(use all fields and separate copy to and criteria ranges):
·     All payments for Utilities.
·     All payment over $50 for food.
·     All payments for Education and Entertainment.
·     All payments that are Tax Exempt (Tax=Yes) or paid between 9/10/06 and 9/20/06.
·     All payments not including Kroger that are between $30 and $50.

2. For the first part of the worksheet, write the following Macros and assign a Button to each
 ·     A macro that prints “Monthly Check Register” table with 0.5 top and bottom margins and 
 fit to 1 page. Call this macro PRINT_REG.
 · A macro that sorts the same table by amount in descending order, prints the table and
 sorts it back to original order. Call this macro SORT_BY_AMOUNT.
 ·    A macro that changes value under “Category” in Criteria range to Food, filters the data 
 accordingly and prints the resulting table. Call this macro FOOD_FILTER.

3. Use List feature to add the following record to the check register:
    1021 9/30/06 Food Lion 32.97 Food No
    Copy Check Register table to two other worksheets in spreadsheet. Sort the first table
by Payee and calculate the total amount for each payee using Subtotal command. Sort the
second table by Category and calculate the average amount for each category again using

4. Use functions DSUM(), DAVERAGE and DCOUNT() to find the sum, average and count
for the Amount column based on one of the criteria given in part 1.

5. In part “Getting a Loan”, assume you are borrowing money to purchase a car. Based on
the information provided, calculate annual and monthly payment for this loan. Use functions
VLOOKUP() or CHOOSE() to see what percentage of the cost you can finance.

6. In the area provided, calculate the Rate and Number of Periods using PPMT(), IPMT(),

7. Under “Investing in Ventures”, you are given 3 scenarios for investment opportunities.
Which one would you select for investment? Use IRR() and NPV() to help you in your

                                                 Page 2

8. In the section “Putting Money….”, you realize you can put away $100 a month for the
next 10 years. How much money would you have after 10 years if you could invest it at an
annual rate of 10%.? How much money can you put down in one lump sum and reach the

9. Finally, your business has purchased a Van for $15,000. You figure out the van would
have a usable life of 5 years with a salvage value of $3,000. Use the three common
depreciation methods to see the amount you need to depreciate for each of the following 5
years. Use functions SLN(), DDB() and SYD() for Straight Line, Double Declining Balance

                                         Page 3

Description: Business Check Register document sample