Practice Midterm A PDF

Preview

Summary

Professor Thann midterm 1 practice and review...

Description

MGMT 472 Midterm

Topics 1. Basic Excel Skills a. Cell references b. Conditional Format c. Charts, databases, filter, advanced filter 2. Basic Spreadsheet models a. Parameters, decision, calculations b. Analysis: using Data table, Goal seek 3. Financial Calculations: PV, FV, NPV 4. Solver, solver table 5. Organizing, extracting data: Pivot table, Lookup, vlookup, index, match function 6. Analyzing data: Building histogram , MMM, standard deviation 7. Simulation Models: a. Generate random data from distributions i. Uniform ii. Normal iii. Discrete distributions using vlookup b. Simulation models using data table 8. Simulations and riskoptimizer with @risk a. How to define distributions b. How to capture correlations c. How to define a model and run risk oprimizer 9. Simquick a. How to build model

Practice problems

1. Suppose a company is about to start the following project, where all the dollar figures are in thousands of dollars. In year 0, the project requires a fixed cost of $11,000. The fixed costs is depreciated on the straight-line basis over five years, and there is a salvage value of $1,000 in year 5. The sales generated in years 1-5 are estimated to be 2,000 units, 3,000 units, 4,500 units, 3,500 units and 1,500 units. The costs of capital in years 1-5 are forecast to be 9.0%, 8.7%, 8.4%, 7.8% and 7.5%. The tax rate is forecast to be a constant 35.0%. Sales revenue per unit is forecast to be $8.80. Variable cost per unit is forecast to be $6.20. a. What is the project NPV? b. What is the minimum sale price such that NPV of the project is 0? c. Use data table to obtain relationship of NPV and Tax rate (15% to 40%)

2. Students’ score on a test is a normal distribution with mean 75 and standard deviation 25. Compute the probability that a random student obtain a score between 70 and 85 using excel functions.

3.

Pivot table, Histogram (SEE the excel file)

4. Solve the following problem

5. Your company is analyzing the market for a new product. Fixed cost can be anywhere between 500K and 700K. Variable cost follows a normal distribution with mean 0.25 and standard deviation 0.05. The selling price is also a normal distribution with mean 1 and standard deviation 0.3. The sale volumes depends on how many competitors are in the market, which is given in the table below. Sales volume: Normal distribution number of competitors

3 or higher

probability

mean

stdev

0

0.5 1,000,000

150,000

1

0.2 700,000

150,000

2

0.1 400,000

100,000

0.2

50,000

200,000

a. Compute the average profit, and standard deviation b. Compute the probability of losing money

c. Compute the probability of gaining more than 200K d. Draw a histogram of the simulation data....