Save this customized exam as LNExcelExam.xlsx,
where LN stands for your last name
You have only two hours to finish this exam.
Please upload the required files to the
Assignments page, ater two hours, even if you are
Complete all the sheets
Create a header with file name code at center
and your name at right
Add your name to cell B7 in this worksheet
Add your IUPUI username in cell B8
1. In Cell B8, Find the monthly payment for each month on a loan to be paid over 15 years.
2. IN Cell B18, find the future value of a monthly savings plan where you contribute $1,000 per month for 20 years and earn 3% interest.
Loan Amount $1,000,000.00
Interest Rate 7.25%
Future Value Analysis
Monthly Contribution $ 1,000
Interest Rate 3%
Number of years 20
Future Value of
Create a data table in the Payments for Varying Prices section of the spreadsheet based on Price in the Mortgage Analysis section
Mortgage Analysis Payments for Varying Prices
Monthly Total Total
Date November-07 Price Payment Interest Cost
Lot Number 13-West $588.80 ($64,486.95) $116,483.38
Price $75,000.00 $ 70,000.00
Down Payment $10,500.00 $ 75,000.00
Loan Amount $64,500.00 $ 80,000.00
Interest Rate 7.25% $ 85,000.00
Years 15 $ 90,000.00
Monthly Payment $588.80 $ 95,000.00
Total Interest ($64,486.95) $ 100,000.00
Total Cost $116,483.38 $ 105,000.00
1.Round: In cell G10, use the round function set to 0 decimals and apply it to the data in B10. Fill it down to G13.
2. Name the range C10:c13 LEM
3.In cell C14, use a function including the range name LEM to find the average of the range.
4.Set the range LEM as the print area
5.Link B7:B8 of the directions worksheet to this worksheet starting in A20
Average Life Life Difference Increase
Life expectancy at Round
State expectancy at expectancy at Male/Female life Total Life
birth Female off
birth birth Male expectancy Expectancy
Alabama 74.40649 71.3 77.5 -6.3 78.12681615
Alaska 76.63050 74.2 79.1 -4.9 80.46202458
Arizona 77.45201 74.7 80.2 -5.5 81.32460531
Arizona 74.98523 72.1 77.9 -5.8 78.73448832
1. Set up worksheet protection so that all formulas,functions, and labels are locked and so that all raw numbers can be changed
2. Apply the color blue to the raw numbers
3. Create a a new sheet in the workbook, named Chart. Format the tab to match the other sheets.
4. Create a clusted cone chart of the Average Life Expectancy at birth. Use a callout and brace like the textbook did in chapter 6
5. Move the chart to the new sheet.
Average Life Life
Life expectan expectan
Male/Female Total Life
State expectan cy at cy at
cy at birth birth
birth Male Female
Alabama 74.4 71.3 77.5 -6.3 78.12682
Alaska 76.6 74.2 79.1 -4.9 80.46202
Arizona 77.5 74.7 80.2 -5.5 81.32461
Arizona 75.0 72.1 77.9 -5.8 78.73449
Create a template and name it alphashell. Make sure you save it as a template separate from this file.
IN the template, creat a styule named alphafont. Use Comic Sans as the font, 18 as th epoint size, and pick a yellow or pink color for cell shading.
In the template, apply the style to Cell A10. Also type your name in Cell A10.