Problem 1 - Repayment of a loan requires the periodic payment of interest and principal. You are interested in the amount of principal you pay in the 60th period of a loan. We can use the PPMT(rate, per, nper, pv, [fv], [type]) function for this purpose. Assume the following parameters for the loan and PPMT function:
rate = 4.0% (annual rate--don't forget to divide by 12 in PPMT function to match monthly input data- per, nper)
per = 60th month
nper = 360 month
pv = $150,000
Create a two-way data table for the calculation of the principal payment for the 60th period for a variety of annual interest rates (rate=4.0 to 6.5%, 0.5% increments as row value) and present values of the loan (pv=150,00 to 250,000, in 25,000 increments as column value).
Problem 2 - One of the best known calculations of Basal Metabolic Rate is the original Harris-Benedict equations for men and women1. According to Wikipedia..."Basal Metabolic Rate is the amount of energy expressed in calories that a person needs to keep the body functioning at rest. Some of those processes are breathing, blood circulation, controlling body temperature, cell growth, brain and nerve function, and contraction of muscles. Basal metabolic rate (BMR) affects the rate that a person burns calories and ultimately whether you maintain, gain, or lose weight."
Woman: BMR = 655 + ( 4.35 x weight in pounds ) + ( 4.7 x height in inches ) - ( 4.7 x age in years )
Man: BMR = 66 + ( 6.23 x weight in pounds ) + ( 12.7 x height in inches ) - ( 6.8 x age in year )
Create a calculator that permits data entry of gender (Woman or Man), weight (pounds), height (inches.), and age (years) in cell C1-C4, respectively. In cell C6 place the formula calculation for BMRs. In cell A6 place a logical formula that results in either "Ms. your BMR is =" if the calculation is for a woman or "Mr. your BMR is =" depending on the entry C1.
Suppose you also ask if an individual has the body fat of an athlete. In Cell E1 insert the text: "Athlete's Body Fat?--"Y" or "N". The answer is placed in G1--either "Y" or "N". If the answer is "Y", the formula in C6 should be multiplied by a factor of 1.25, otherwise it remains the same. The multiplication should occur due to a logic statement.
Restrict all entries to integer values: e.g. 180 lbs., 68 inches, and 68 years. Use these in your model.
Problem 3 - The Internal Rate of Return function (IRR(values, [guess]). ) is a calculation of the interest paid with an initial investment (-) and subsequent periodic returns (+). A relative has approached you with an opportunity to invest in his new invention. Let's call this a base investment and it has an IRR of 11% (see below). The relative offers 6 yearly payments (20000, 30000,....,15000) for an initial loan of 100000. Additionally, the relative also offers 3 other investment options. You decide to build a table that calculates the IRR for the other investments. These investments are all a variation of the base. For example, Investment 2 asks for 25% more from you initially (-125000) and increases the 6 payments by 25%; thus, Return yr1 is +25000 Return yr2 is 37500, etc.
Write three formulas with absolute and relative addressing that reference the cell values in green and can be easily copied to build the table. The partially completed table is shown below. Build the table with the northwest corner in Cell A1.
Problem 4 - In one of this module's videos we discussed two very convenient mechanisms for control tasks in Excel: Scroll Bar (Form Control) and Button (Form Control). These can be made available in the Quick Access Toolbar by using the steps--File/Options/Quick Access Toolbar/All Commands/Add. The also appear in the Developer ribbon under Controls/Insert/Forms Control.
Use the Buttons to do the following:
1) Assign a manually recorded macro to a Button called "Take Me Home," that when pressed activates.
2) Assign a manually recorded macro to a Button called "You Move Me" that when activated does the following: copies that data range below and moves it over by 3 columns and pastes it, sorts the entire data set by value (small to large), then finally, the cursor is returned to the Cell A1.
3) Create a scroll bar that generates values from 3% to 15% in exactly 0.5% increments--3%, 3.5%, etc.
Problem 5 - The list below is a list of 4 of my advisees at an exclusive private school in Williamsburg, VA--New Shaolin Academy. The Dean of Students sends me the list with their names and the demerits they have earned for the academic year. Demerits are issued to students for minor offenses, like--Not eating all their vegetables at the dining hall, sleeping late on weekends, etc. I secretly like students that "push the envelope" and occasionally earn demerits. I have decided to invite student advisees that earned more than the average demerits of my 4 advisees to dinner. Additionally, I will send all students an email to inform them of their demerits.
a) Create a 4th column in the data set that uses the concatenate(text1,text2,...) function to concatenate the data to form a proper email address. Next, apply conditional formatting to the demerits column that changes the color of the cell for those that are above the average of the 4 advisees. Perform your work on the list below and tell me who is invited to my home for dinner. Is there an advisee that is a "Goody two-shoes" (a virtuous little character).
Problem 6 - The equity and bond markets have become difficult for you to stomach, without a strong antacid. Your billionaire son-in-law, who is a titan in the world of real estate finance, suggests that you might want to invest in real estate, particularly single family and multi-family dwellings. This makes sense to you, and you decide to build a flexible model to answer financing questions.
The PMT(rate,nper,pv) function in Excel is your focus since you are keenly aware of cash flow issues resulting from bank loans. It contains 3 arguments: Rate, Number of Periods (Term), and Present Value (Principal). The model is shown with particular values below. (Hint: The Monthly $ Outflow should be about $939 for these values)
Create a Scroll bars that:
1) Controls the value of pv and locate it next to the pv value in the model. Make the lower limit $100,000 and the upper limit $250,000. Also, make the increment change $10,000 and the page change $50,000.
2) Controls the value of nper and locate it next to the nper value in the model. Make the lower limit 120 and the upper limit 480. Also, make the increment change 12 and the page change 60.
3) Controls the value of rate and locate it next to the rate value in the model. Make the lower limit 2.5% and the upper limit 8.0%. Also, make the increment change 0.5% and the page change 1.0%
Problem 7 - One evening your sister-in-law Yolanda, who you fear, tells you that she has developed a new investment strategy. She only holds gold bullion or Shares in an S&P500 index mutual fund, depending on the prices of these two investments. If the value of gold goes over $1261/oz. or the S&P index is below 1895 she sells gold and buys S&P Index; otherwise she holds her current position. I suggest, rather timidly, that with market volatility being high, she might also consider waiting for the conditions to remain the same for at least 3 trading days before executing the S&P500 purchase. She uncharacteristically agrees and even smiles at me.
Yolanda asks you to create a logical function using the IF(), AND(), and OR() functions in Excel to tell her explicitly to either "Hold Current Investment Position, Yoli!" or "Sell Gold and Buy S&P Index, Yoli!". Base the logic on the three elements discussed above.
Create your logical function in the model. (Hint: The OR will be used for the first 2 conditions--1261 and 1895-- and AND will be used for the results of the OR and 3 days.)
Problem 8 - It is often the case that a particular value is converted into another value for the purpose of analysis. This is usually done by applying some rule(s) to perform the transformation. For example, a score of 85 might translate into an exam letter grade of B+. We can use an extremely useful concept in Excel to make these transformations: VLOOKUP() and HLOOKUP().
Consider an exam for a group of students where the points achieved on the exam are translated in letter grades in the following way. Each exam is adjusted downward by the number of classes missed according to the table below:
Grade Scale Missed Classes ... Pts Off
94-100.....A 0-2 0
85-93.......A- 3-4 5
74-84.......B+ 5-above 10
62-73.......B
50-61.......C
36-49.......D
0-35.........F
Use VLOOKUP()s for the conversion of the student grade and the grade adjustment. Place the table for the lookups next to the student data and use the area in green for your calculations.
Problem 9 - The Data below is a sample of the number of page-views for the month of February by 30 online shoppers to a small online retailer of Spanish Foods.
a) Use the Stats/Basic Stats/Display Descriptive Statistics tool in Minitab to analyze the pageviews data. Copy using the snipping or similar tool to Excel.
b) Also, use the Stats/Basic Stats/Graphical Summary tool to analyze pageviews, and copy the graph to Excel.
Attachment:- Assignment File.rar