CS 107 Computers and Their Applications

Fall 2006

Buckman Hall 120

Team Assignment 5 - Calculating Business Expenses & Taking a Loan

UNH Home Page Contact Instructor

Course Home Page

Syllabus

Course Schedule

Team Final Project

Part One

It's time to expand your business. And to expand your business, your team needs money. So, your team is going to have to take out a loan from the bank. Since you have to watch your budget, your partners and you will have to figure out what the loan will cost your business. Using the information below, figure out what your monthly payment for your loan will be. Then set up an amortization table which will show how much of each payment goes toward your principal payment and how much will go toward the interest. Finally, your team wants to know what the total interest costs for this loan will be. So, you'll want to total all of the monthly interest payments to see what the cost of your money will be.

Loan Amount: $ 10,000
Annual Interest Rate: 8%
Year to Payback: 3
Number of Payments per Year: 12

For this portion of the assignment you will use the PMT function to find the dollar amount of the monthly payment. For the amorization table, ise the PPMT function to find the dollar amount of principal potion of your monthly payment. Use IPMT fundtion to find the interest portion of the payment. Remeber to use a "$" in front of any cell address which you do not wish to change if you are copying the formulas.

Part Two

Your business is now going to have to start keeping track of your monthly expenses. One expense is the depreciation of your equipment which includes the new computer and software you have just purchased. For simplicity, your business is going to use straight line depreciation. You calculate that your computer will have a useful life three years. This means that you will divide the total cost of your computer, software, printers, and any other equipment by the total number of months of useful life. This will be your monthly depreciation cost.

Part Three

Now that you know what your loan payments and depreciations are going to be, it's time to do your monthly budget. Besides your loan payment and depreciation, you know that your business is going to have at least four other expenses such as your salaries, rent, office supplies, travel expenses, and so on. Of course, these other expenses will vary depending upon what your business is. When you determine what your other costs are going to be, you are going to show what your total monthly costs are for a six month period.

You also have money coming in during this six month period. The way in which you figure out how much money is coming is will depend upon what type of business you are running. Your income may be a fixed dollar amount per client, a total number of items sold at a specific price, etc. For the six month period that you are listing your expenses, you are also going to list your income, how ever you calculate it. You then want to show whether you made money each month, or lost money each month. You may have some months where you make a profit, some months where you have a loss, and some months where you just break even.

Extra Credit

For extra credit, to help those who may not have had a stellar performance on the first test, you and your partners decide to use Excel to perform some "what-if" analysis. You and your partners should look for two factors that you could vary. For example, if you are running a video store, you can see how your net profit will change if you vary the price of the video rentals. You can see how your profits will change if you vary both the price of a rental and the total number of rentals made each week. The two items you vary will depend upon what type of business you are running.

First, you must figure out the formula for you current profit. Next, use data analysis of a single variable to see how varying one factor will effect your profit. Finally, using data analysis of two variables, see how varying both factors will effect your profit.

Due Date: October 20, 2006

Assignment Requirements:

Three (four if your team is doing the extra credit portion of the assignment) excel worksheets, one for each part of the assignment, each having the following specifications:

Part One

Part Two

Part Three

Extra Credit


Last Updated: 10/12/06