Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please help me fill out the following excel spread sheet and provide the answers for the following problems. Thanks in Advance For the problems 1-5,

Please help me fill out the following excel spread sheet and provide the answers for the following problems. Thanks in Advance

For the problems 1-5, begin by assuming you have: Unit Sales: 8275 Sales Growth Rate, years 1-4: 0.07 Inflation Rate: 0.02 Real Cost of Capital: 0.11 Tax Rate: 0.22 Sales Revenue / Unit: 10.79 Variable Cost / Unit: 7.23 Cash Fixed Costs: 8676 Investment: 50271. Problem 1: What are the projected unit sales in year 7 if sales are expected to increase by 12% in year 5, and then decrease by 21% in years 6 and 7?

"Problem 2: Assuming sales revenue per unit, variable cost per unit, and cash fixed costs all increase by the rate of inflation, what is your projected gross margin in year 7?" Problem 3: What is the net present value of this project? Problem 4: After you conduct your initial analysis, you discover that the firm has the option to abandon the project and sell its specialized equipment at the end of year 8 for $4189.25. What is the new NPV of the project?" "Problem 5: Your boss is apprehensive about your worksheet and your estimation of this project's NP. THIS LOOKS LIKE A JOB FOR SOLVER, you blurt out, only to find out that your boss bought a bootleg version of Excel that does not include the add-in. By changing the unit sales of your existing model as it stands from the prior problem (i.e., assuming the option to abandon from #4 is exercised, basically DO NOT REVERT YOUR ANSWER BACK BEFORE COMPLETING THE PROBLEM), find the number of unit sales needed in year 1 to give you an NPV of zero.

image text in transcribed \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F & G & H & 1 \\ \hline 1 & & Year 0 & Year 1 & Year 2 & Year 3 & Year 4 & Year 5 & Year 6 & Year 7 \\ \hline 2 & Unit Sales & & & & & & & & \\ \hline 3 & Inflation Rate & & & & & & & & \\ \hline 4 & Real Cost of Capital & & & & & & & & \\ \hline 5 & Tax Rate & & & & & & & & \\ \hline 6 & & & & & & & & & \\ \hline 7 & Dicount Rate(Cost of capital) & & & & & & & & \\ \hline 8 & Cummulative Discount Factor & & & & & & & & \\ \hline 9 & & & & & & & & & \\ \hline 10 & Sales Revenue/Unit & & & & & & & & \\ \hline 11 & Variable Cost/Unit & & & & & & & & \\ \hline 12 & Cash Fixed Costs & & & & & & & & \\ \hline 13 & & & & & & & & & \\ \hline 14 & Sales Revenue & & & & & & & & \\ \hline 15 & Variable Costs & & & & & & & & \\ \hline 16 & Gross Margin & & & & & & & & \\ \hline 17 & & & & & & & & & \\ \hline 18 & Cash Fixed Costs & & & & & & & & \\ \hline 19 & Depreciation & & & & & & & & \\ \hline 20 & Total Fixed Costs & & & & & & & & \\ \hline 21 & & & & & & & & & \\ \hline 22 & Operating Profit & & & & & & & & \\ \hline 23 & Taxes & & & & & & & & \\ \hline 24 & Net Profit & & & & & & & & \\ \hline 25 & & & & & & & & & \\ \hline 26 & Add back Depreciation & & & & & & & & \\ \hline 27 & Operating Cash Flow & & & & & & & & \\ \hline 28 & & & & & & & & & \\ \hline 29 & Investment & & & & & & & & \\ \hline 30 & Cash Flows & & & & & & & & \\ \hline 31 & Present Value of Each Cash Flow & & & & & & & & \\ \hline 32 & Net Present Value & & & & & & & & \\ \hline \end{tabular}

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The VAR Implementation Handbook

Authors: Greg Gregoriou

1st Edition

007161513X, 978-0071615136

More Books

Students also viewed these Finance questions

Question

4. Describe the role of narratives in constructing history.

Answered: 1 week ago

Question

1. Identify six different types of history.

Answered: 1 week ago