Document Sample
Alpha Powered By Docstoc

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
not finished.

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.

            Mortgage Analysis
Loan Amount                  $1,000,000.00
Interest Rate                        7.25%
Years                                    15
Monthly Payment

       Future Value Analysis
Monthly Contribution   $              1,000
Interest Rate                            3%
Number of years                           20

Future Value of
Monthly Investment
Data Table

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
                                                    $ 110,000.00
                                                    $ 115,000.00
                                                    $ 120,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
                                              Difference    Increase
                    Life   expectan expectan
                                             Male/Female Total Life
      State       expectan   cy at    cy at
                                                  life      Expectan
                    cy at    birth    birth
                                             expectancy        cy
                    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.