Microsoft Excel – Chapter 3

 

Chapter Overview:  Excel Chapter 3, “Spreadsheets in Decision Making: What If”, is an extremely important chapter in a complete understanding of a program such as Microsoft Excel.  In the following projects, topics covered will include the analysis of a car loan, the PMT function, the goal seek command, home mortgages, relative versus absolute addresses, the fill handle, pointing, functions, the grade book, statistical functions, arithmetic expressions versus functions, AutoFill, the IF function, VLOOKUP function, working with large spreadsheets, freezing panes, grouping and outlines, and the scenario manager.  

 

 

Back to Home Page


Projects Table of Contents

 

 

Individual Projects

 

Excel Chapter 3, Project 1 – Freeze Panes

Excel Chapter 3, Project 2 – Car Financing

Excel Chapter 3, Project 3 – Creating a New Spreadsheet
Excel Chapter 3, Project 4 – Fill Handle, Pointing, and Functions

Excel Chapter 3, Project 5 – The Car Dilemma

Excel Chapter 3, Project 6 – Student Grades

Excel Chapter 3, Project 7 – The Car Dilemma Part 2

Excel Chapter 3, Project 8 – Excel Statistics

Excel Chapter 3, Project 9 – Group and Outline

Excel Chapter 3, Project 10 – Using the Average Function and AutoFill

Excel Chapter 3, Project 11 – College Financing

Excel Chapter 3, Project 12 - Scenario Manager

Excel Chapter 3, Project 13 - Using the IF function

Excel Chapter 3, Project 14 - Using the VLOOKUP Function

Excel Chapter 3, Project 15 – Mortgage Financing

 

 

Group Projects

 

Excel Chapter 3, Group Project 16  – Car Dealerships

Excel Chapter 3, Group Project 17 – Freeze Panes, Scenario Manager, and Group and Outline

Excel Chapter 3, Group Project 18 – House Mortgage Rates

Excel Chapter 3, Group Project 19 - Amortization

Excel Chapter 3, Group Project 20 – Excel Functions

 

 

 

 

 

The Projects

 

 

Individual Projects

 

 

Excel Chapter 3, Project 1 – Freeze Panes

 

Learning Objectives:  In this project, you are expected to be able to freeze panes and format numbers (Ex:  45 to $45).

 

You are an accounting clerk for the Rwanda Computer Manufacture. You find that when you’re keying the figures into the excel file of the company’s income statement, the account titles don’t stay put. So you have to constantly scroll back and forth, as you are keying the sales and expenses of each month. Your objective is to learn how to freeze those account titles to make your job easier.

 

Step 1: Open the “Freezing Titles” file from the Files folder.

Step 2: Click the Window menu and select Freeze Panes.

Step 3: Freeze the cell B6. (Note: If you want to unfreeze the heading just click Window and select Unfreeze Panes.)

Step 4: From B6 to M6, key in 121000; 145000; 312000; 450200; 123600; 112300; 426000; 123400; 561200; 145600; 256000; and 213000.

Step 5: Format those numbers as currency.

Step 6: Save the file as “Freezing Panes Student Solution”

 

Solution Screen Shot:

 

Back to top

 

 

Excel Chapter 3, Project 2 – Car Financing

 

Learning Objectives:  In this project, you are expected to be able to be able to calculate the monthly payment for a car using certain variables and a PMT function, and then using an IF function, determine if you can afford it.

 

You wish to purchase a car.  You make $1,000 a month.  Assume you have no other expenses per month and will spend all of your monthly income on payments for this item. Comprise a spreadsheet displaying the price, interest rate, and monthly payment associated with each purchase.  

 

Step 1:  Go online and find a car you are interested in.  Note the price.

Step 2:  In Excel, put in an interest rate of 5% annually for 5 years

Step 3:  Use Excel IF and PMT commands to determine actual cost associated with your purchase.

Step 4:  Assume you have 8% of total price to make as a minimum down payment.

Step 5: Calculate the monthly payment you will have to pay in order to purchase this car.

Step 6:  You can only afford to pay $450 a month as the other $550 must go towards other things.  Using an IF function, display on the spreadsheet whether or not you will be able to afford this car

Step 7:  Save the file as “Car Financing Student Solution”

 

Solution Screen Shot:

:

         

Back to top

 

 

Excel Chapter 3, Project 3 – Creating a New Spreadsheet

 

Learning Objectives:  In this project, you are expected to be able to manipulate a spreadsheet and type different things into different cells.

 

Step 1:  In a new Excel spreadsheet, highlight all cells in rows 1 and 2 by clicking and dragging over the 1 and 2 on the upper left corner of the screen

Step 2:  Right click the mouse and click format cells and then click on the alignment tab and check merge cells, which is located towards the bottom of the window

Step 3:  Put in the title “Grade Book: Spring Semester” and then align it to the left

Step 4:  In cell A5, type “Name”,  in B5, “Student ID”, in C5 “Test 1”, in D5, “Test 2”, in E5, “Test 3”, in F5 “Test Average”, in G5, “Homework”, in H5, “Semester Average”, and in I5, “Grade”

Step 5:  Adjust column widths accordingly by highlighting the entire column, right clicking and then clicking on column width. You can also click and drag the edges of each column at the top of the screen to adjust the width.

Step 6:  Shade all of the column headings with the color of your choice. To do this, highlight the entire row, right click, and then click format cells. Next, click on the patterns tab, click on the color of choice, and click ‘ok’.

Step 7:  Save the file as “Creating a New Spreadsheet Student Solution”

 

Solution Screen Shot:

 

Back to top

 

 Excel Chapter 3, Project 4 – Fill Handle, Pointing, and Functions

 

Learning Objectives:  In this project, you are expected to be able to using Microsoft Excel’s fill handle, use the pointing technique, and use basic functions.

 

Step 1: Click on cell B1 and type the word “Cows”, in C1 type “Goats” and in D1 type “Total Animals”

Step 2: Click on cell A2 and type the word "Farmer 1"

            Step 3: Use the fill handle on cell A2 and use it to fill cell A3 and A4

Step 4: Select cell B2 and type in "1".  Select cell C2 and type in "3"

Step 5: Using the pointing technique, in D2, sum cells C2 and B2

Step 6:  Farmer 2 has 3 cows and 5 goats.  Farmer 3 has 6 cows and 4 goats.  Enter these values into the respective cells.

Step 7:  Once again use the fill handle on cell D2.  Drag it down to D4 to find out how many animals each farmer has total. 

            Step 8:  Now, in cell A5 type "total".  Using pointing again, in cell B5, sum B2, B3, and B4. Now you know how many cows and goats there are total. 

Step 9:  Finally, select cell B5.  Using the fill handle, fill C5 and D5

Step 10:  Save the file as “FHPF Student Solution”

 

Solution Screen Shot:

 

Back to top

 

 

Excel Chapter 3, Project 5 – The Car Dilemma

 

Learning Objectives:  In this project, you are expected to be able to calculate car payments for a group of people using different variables for interest rate, length of loan, and loan amount. 

 

Gahiji, Muteteli, and Runihura need a vehicle.  They go to the bank and get a loan for $10,000 with an interest rate of 10% over a three-year period. 

 

Step 1: Enter the following categories on your spreadsheet:  Amount of loan, Interest rate, Number of periods, Total vehicle payments, Individual payments. 

Step 2:  Expand column A to fit the previous categories

Step 3:  Fill in the given information next to the categories 

Step 4: use the PMT function to find out the total vehicle payment.

Step 5: Using you Total vehicle payment results calculate how much it will be for each person by dividing total vehicle payments by 3. 

Step 6: Gahiji, Muteteli, and Runihura decide that they would rather have a $20,000 car, so they go and get another loan from the bank.   Together, the new total loan is $20,000 with an interest rate of 8.5% over 4 years.  In column C, enter your new values and then calculate the same thing from as in step 5. 

Step 7:  Save the file as “The Car Dilemma Student Solution”

 

Solution Screen Shot:

 

Back to top

 

 

Excel Chapter 3, Project 6 – Student Grades

 

Learning Objectives:  In this project, you are expected to make a grade book capable of calculating class averages and then altering it in order to do things such an implement a curve and find the new averages.  You also are expected to be able to use the average function. 

You are the teacher of a class of 20 students.  Each student would like to know if it is possible for him or her to get a 90% or above, based on his or her current grade.  If it is possible to achieve, what minimum score would need to be received in order to have at least a 90% in the class.  In this class, there are 600 total points.  All grades except for the final exam, which is worth 200, have already been recorded in the grade book. Using Excel, make a grade book based on the following information that would answer each student’s question. 

 

Step 1:  There are 20 students in the class that much be listed in the grade book.  The current percentages of the 20 students are:  56, 59, 61, 62, 63, 67, 70, 73, 74, 79, 80, 82, 84, 87, 88, 91, 93, 95, 97

Step 2:  Find out how many students are able to get a 90% in the class

Step 3:  Calculate the class average.  

Step 4:  Noticing a rather low class average, the teacher decides right before the final exam that there needs to be a 5% curve.   This curve is implemented before the final exam.  Starting in a new column, add 5% to each student’s grade

Step 5: Calculate the new point totals for each student

Step 6:  Now, again calculate how many students are now able to get a 90% in the class

Step 7:  Once again, calculate the class average. 

Step 8:  Save the file as “Student Grades Student Solution”

 

Solution Screen Shot:

 

Back to top

 

 

Excel Chapter 3, Project 7 – The Car Dilemma Part 2

 

Learning Objectives:  In this project, you are expected to be able to calculate car payments for a group of people using different variables for interest rate, length of loan, and loan amount.  Additionally, in this project you will have income barriers, as the characters involved can only afford a specific amount of money per month, making you use the goal seek command.

 

Gahiji and Muteteli decide that they want to share a car between the two of them. They go to the bank and get a $15,000 loan over five years with a 7% interest rate. 

 

Step 1: One your spreadsheet, enter the given information.    

Step 2: Use the PMT function to find out how much the monthly payments will be.

Step 3: Using you Monthly Payment results, calculate how much it will be for each person by dividing monthly payments by 2. 

               Step 4: Gahiji and Muteteli can only afford $121.00 a month each. Using the goal seek command, find a payment method for them in which they pay no more than $242 a month.

               Step 5: Now, in the column next to your first column, copy all values over, but change interest rate to 9%.  

         Step 6:  Finally, make one last column with the same values, but make the interest rate 5%. 

Step 7:  Save the file as “The Car Dilemma Part 2 Student Solution”

 

Solution Screen Shot:

     

Back to top

 

 

Excel Chapter 3, Project 8 – Excel Statistics

 

Learning Objectives:  In this project, you are expected to know the basic statistical functions in Excel, including the SUM, Average, Max, Min, COUNTA, and COUNT functions. 

 

Step 1:  In A2-A6, list names.  You can come up with any name you choose.  In B1 type “Brothers” and in C1 type “Sisters”

Step 2:  Give each person as many brothers and sisters as you choose. 

Step 3:  Now, in A7 and D1 type “Total”. 

Step 4:  Use the SUM function to find out how many brother and sisters there are total in cell B7 and C7.  In cells D2-D6, find out how many total brothers and sisters each person has.  In D7, find out how many brothers and sisters there are total.

Step 5:  In A8 and E1 type “Average”.  Now you need to find out the average of each category. 

Step 6:  In A9 and F1 type “Max”.  Use the MAX function to calculate the maximum of each category. 

Step 7:  In A10 and G1 type “Min”.  Use the MIN function to calculate the minimum of each category. 

Step 8:  In A11 use the COUNTA function to find out how many cells are filled with numbers and words.  In B11 use the COUNT function to find out how many cells are filled with numbers.  In these functions, use cell A1 – G10. 

Step 9:  Save the file as “Excel Statistics Student Solution”

 

Solution Screen Shot:

 

 

Back to Top

 

Excel Chapter 3, Project 9 – Group and Outline

 

Learning Objectives:  In this project, you are expected to use the Group and Outline function in order to arrange different aspects of a spreadsheet by country. 

 

You are the regional sale manager of the Super Mercedes Dealership. Your job is to access the sales of cars in Rwanda, Tanzania, Kenya, Uganda, and Burundi. Your objective is to group the company’s employees and calculate the total sales for each country in addition to seeing how employees in each country perform.

 

Step 1: Open the “Exc3Proj9Data

Step 2: Click the Sort Command and sort by country.

Step 3: Highlight rows 7,11,16,20,23 at the same time and click Insert Rows for the Insert menu.

Step 4: Label “Total: Kenya” in C7; “Total: Uganda” in C11; “Total: Rwanda” in C16; “Total: Tanzania” in C20; “Total:  Burundi”.

Step 5: Format those labels in bold and red letters and center them.

Step 6: Add up the all the sales of each employee in each state separately, using the Sum function.

Step 7: Highlight row 5 to 7 for employees in Kenya and click the Data menu.

Step 8: Click the Group and Outline Command.

Step 9: Group the rest of the countries in the same way.

Step10: Once you have finished grouping the rest of the countries, you see those minus signs on the left hand side. Click those signs.

Step11: Save the file as “Group and Outline Student Solution”      

 

Solution Screen Shot:

 

Back to top

 

 

Excel Chapter 3, Project 10 – Using the Average Function and AutoFill

 

Learning Objectives:  In this project, you are expected to be able to use the average function and AutoFill to calculate grades in a grade book.  Additionally, you will need to be able to manipulate functions and be able to format numbers. 

 

 

Step 1:  Open the file “Exc3Proj10Data

Step 2: Click on cell F6, which should be the first cell under the Test Average column corresponding to the student Dukuzumuremyi and calculate his test average (make sure you divide your equation by 100).

Step 3: Also find the test average for Mukamutara and Gahiji using the AVERAGE function (make sure you divide your equation by 100).

Step 4:  Highlight the three cells where you put the averages in (Cells F6-F8) and then click on the fill-handle, located at the bottom right hand corner of the active cell box.  Next, drag the box down to the cell corresponding to the last person’s test average (cell F17)

Step 5:  Format the averages you just came up with.  Change them from decimals to percentages and set decimal places to 1. 

Step 6:  Save the file as “Using the Average Function and AutoFill Student Solution”

 

Solution Screen Shot:

 

 

Back to Top

 

 

Excel Chapter 3, Project 11 – College Financing  

 

Learning Objectives:  In this project, you are expected to be able to make a complex spreadsheet and calculate a payment plan for college tuition.  You must use many different variables, including amount of money you already have, interest, and loan amount to calculate a relatively complex solution. 

 

Your child is going to college next year.  You wish to make a 4-year schedule associated with payments to the school.  You currently have $20,500 saved for tuition and will pay $3,500 twice a year as a college semester cost.  Additionally, the money you have left in the bank makes 2.5% semi-annual (twice a year) interest.  Whatever amount not paid by cash is to be funded by a loan to be paid at the end of four years with cumulative interest.   

 

Step 1:  Using the above information, make a spreadsheet on Excel to figure out how long you will be able to pay tuition before you run out of money.  Note:  While the spreadsheet can be made in many different ways, the answer should be the same.

Step 2:  You now know that you will only be able to support your child through their third year of college.  You decide it is necessary to take out student loans in order to pay for the final year of college. You take out $7000 in student loans and place it in your bank account.  Make sure you calculate Year 4 without the loan included before you go on.

Step 3:  These student loans have a 3% semi-annual interest rate

Step 4: Calculate how much money you will have remaining at the end of the last year of college. 

Step 5:  Using the information you have about financial condition at the end of the third school year, make a new spreadsheet below the first one with the new calculations (You only have to include Year 4 in this spreadsheet)

Step 6:  Save the file as “College Financing Student Solution”

 

Solution Screen Shot:

 

Back to top

 

Excel Chapter 3, Project 12 – Scenario Manager

 

Learning Objectives:  In this project, you are expected to be able to make multiple scenarios in Excel in order to decide whether or not a homeowner should refinance his house.  

 

A homeowner is thinking about refinancing his 15-year mortgage loan at 7.35% interest rate. He is trying to decide whether or not to refinance his mortgage in a new 15-year loan at 6.75% or in a 30-year loan at 4.35%. Your tasks are to create two scenarios based on the information given and provide a scenario summary so homeowner can make his decision.   

 

Step 1: In cell A2, type “To Refinance or Not to Refinance?”

Step 2: Format the title in bold, Arial, 14-size font and merge and center it.

Step 3: Type “Principle Value” in cell A4, “Interest Rate in A5, “Term (years) in A6, “Monthly Payment” in A7, and “Total Interest” in A8.

Step 4: In cell C4 fill in “128900” and format it as currency; in C5 and C6 key in 7.35% and 15.

Step 5: In C7 use the PMT function to calculate the monthly payment.

Step 6: In C8 type in the formula box (12*C6*C7)-C4.

Step 7: Select the Scenario in the Tools menu.

Step 8: Click Add and write “30-year loan at 4.35%” in the scenario name and drag C5 and C6 in changing cells for the first scenario.

Step 9: Change the scenario values to information given on the new 30-year loan.

Step10: Use those same steps from step 9 to step 10 for the second scenario, but this time, type “15-year loan at 6.75%” in the scenario name. And type “current loan at 7.35%”. Remember to change the scenario values to both scenarios. 

Step11: Click Show in the Scenario Manager.

Step12: Save the file as “Scenario Manager Student Solution”    

 

Solution Screen Shot:

 

Back to top

 

Excel Chapter 3, Project 13 – Using the IF Function

 

Learning Objectives:  In this project, you are expected to use the IF function to alter the final grade of students depending on certain things.  Additionally, you are expected to use the AutoFill technique. 

 

Step 1:  Open the file “Exc3Proj13Data

Step 2:  For the homework grade, the teacher gives a student an ‘OK’ if they have done most of their homework. If the student did not, he/she receives a ‘POOR’.  Mukamutara, Muteteli, Uwimana, and Nyillingondo were the only ones to receive a ‘POOR’ in homework. Thus, type in POOR in each corresponding cell under homework (G7, G11, G16, G17) and for the rest of the students, type “OK” in their corresponding cells.

Step 3:  The teacher has decided to add a 4 percentage point bonus for those who received an “OK” in homework.  In cell D18, type “HW BONUS” and in cell D19, type the number “4”.

Step 4:  Using the IF function, calculate their semester averages making sure to include the fact that an “OK” in homework is worth 4 bonus percentage points and a “POOR” is worth none.

Step 5:  First calculate Dukuzumuremyi’s semester average and then calculate Mukamutara’s.  After this, use the AutoFill technique or the fill handle to fill in the rest of the semester averages. 

Step 6:  Format this number to a percentage with only 1 decimal place

Step 7:  Save the file as “Using the IF Function Student Solution”

 

Solution Screen Shot:

 

 

Back to top

 

 

Excel Chapter 3, Project 14 – Using the VLOOKUP Function

 

Learning Objectives:  In this project, you are expected to use the VLOOKUP function in order to have the final grade of students placed into a grade book without physically typing it for each student. 

 

Step 1:  Open the file “Exc3Proj14Data

Step 2:  In order for the VLOOKUP function to work properly, you must first create a table as a reference point.  In cell F18, type the title for the table, “Grading Criteria”. 

Step 3:  Directly under put the following numbers in order, each number directly under the previous one: 0, 60, 70, 80, 90.

Step 4:  To the right of each of the numbers, put the corresponding letter grade. For 0, it should be F, D for 60, C for 70, B for 80, and A for 90.

Step 5:  Use the VLOOKUP function to find the grades of Dukuzumuremyi and Mukamutara.  Hint : To make a cell value not change when using AutoFill or the fill handle, type $ signs around it.  For instance, if you want to refer to cell F19 each time a certain formula is calculated, in the formula, you would type $F$19 instead of just F19.  

Step 6:  Find the remaining grades by either typing in the formula for each one or using the AutoFill or fill handle techniques

Step 7:  Save the file as “Using the VLOOKUP Function Student Solution”

 

Solution Screen Shot:

 

Back to top

 

 

Excel Chapter 3, Project 15 – Mortgage Financing

 

Learning Objectives:  In this project, you are expected to make a spreadsheet that can calculate mortgages based on variables (amount of money borrowed and interest) that can change.  Also, you must be able to come up with financial functions to calculate monthly payments. 

 

You are interested in borrowing $50,000 from the bank to help buy a new house.  This project will give you hands on experience with function in Excel as well as go over the fill handle and pointing once again.

 

Step 1: Open the file Exc3Proj15Data

Step 2: In C1, type $50,000, the amount of money you plan to borrow. 

            Step 3: You have been offered an interest rate of 3.5% on this bank loan.  Type this amount into cell C2.

Step 4: Make cell A6 equal to cell C2, but don’t simply type 3.5% into A6, use a function

            Step 5: Make A7 equal to A6 plus .005. 

            Step 6: Use the fill handle on cell A7 to fill cells A8 through A12

            Step 7: Select cell B6.  Go to the Paste Function dialog bow, click “financial” and then click “PMT”

Step 8: Next to "rate" type A6/12.  Next to "Nper" type 10*12.  Next to "Pv" type -$C$1

Step 9: Use the fill handle on B6 to fill in B7 through B12

            Step 10: Now, click on cell C6.  Go back to step 5 and repeat making the term 20 years this time around rather than 10

Step 11:  Use the fill handle on C6 to fill in C7 through C12

            Step 12:  Now, click on cell D6.  Using the pointing technique, find the difference between cell C6 and cell B6

Step 13:  Use the fill handle once again on D6 to fill in D7 through D12

Step 14:  Lets assume that your starting interest rate was not 3.50%, but it was 4.00%.  Change this on your spreadsheet

Step 15:  Now, lets say you want to finance your house over 30 years.  In cell E5, type "30 years" and in cell E6, put in the formula for 30 years by going back to step 5 and editing when necessary. 

            Step 16:  Erase cell C1 and type $60,000 into cell D1.  Edit your functions from earlier in order for your spreadsheet to still be accurate.  Note: You will have to use the fill handle technique once again in columns B, C, and E to reflect the changes to the function

            Step 17:  Save the file as “Mortgage Financing Student Solution”

 

            Solution Screen Shot:

 

Back to top

 

 

Group Projects

 

 Excel Chapter 3, Group Project 16 – Car Dealerships

 

Learning Objectives:  In this project, you are expected to use the given information to calculate dealer profits and car salesman commission.  This is an extremely complex problem that will require the hard work of all group members to be successfully completed.

 

Car dealerships buy their cars from the factory for $15,000 each and have an asking price for each of factory cost + 25%.  People negotiate prices with salesman, and generally can talk the dealer’s price down.  Car salesman make commission based on how much the cars they sell end up going for.  If the car sells for 95% of the dealer’s asking price or more, there is a 5% commission (Calculated as (Price Sold For - $15000)*.05).  If the car sells for 90%-94% of asking price, there is a commission of 3%.  If the car sells for between 80%-89% of asking price, there is a 1% commission.  A dealer will not sell for less than 80% of asking price. 

 

Objective 1:  In a group, make a spreadsheet to keep track of dealer profits with the following situation:

--- Assume there are two salesmen.  The first salesman sells a car for 98%, 91%, and 85%.

--- The second salesman sells a car for 92%, 90%, and 81%. 

Objective 2: Calculate total dealer profits  (selling price – salesman’s commission – cost)

Objective 3:  The car company now decides that not enough cars are selling so they give a $500 manufacturer’s rebate to anyone that buys a car. Show how this rebate effects commissions and total dealer profit.  Remember, the rebates are paid back to the dealer from the manufacturer.  Buyers are already aware of the rebate and have already taken it out of their offer price.  This means that a buyer who ends up spending $18000 on a car was originally willing to pay $18500.  

Objective 4:  Calculate total dealer profits by making a new spreadsheet below the old one

Final step:  Save the file as “Car Dealerships Student Solution”

 

Solution Screen Shot:

 

Back to top

 

 

Excel Chapter 3, Group Project 17 – Freeze Panes, Scenario Manager, and Group and Outline

 

Learning Objectives:  In this project, you are expected to use your knowledge of freezing panes, scenario manager, and the group and outline commands to complete objectives assigned to your group.  There are multiple scenarios to complete that require your entire group to work hard to successfully arrive at the answer. 

 

Now that you have a grip on different uses of Freeze Panes, Scenario Manager, and Group and Outline command. You and two others will team up to create an Excel project using these newly required skills, along with others from previous chapters. The objective of your team is a create grade book using Excel.

 

Objective 1: In cell A1, type the heading “Grade Book” and merger and format the label in bold and 18-size font. From cell A4 to P4, type in the following headings:

Lab Exercise 1

Lab Exercise 2

Lab Exercise 3

Lab Exercise 4

Total Lab Exercise Points

Quiz 1

Quiz 2

Quiz 3

Total Quiz Points

Exam 1

Exam 2

Exam 3

Total Exam Points

Total Final Points

Performance Rating

 

After typing all these headings, create a list of twenty students’ names. These twenty names can be of your classmates or can be fictional.

 

Objective 2: Once your team has created a list of twenty students’ names and labeled the headings, your team will make up numbers for lab exercise 1 to 4, quiz 1 to 3, and Exam 1 to 3 for each student. There are some information about the different headings: (1) each lab exercise is worth 50 points (2) each quiz is worth 60 points (3) each exam is worth 100 points. Try to create terrible numbers for quizzes In cell B27, type in “Percentage of the final points:”

From cell B29 to B31, type in:

 

Total Lab Exercise Points:

Total Quiz Points:

Total Exam Points:

 

From cell B33 to B35, type in:

                    Total Lab Exercise Points Possible:

           Total Quiz Points Possible:

      Total Exam Points Possible:

 

From cell F29, type in “20%”, “20%”, and “60%”. From cell F33 to cell F35, type in “180”, “180”, and “300”.

Objective 3: Calculate each student’s Total Lab Exercise Point by using the SUM Function/$F$33, for each student’s Total Quiz Points use SUM function/$F$34, and for each student’s Total Exam Points use SUM function/$F$35. And to calculate the each student’s Total Final Points use the equation: ((SUM Function/$F$33)*$F$29)+((SUM function/$F$34*$F$30)+((SUM function/$F$35) *$F$31), and format as percentage and one decimal place. Once your team has all the calculations done, use VLOOKUP function to change the numbers in Total Final Points into the Performance Rating. Here are the guidelines for creating the rating: 90% is rated “Excellent”, 80% is rated “Very Good”, 70% is rated “Satisfactory”, and 0 is rated “Unsatisfactory”.

Objective 4: Create two scenarios using the Scenario Manger function, one named “no change” and another “change”. For the Changing cells title of “no change” scenario, drag F29 to F31 and click Ok. For the “change” scenario, change the Scenario Values to 25%, 15%, and 60%. Apply the “change” scenario.

Objective 5: After applying the “change” scenario, sort the Performance Rating and group the students by their performance rating. Use the COUNTA function to count the number of each rating in each section.

Final step:  Save the file as “FPSMGO Student Solution

 

Solution Screen Shot:

 

Back to top

 

 

Excel Chapter 3, Group Project 18 – House Mortgage Rates

 

Learning Objectives:  In this project, you are expected to find a house you are interested in and find out the best ways to finance it using PMT functions.  In your group, you will discover multiple loan and interest amounts that can be financed based on an original loan amount and interest rate. 

 

In a group, go onto the internet and go to a site such as realestate.com.  On this website, find any house that you are interested in.  The price of this house doesn’t matter.  You will be purchasing this house and are interested on financing it.  You must loan the entire amount of the house from the bank.  You are not sure exactly how much you need, so you will calculate using increments of $10,000.  You will be taking this money out on a 20-year loan with a starting interest rate of 5% and an increment of 1%. 

 

Step 1:  On a spreadsheet, show the given information.  Show the categories in column A (Initial loan, Loan increment, Starting interest, Interest Increment, and Term in Years).  In column B, show the amounts. 

Step 2:  Make it so your starting interest is shown in A8 and it increases by increments of 1% all the way down to A15.  In B7, put your loan amount and have it increase by the loan increment up until F7. 

Step 3:  Using a PMT function, calculate the monthly payment at each interest rate for each loan amount.  Enter a separate PMT function in each column, B, C, D, E, and F using the loan increment amount at the top of each column. Do not use the fill handle to complete each row, but you will be able to use it to complete each column. 

Step 4:  In this problem, your spreadsheet should be completely flexible.  This means that you should only have to edit B1, B2, B3, B4, and B5, and be able to change the results of the entire sheet.

Step 5:  Change your initial loan to $150,000 and your Starting interest rate to 3%. 

Step 6:  Change your Loan increment to $7,500

Final step:  Save the file as “House Mortgage Rates Student Solution”

 

Solution Screen Shot:

 

Back to top

 

 

Excel Chapter 3, Group Project 19 – Amortization

 

Learning Objectives:  In this project, you are expected to work in your group in order to create an amortization schedule, which will show you the payoff schedule of a loan over time.  You will use the IPMT and PPMT functions. While this only has one step, it is an extremely difficult problem and will require a very strong group effort. 

 

You and your group are interested in purchasing real estate.  Unfortunately, you do not have the funds to make any purchases in cash and must take out a mortgage.  The total cost of the real estate amounts to $200,000.  The real estate includes 2 condominium units worth $125,000 each.  The Real estate can be sold as a whole at its discounted price ($200,000 total) or as individual units ($125,000 each).  The more you borrow, the longer you have to pay it back.  In other words, you have 3 years to pay back a loan of $200,000 or 5 years to pay back a loan of $250,000.   The interest rate is a steady 5%.  The total value of the property also increases by 3% each year.  Assume that at the end of the mortgage, the property will be sold at its total value. 

 

Objective:  Construct an amortization table and determine which mortgage should be taken out in order to maximize profit when property is sold.  Remember, if you need help with this project, you can go to the help bar (Hit Alt-H) and look up PPMT and IPMT functions.

Final step:  Save the file as “Amortization Student Solution”

 

Solution Screen Shot:

 

 

Back to top

 

 

Excel Chapter 3, Group Project 20 – Excel Functions

 

Learning Objectives:  In this project, you are expected to use all of your knowledge of functions in Excel in order to complete a project that requires the use of many separate functions. 

 

Objective 1:  Open the file “Exc3Proj20Data

Step 1:  In cell A12, use the SUM function to sum all cells from A1 to A11

Step 2:  In cell B12, use the AVERAGE function to find the average of the values from cells B1 to B11

Step 3:  In cell C12, use the MAX function to find the largest value from C1 to C11

Step 4:  In cell D12, use the MIN function to find the smallest value from D1 to D11

Step 5:  In cell E12, use the COUNT function to determine how many cells have a numerical value between E1 and E11

Step 6:  Make all numbers in all cells between A12 and E12 centered and bold

Objective 2: 

Step 1:  Go to cell B15.  Create an IF function that will say “Good” if the numerical value in the corresponding cell is greater than 50 and will say “Bad” if it is less than 50. 

Step 2:  Using the fill handle, fill all cells between B15 and B25 with the IF function. 

Step 3:  In cell D30, use the COUNTA function to determine how many cells in the entire spreadsheet have something in them (Between cells A1 and E25)

Step 4:  Make all text in all cells between B15 and B25 centered and bold

Final step:  Save your solution as “Excel Functions Student Solution”

 

Solution Screen Shot:

 

Back to top

 

 

 

 

 

Back to Home Page