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]
Our Service Charter
-
Excellent Quality / 100% Plagiarism-Free
We employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers. -
Free Revisions
We keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed. -
Confidentiality / 100% No Disclosure
We make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service. -
Money Back Guarantee
If the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit. -
24/7 Customer Support
We have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.