Business Modelling

Order Description

subject: Business Modelling
The work should be 3 excel sheets in 1 excel file.
This is a work that needs to be done in excel using excel tools as you can see from the instructions, it is based on 3 questions and you have to create 3 excel spreadsheets 1 for each question but the all work needs to be only 1 excel document with 3 spreadsheets ( when the teacher opens he will see question1 and all the answers at the 1 sheet then he clicks on sheet 2 and sees question 2 and all the answers and the same for question 3) . Remember to use excel tools/formulas to do the tables/answers so when he clicks at any number of the sheet the formula will appear at the top and he can clearly see how did u do the work and that you have used them. For example when he says to isolate the average sales you need to do that using the excel average function etc . Please read carefully the instructions.

This assignment contains three questions. Marks for each sub-question are shown in square brackets. Note that some points (bonus points) will be assigned based on the look of your spreadsheet (e.g., professional layout, formatting, colours) and the use of advanced Excel functionalities (data consolidation, scalability, range names, documentation). Please, only submit ONE Excel file including the answers to all the questions. Clearly name the individual sheets in your file (e.g., question 1 a, question 2b etc.). Explanations and comments you may want to include in your answers should also be part of the spreadsheet. No additional documents (e.g., Word documents) will be accepted.

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).
[1 0]

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 5years?[2]

d) If you think you can afford a monthly repayment of £2000, how long will it take you to repaythe 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 inincrements of 5,000 for the down payment and values between 15 and 30 years in steps of5 for the term of the loan.[8]

Bonus for good spreadsheet development [5]