Question 1 (Databases)
Consider the database Executives.xls (available on Moodie) with data on executive compensation for a sample of companies. The information in the database constitutes a sample of 100 records from a publicly available survey of executive compensation. The worksheet “Glossary” provides definitions of the fields, in case the column titles are not selfexplanatory.
a) From the database, isolate the executives whose company’s Head Quarter is located in either New York State (NY) or California (CA) and whose salary is greater than or equal to $800,000 (note that the salaries in the spreadsheet are given in thousands of dollars). Save this information in a new table. [5]
b) For only the two states above (NY and CA), compile a tabulation displaying the average executive sales broken down by industry description and state. In the result table, highlight the average sales in the Publishing-Newspaper industry.
[1 0]
c) Compile another tabulation showing the executives average salaries and average sales broken down by industry description (consider now all the states).
Use this table to answer the following questions:
i. What is the average executive salary across all industry sectors?[2]
ii. In which industry sector, do executives have the maximum average salary
and sales? [3]
Question 2 (NPV)
Project A requires an initial outlay of £100,000, but will return £40,000 at the end of each of years 2, 3 and 4 whereas project B requires an initial outlay of £140,000 but will return £40,000 at the end of years 1, 2, 3 and 4.
a) Calculate the NPV of each project if the discount rate is 6% compounded annually. [1 0]
b) On the basis of your answer to part a), which project would you invest in? Why? [5]
c) Calculate the IRR for each of the two projects. [1 0] d) On the basis of the IRR which project would you prefer? Is it worth investing in either of the two projects? [5]
Bonus for good spreadsheet development [5]
Question 3 (Mortgage)
You are planning to buy a holiday villa in Spain which costs £300,000. You have a deposit of £30,000 and want to pay the rest through a mortgage. Your local bank offers you a 3.99% fixed rate for 5 years.
a) Compute the monthly repayments that you will have to make if you want to repay the mortgage in full in 25 years using the PMT function in Excel. [5]
b) Check your answer to part (a) by using Goal Seek. [1 0]
c) What would the outstanding balance be when you have to remortgage your loan after 5 years? [2]
d) If you think you can afford a monthly repayment of £2000, how long will it take you to repay the mortgage? [5]
e) Build a two-way data table to show how the monthly payment varies with changes in the down payment and in the term of the loan. Use values between 20,000 and 40,000 in increments of 5,000 for the down payment and values between 15 and 30 years in steps of 5 for the term of the loan. [8]
Bonus for good spreadsheet development [5]
