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 is
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
