Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Exercise 1 -- Decision Analysis: SVM Three Initiative -- Second Graded Model

Reminder:  This is a graded exercise and your submission must be a product of only your own work.  You cannot seek assistance with this exercise from anyone.  If necessary, you may ask clarifying questions via posts to “Week 5 Discussions With Professor” on Canvas.  You may not post anything regarding this exercise to any “Discussions With Fellow Students” on Canvas.

The model must be posted to Canvas by 11:00 PM on Friday of Week 3.

Background For This Exercise

SVM has a product named Three.  As of the end of 2022, Three had a remaining planned life of three years; i.e. the SVM plan as of the end of 2022 includes the retirement the SVM product in question by the end of 2025.  Nonetheless, at the very end of 2022 SVM management is considering new production technology that would enable SVM to improve its Gross Profit Margin on Three over the last three years of its product-life.  This investment proposal has been code named “Three Initiative.”

This exercise asks you to model the Three Initiative by changing the SVM drivers so that the SVM model reflects the addition of the Three Initiative.  The idea is that the SVM model with the Three Initiative added can be compared to the “original” SVM model as a means of understanding the impact of the Three Initiative.

Description of the Three Initiative

The specifics of the initiative are as follows:

buy $90 million in “new technology” equipment at the very end of 2022 to be used over the next three years to obtain efficiencies in the production of Three over the remaining three years of its product-life;

the new equipment will replace old equipment (the old equipment has no net salvage value and no net book value) and will be installed in the same space in an SVM facility that is now used by the old equipment;

the $90 million price tag for the new equipment includes the cost of removing the old equipment as well as the cost of installing the new equipment, both of which will be done by the equipment vendor;

the new equipment will be depreciated on a straight line basis for three years to zero, it’s anticipated EOY 2025 salvage value;

the specific anticipated efficiencies provided by the new equipment will be to lower labor hours and raw material costs incurred in the production of Three;

the specific $ savings for labor have been estimated at $55 million, $60 million and $25 million for, respectively, 2023, 2024 and 2025;

the specific $ savings for raw material costs have been estimated at $30 million, $40 million and $18 million for, respectively, 2023, 2024 and 2025;

the initiative is not expected to affect any other operating expenses;

the relationship between Inventories and Cost of Sales and between Accounts Payable and Cost of Sales will be the same as for SVM overall without the Three Initiative;

relative to their values for SVM without the Three initiative, the initiative will leave Accrued Expenses/Sales down by 50 bps, 25 bps and 10 bps in each year, respectively, and it will return to its “without-Three” SVM level for 2026 and thereafter and

any taxable income generated by the initiative would be taxed at a rate identical to SVM’s estimated Marginal Tax Rates.

Your Tasks For This Exercise

Start by opening the SVM 2022 FM2 (Target) model (available on Canvas in the Week 1 module) and renaming it G1 (x), using the “x” assigned to you in the Gn (x) File Names file on Canvas Week 3).

Your basic task is to add -- “model in” -- the Three Initiative to your G1 (x) model.

The Three Initiative financial analysis team has developed a file -- Three Initiative Drivers (see the Week 3 Module on Canvas) -- that identifies the initiatives estimated driver values.  Add these estimated driver values to cells in your G1 (x) model by linking cells in your G1 (x) model to the appropriate cells in the Three Initiative Drivers file.  You likely will want to add these values to cells in your G1 (x) model by adding them (via links to Three Initiative Drivers) to cells on Page 3 in IS INPUT, BS INPUT and/or CV INPUT.  On the other hand, you can add them anywhere in your G1 (x) model as long as you do not change the underlying structure of the model.

Follow standard protocol to finish modeling in (i.e. “adding”) the Three Initiative to your G1 (x) model.

Make sure you follow best modeling practices so that if different Three Initiative drivers values were reset in the Three Initiative Drivers file (for just those in bold green), the required changes would automatically be made in your model.  (In other words, build a model that is interactive – for example, do not use Excel’s goal-seek functionality to set the (green font) entered driver cell values in your G1 (x) model.)

You should investigate the impact of this initiative on SVM’s EBIT Margin, EBIT Tax Rate and IC Turns.  Of course, these drivers will lead to impacts on NOPAT, IC, FCF, EP, ROIC -- and more traditional performance metrics -- EPS and ROE.  For your own comfort, make sure all of these impacts as reflected in your model with the Three Initiative modeled in make sense to you.

Finally, you should audit your model in any way that you think will assure that it is working correctly.   The auditing will not be directly graded -- it’s just for your own purposes.

The impacts of the Three Initiative on the above metrics are “intermediate” impacts.  What ultimately matters is the impact on shareholders.  Using your judgement, informed by our discussions in this course re performance and decision metrics, be prepared to provide (as described below) a summary of the impact of the Three Initiative on SVM’s shareholders.

Given the interest in other finance courses in the NPV of “projects,” it would also be interesting to compare whatever metrics you decided in the previous paragraph are relevant to shareholders to the net present value (NPV) of the Three Initiative as of EOY 2022.  So, calculate the NPV of the Three Initiative as of the EOY 2022 (placing the calculations wherever you would like to) and link the resulting calculated NPV to Cover Sheet F20.  (Make sure there is no text in Cover Sheet F20, just a reference to the final NPV value, wherever you calculated that NPV.  Feel free to add a title in Cover Sheet C20)

Finally, replace whatever is in the Profile text box on the Cover Sheet with a short description of what your basic recommendation is as to the advisability of undertaking the Three Initiative and what that recommendation is based on.

Warning: Do NOT change the structure of any file provided to you. (No rows or columns added, no rows or columns deleted, no worksheets deleted (worksheets can be added), no worksheet names changed -- you get the idea.)  If you need to make extra calculations, in any provided FAMOUS files, make them on any of the Calculation pages (Page 3) or in the Calculations worksheet. The difficulty in grading models expands exponentially if the basic structures of the template models are changed.

A Side Note: The actual financing plan for the $90 million will be set by “the suits” so as to maintain SVM’s Target Capital Structure (i.e. they will implement the required plans relative to Mkt Securities, Common Dividends, Debt, etc. and you do not have to that modeling in your G1 (x) file), so it is appropriate in your G1 (x) model to leave SVM’s WACC at the level estimated in the model without the Three Initiative and it is appropriate in your G1 (x) model to let the model, as a first-pass solution, use Mkt Securities and/or Short-Term Debt to automatically provide the cash flow required by and/or generated by the Three Initiative.  In other words, your model will result in MS and/or STD amounts, as well as other financing amounts, that will be revised by the suits when you pass your Three model on to them, but those revisions will leave WACC as it is currently reflected in your SVM model.

Upload Files To Canvas

When done with this exercise, upload your G1 (x) model to Canvas using the Week 3 Second Graded Model link in the Week 3 module. The model must be posted to Canvas by 11 PM on Friday of Week 3.

Double check that you did not name the submitted model G1x or G1(x) or Gx1 or G1 (x) LastF or ….  The submitted model should be named exactly G1 (x), obviously, with x replaced by the number assigned to you.  (If files are misnamed, the preliminary grading system does not work, greatly extending the total time to grade the models.)

Special Note To Mac Users: If you are working with FAMOUS files on a Mac, save and upload a second copy of your completed model named G1 (x) mac.

Note Re Canvas File Naming: When you submit a file to Canvas, you will see a name for the file exactly as you named it.  However, if you decide to submit a redone version of your file, replacing your first submission, then Canvas will start adding “-1” or “-2” etc. to your file name.  There is nothing you can do about this, so you do not have to try to correct the Canvas file name to get back to the exact file name I asked for.

To help make the processing of your uploaded files more efficient, always do the following before uploading your models:

turn the zeros off and estimates on (Zeros On/Off button and Estimates On/Off button);

make sure your two names (LastF and First Last) are in the Analysts box on the Cover Sheet;

select the Cover Sheet worksheet;

run the Cleanup macro (Ctrl+Shift+C) and

save the model and submit it.