Excel Lab - IT Cost/Benefit Analysis Using Excel

This lab will assist you in reviewing basic Excel concepts and specific Excel formulas and functions that will be used in your Assignment 1. Note: make sure that you label every spreadsheet variable so that it can be easily understood by a reader.

Lab 1:

1) Basic text manipulation
Center the following Page title and make the text color purple
"BUS311 Excel Lab" in size 14.

2)      PMT formula
Calculate the monthly payment for a car loan. The sale price is $25,000, the down payment is $5,000.    You elect a 5 year loan with an annual interest rate of 5%.

3)      Amortization table; Absolute vs. Relative reference

Calculate the monthly payment using the PMT calculation.
Create an amortization table: for each month of the loan, show the principal remaining, the amount of principal paid, and the amount of interest paid.
Compute the total interest paid on the loan.
Calculate the yearly interest paid on the loan. Place this value to the immediate right of the values for the 12th month in each year of the loan.

Lab 2:

4) IF logic function
Generate a letter grade for each of the students in a course using IF logic.

Student	Year	Score	Letter
1	3	91	A
2	4	95	A
3	2	92	A
4	4	59	F
5	4	65	D
6	4	72	C
7	3	83	B
8	2	78	C
9	4	51	F
10	3	69	D
11	3	81	B
12	4	94	A
13	3	99	A
14	4	78	C
15	3	12	F
16	4	66	D

Now add a column to the right of "Letter" that indicates if this student is eligible for an award. A student is eligible if they score an A and they are a Senior.

5)       Charts and Graphics
Generate a pie chart for the grade distribution in question 4.

6)      Breakeven Analysis
You are planning to invest US$100K in the first year and US$50K for each subsequent year for a new business. You expect this business to bring in a profit each year of US$70K.   In which year will you break even?   Generate a graph to display this information.

7)      Work between worksheets
Use a cell/variable in one worksheet to refer to a cell/variable in another worksheet.

IMPORTANT:

At the end of each Lab, you will need to submit your Excel file to Laulima to get participation points.