READ PART II OF THIS ASSIGNMENT CAREFULLY BEFORE CONTINUING YOUR WORK. Provide clear and complete responses to questions. WHEN COMPLETING PART II, PLEASE CLEARLY IDENTIFY SENSITIVITY RESULTS. [Note: In most cases, you will only need to capture and present the first sheet (?Assumptions?) that reflects changes to inputs and summarizes income and cash flow outcomes. Label sensitivities, and highlight changes to input assumptions along with associated outcomes. FCC PART II: DECISION MAKING AND ANALYSIS Your financial model (Part I) provides the ?Baseline Scenario?, which is the starting point for Part II analysis. For each decision-making extension, refer to the baseline and, unless stated otherwise, consider each situation incrementally from the baseline (ceteris paribus). Please label sensitivities and question items in a written report and paste screen captures from spreadsheet results, as necessary, to support your responses. The bottom right corner of the Assumptions Sheet contains the box ?Sensitivity Analysis, Summary Results? that highlights summary sensitivity results. A. CVP AND SENSITIVITY ANALYSIS 1. FCC?s market researcher indicates that the PID market is becoming highly competitive and sales prices must be reduced. a) Begin with the baseline scenario. By what percentage may FCC lower the unit selling prices across all quarters for both products and maintain breakeven NOI for the year? [Under DATA select WHAT IF ANALYSIS and then GOAL SEEK and SET CELL for NOI (Assumptions Sheet, cell I49) to ?0? ? BY CHANGING cell ?I17? in Assumptions Sheet]. Confirm that you obtained breakeven NOI, print and highlight your summary results (Assumptions Sheet). b) What is the projected line-of-credit balance on December 31 given this price reduction? c) Assume that the price reduction occurs. Determine the percentage change in unit volume required to achieve a ?breakeven cash flow? for the year. Perform GOAL SEEK by setting the ending LOC equal to ?0? and changing cell ?I4? (unit volume in Assumptions). Briefly explain your result. 2. Kate Forest is disappointed with the baseline net income projection. Bogey Fields suggests that an advertising campaign (cost of $25,000 per quarter), combined with increases in sales commissions (to 7% for the entire year) can improve unit PID sales and overall profitability. a) Return to the baseline. Incorporate Mr. Fields? assumptions and then determine by what percentage must total unit PID sales increase to achieve Net Income of $1,300,000 for the year? [Use GOAL SEEK and SET CELL for Net Income to $1,300,000 -CHANGE CELL ?I4? in Assumptions sheet]. b) Continue with the revised marketing assumptions (excluding volume change from part a), but also shift the overall sales mix by an additional 2.0% (i.e., change the overall sales mix sensitivity (cell I12) to 2% so that Quarter 1 becomes 5%, Quarter 2 becomes 6%, etc.). Given this shift, is the percentage increase in PID sales to achieve Net Income of $1,300,000 more or less than without the shift in sales mix? Explain. [Use GOAL SEEK - CHANGE CELL ?I4? in Assumptions sheet]. c) Return to the Baseline. Now, suppose that the PID-P product does not achieve sales growth expectations, but instead the sales mix remains constant at 90% (PID-B) and 10% (PID-P) throughout the projection period. Determine the differential NOI and LOC balance relative to the original (baseline), sales mix assumptions. [Use SCENARIO MANAGER AND PRINT THE SUMMARY]. Select DATA, WHAT IF ANALYSIS, SCENARIO MANAGER, and then EDIT Scenario ?II_A_2C? within the Assumptions Sheet. Click OK and then enter ?0? for each of the CHANGING CELLS (E13:H13). Click OK and enter zeros for all changing cells. Then, SUMMARY and OK again. Print your SCENARIO SUMMARY (modify result cells to I49:I51). Briefly interpret differences in scenario RESULTS. 3. Return to the baseline. Now, assume that FCC?s supplier experiences an increase in the cost of the special flash memory chip required by the premium model. The supplier has indicated that it will pass along the costs to FCC as an increase of $22.00 per PID-P (enter 22 into cell I22), beginning Quarter 1. a) Determine the projected annual NOI and ending LOC balance, assuming that the cost increase materializes. b) Continue with the cost increase. By what percentage must total unit PID sales volume increase to maintain the same level of NOI as before the cost increase? [Use GOAL SEEK] c) Return to the baseline. Separately determine the model elasticity for the two parameters, sales mix and sales price, with respect to NOI. Recall that model elasticity is the ratio of %? outcome to % ? parameter. Enter ?5%? into overall sales mix sensitivity cell I12 and assess the change in NOI. Then, do the same for the overall sales price sensitivity cell I17. With respect to which parameter is NOI more elastic? Briefly explain.
Paper#5087 | Written in 18-Jul-2015Price : $25