Seattle UniversityAlbers School of Business and Economics
Accounting DepartmentFaculty: Carlos A. De Mello-e-Souza

ACCT 580: Midterm Exam, Winter 2012

Read the honor code below, and then sign and print your name. Exams will not be graded unless the student has read the honor code and signed below agreeing with it.

Honor Code

By signing and printing my name below I affirm knowing that this exam is to be completed by myself and that assistance received from any other individual(s) constitutes cheating and as such will be subject to disciplinary action as deemed appropriate by the Office of the Dean of the Albers School of Business and Economics. I am aware of all the specific instructions that apply to this exam (provided on the following page) and agree to abide by them.

Student’s signature:______Date: ______

Printed name: ______

1

ACCT 531 – Midterm Exam (Fall 2009)

ACCT 580–Systems Modeling for Financial Decisions– Winter 2012

ACCT 580: Midterm Exam, Winter 2012

Instructions:

  • Calculators and computers are permitted. This is an open book and notes test. Access to the web is permitted but only when using the Excel Help tool. Any other usage of the internet will be considered a violation of the University’s code of academic honesty and will lead to a grade of zero in this exam.
  • You will work on this exam without giving or receiving assistance to/from classmates or any other individuals.
  • Audio/video players, cell phones and any other communication devices MUST be turned off during the exam and placed on the floor in a bag or briefcase. Any unauthorized usage of communication devices will be considered a violation of the University’s code of academic honesty and will lead to a grade of zero in this exam.
  • I will not answer any question during the exam, except in the case of typos and simple clarifying questions. If you believe that a question is ambiguous or that essential information is missing in a question, try to make the simplest possible assumption that allows you to answer that question.
  • The principles of good worksheet design that we discussed in class apply to all parts of this exam, unless explicitly indicated otherwise.
  • In order to assist me in giving credit to your answersstrive to build well documented models. Use clear titles and labels. Give logical names to your tabs. It is not my responsibility to uncover your reasoning, meaning or intent. It is in your interest to make those as clear as possible.
  • All answers must be given in a single Excel workbook. Your Excel workbook will have multiple sheets (tabs). Answers provided elsewhere will not be graded.
  • If you would like to protect cells in your workbook, do so without a password. I will not grade password protected sections or hidden cells in a workbook. Grouping rows and columns is OK.
  • You have 120 minutes to complete this test. I will deduct 20points from the grade of any student who begins to work on the exam before permission is given, or who continues to do so after time is up, regardless of what is being done. Exams uploaded 5 minutes or more after time is called will not be graded. If a delay is caused by the system, please let me know in class, when the delay happens.

Suggestions:

  • Remember that what is important is providing a response that addresses the question, not an excessively long or complicatedresponse.
  • Don’t spend too much time on any single item.
  • Be confident in your knowledge, skills and intuition.
  • If you have extra time at the end, go back and“stress” test your models.

Choose two questions to answer from the first group of questions below {Q1, Q2, Q3}, and a single question from the second group {Q4, Q5}:

Questions / Max. Points / Score / Knowledge Tested

Q1

/ 32 / Database, filering, statistics

Q2

/ 32 / Regression analysis

Q3

/ 32 / Data tables and graphs (sensitivity)
Q4 / 36 / Table lookup
Q5 / 36 / Optimization with Excel’s Solver
Grade:

Re-grading requests:

▪For each question you would like me to re-grade, please explain why you believe your answer addresses the question asked and deserves additional credit. Label each request with the corresponding question and item number. Your request can be typed or hand-written. If hand-written, be sure to write as clearly as possible. Continue on the back of this page if necessary. (It is not enough to write simply, for example, “Look at Question II.”)

▪Grade adjustments can be positive or negative.

▪The deadline for re-grading requests is one week after I return these tests.This deadline is the same even if you miss class when I return the tests.

Tab #1. Databases, Filtering and Statistical Functions (IBRD Data)[20 pts.]

Tab #1 in the exam file contains data from enterprise surveys conducted by the IBRD (World Bank).

Required:

(1.1)Using filters, create a modeling structure to computeaverages for the following variables:

V1: Sr. mgt. time spent in dealing with requirements of govt. regulation

V3: Days to obtain a construction related permit

V15: Percent of full-time female workers

Apply your solution tocountries in the LCR region (South America & Central America).

Place your solution for the LCR region in a new tab titled Tab#1.1.

(1.2)Using database statistical functions, create a modeling structure to computeaverages for the same variables that are listed in item 1.1 above.: V1, V3 and V15

Test your solution with countries in the LCR region, and then apply it to countries in the ECA region (Eastern Europe & Central Asia).

Place your solution for the ECA region in a new tab titled Tab#1.2.

Commentsand instructions to the user:

Tab #2. Regression Analysis (Graduation Rates)[20 pts.]

Tab #2 in the exam file contains information on graduation rates in colleges with less than 5000 students. (Source: “Statistics: The Exploration and Analysis of Data”, by R. Peck and J. Devore.)

Required:

(2.1)Using Excel’s data analysis tools, run a regression of Y (the six-year graduation rate) on the following variables: median SAT score (X1), student-related expense per full-time student (X2), and whether college has only female/male students or has both genders (X3).

Place yourappropriately formated solution in a new tab titled Tab#2.1.

(2.2)Using the LINEST function, repeat the regression in item 2.1 above

Place your solution in a new tab titled Tab#2.2. Remember to label your results so that a user who is unfamiliar with LINEST, but understands regression analysis, can understant the results.

Comments and instructions to the user:

Tab #3. Data Tables and Graphs. Sensitivity Analysis. (KMV Model)[20 pts.]

Sheet #3 in the exam file contains the logic of a very basic model of default risk based on Black and Schole’s option pricing model and on the analogy between equity and call options. Input cells, auxiliary calculation cells, and output cells (results) are clearly labeled on sheet #3. (Ref.: Moodys, KMV.)

Required:

(3.1)Use Excel’s one-variabledata table tool tocomputemarket value of equity and the market value of debt as the market value of assets goes from 0 to $35, at intervals of 50 cents.

Place your solution in a new tab titled Tab#3.1.

(3.2)Create a graph in good form to display market values of equity, debt and total assets (Y axis) as a function of the market value of total assets (X axis).

Place your solution in a new tab titled Tab#3.2.

(3.3)Use Excel’s two-variable data table tool to compute market value of equity as the market value of assets goes from 0 to $35, at intervals of $1, and the asset volatility parameter goes from 0.08 to 0.10 in intervals of 0.005.

Place your solution in a new tab titled Tab#3.3.

Comments and instructions to the user:

Tab #4. Application of Table Lookup Functions (Present Value Tables)[20 pts.]

Sheet #4 in the exam file contains present value tables for three common types of cash flow streams: a single value at time T (SV), an ordinary annuity from t=1 to T (OA), and an annuity in arrears from t=0 to T-1 (AA).

Required: Given the type of cash flow stream (SV, OA or AA), the number of periods T, and the opportunity cost of capital r, enter the appropriate present value in the cell labeled “PV”.

Place your solution in a new tab titled Tab#4.1.

Hint: Solve the problem in stages.

Comments and instructions to the user:

Tab #5. Optimization (Capital Budgeting)[20 pts.]

Sheet #5 is reserved for your solution to a problem in capital budgeting at the Rio-São Paulo Fast Train Company. The company has R$140 Million to invest, and four possible projects to choose from. No project can be repeated. The controller has asked your help in finding the combination of projects that maximizes present value for the company. The initial outlays required for each project and the corresponding present values are shown in the following table:

Source: Available upon request.

Project / Initial Outlay
(R$ MM) / Present Value
(R$MM)
A / 50 / 80
B / 70 / 110
C / 40 / 60
D / 30 / 40

Required:

(5.1)Create a framework in Excel to solve the problem. Remember to use principles of good spreadsheet design. Use Tab#5 for your framework.

(5.2)Find the solution that maximizes present value for the company.

Place your solution in the same tab (Tab#5).

(5.3)Show how the solution would change if the company had instead R$160, R$180 and R$200 Million to invest.

Place your solution in a new tab titled Tab#5.3.

(5.4)In the space below, explain how you might address item 5.3 above if the question had asked instead for sensitivity of the solution to budget limits from R$100 Million to R$300 Million in intervals of R$10 Million.

Comments and instructions to the user (use back of page if necessary):

1/9