Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Business Plan Model Build a spreadsheet that provides a financial model for the Business Plan Spreadsheet Modeling Exercise. As always with financial models, years are

Business Plan Model Build a spreadsheet that provides a financial model for the Business Plan Spreadsheet Modeling Exercise. As always with financial models, years are across the columns, and financial information isimage text in transcribed down the rows. Your model should have good spreadsheet engineering. Compute an appropriate NPV using WACC as the discount rate.

Business Plan Spreadsheet Modeling Exercise Here is some information for a business plan for a new powdered-milk line in a factory. Expected values US$650,000 Remarks Capital expenditure to install the new line 77,000 tons Model component Investment in new manufacturing line Initial quantity to be sold Annual growth in quantity Initial sales price Will be influenced by the growth factor 3 percent US$5.50 per ton 63.4 percent of the net proceeds of sales (NPS) Variable expense percentage PEME (products' fixed marketing expenses) Mixed of fixed and variable expenses US$3,000 +2.5 percent of net proceeds of sales US$11,400 (annual) Fixed over 10 years Fixed factory overhead (FFO) Depreciation of the line Linear over 10 years US$65,000 (Capital expenditure divided by 10) General overhead US$35,000 Fixed over 10 years 35 percent of earnings Not under company control Tax rate before taxes Weighted average cost of capital (WACC) 9 percent Cost of capital, not under company control Time horizon 10 years We want to build a spreadsheet model to evaluate the cash flows produces by this new line. We want to compare the result with the initial investment. It is worth investing in the new line if the present value of the future cash flows is greater than the initial investment. (Alternatively, we want to invest in the line if the internal rate of return is greater than the WACC.) If the line is attractive, we want to perform sensitivity analysis to understand key risks - what changes to inputs would make the line unattractive? If the line is unattractive, we want to perform sensitivity analysis to understand what would need to be changed to make the line attractive. Business Plan Spreadsheet Modeling Exercise Here is some information for a business plan for a new powdered-milk line in a factory. Expected values US$650,000 Remarks Capital expenditure to install the new line 77,000 tons Model component Investment in new manufacturing line Initial quantity to be sold Annual growth in quantity Initial sales price Will be influenced by the growth factor 3 percent US$5.50 per ton 63.4 percent of the net proceeds of sales (NPS) Variable expense percentage PEME (products' fixed marketing expenses) Mixed of fixed and variable expenses US$3,000 +2.5 percent of net proceeds of sales US$11,400 (annual) Fixed over 10 years Fixed factory overhead (FFO) Depreciation of the line Linear over 10 years US$65,000 (Capital expenditure divided by 10) General overhead US$35,000 Fixed over 10 years 35 percent of earnings Not under company control Tax rate before taxes Weighted average cost of capital (WACC) 9 percent Cost of capital, not under company control Time horizon 10 years We want to build a spreadsheet model to evaluate the cash flows produces by this new line. We want to compare the result with the initial investment. It is worth investing in the new line if the present value of the future cash flows is greater than the initial investment. (Alternatively, we want to invest in the line if the internal rate of return is greater than the WACC.) If the line is attractive, we want to perform sensitivity analysis to understand key risks - what changes to inputs would make the line unattractive? If the line is unattractive, we want to perform sensitivity analysis to understand what would need to be changed to make the line attractive

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 Changing Face Of American BankingDeregulation, Reregulation, And The Global Financial System

Authors: Ranajoy Ray Chaudhuri

3rd Edition

1137365811, 9781137365811

More Books

Students also viewed these Accounting questions