Please help answer these questions on the Alliances case based on these requirements, please.

Alliance Case Assignment FIN 600 Capital Investment and Financing Decisions University of Kentucky - Spring 2021 The purpose of this document is to outline the proper format for writing up the Alliance case recommendation. The filename should consist of the case name, and the group name: (e.g., Alliance_Group101.docx). The Final document and spreadsheet should be uploaded to Canvas. The document should have a cover page with the case name, the group name, and a list of all group members. The recommendation should be written in a professional manner (i.e., do not use non-standard abbreviations, personal anecdotes, or colloquial expressions). The purpose of professional writing is to convey information in a clear and succinct fashion. As such, while there is a maximum of two pages, there is no minimum page requirement and extraneous material should be omitted. While not directly scored, grammatical errors, misspellings or other shortcomings that inhibit clarity will detract from your score. The case score will be determined as follows: - Executive Summary (a short paragraph) [20%] - Summarize the context of the case -Discussion [20\%] - Outline the steps you took to value Alliance - Discuss the assumptions used for Revenues and EBIT in the forecast. Do you think that the assumptions were reasonable? Explain your rationale. -Recommendation/Analysis [20%] - What is your estimated value of the firm? Did National overpay? - Discuss the sensitivity of your valuation to assumptions (e.g., sales increase and margin changes) - Sensitivity 1 Sales increase by 10% over the rate overwise used in the forecast for each year and the steady state. Note this is an increase BY 10% not to 10%. How much does NPV change - Sensitivity 2: hold EBIT margins constant throughout the forecast period and the steady state. How much does NPV change? - A graphical approach is recommended but not required - Interpret your results. - Supporting calculations (Very important!) (Excel spreadsheet) [40%] - Attach supporting calculations in an Excel document with the same name - Format calculations such that each table can be read independently (i.e. label all variables with descriptive names) - Your calculations should be easily followed by a third party! - Using cell references for key inputs is highly recommended. (Beware of hard- coding values) Helpful Hints: 1. First calculate the FCF using 2006 as year 1. To forecast NWC, take the NWC in 2006 as a \% of Sales and hold that \% constant through the forecast years. 2. For PPE, note that net PPE includes Depreciation and Capex. Calculate Net PPE turnover (Sales/PPE) in 2006, and hold that constant throughout the forecast period. In each year, Net PPE equals Sales/PPE Turnover. Include this amount in your calculation of FCF. 3. The spreadsheet uses the concept of a "Steady State". Normally, we calculate the terminal value by taking the FCF in the last forecast year and multiplying by 1+g. In this case, the steady state is a replacement for C1 in the Terminal Value calculation. Once you have calculated the FCF for the steady state, use this as the numerator in the Growing Perpetuity formula to calculate Terminal Value. (i.e., Do NOT multiply it by 1+g ) -3- UV0740 Exhibit 1 ALLIANCE CONCRETE: JUST ONE MORE THING Income Statement Forecast (in thousands of dollars) \begin{tabular}{llllc} 2002 & 2003 & 2004 & 2005 & Forecast \\ \hline \end{tabular} Revenue Cost of goods sold Gross margin General and administrative EBIT (earnings before interest and taxes) Interest Tax Net income \begin{tabular}{rrrrr} 128,978 & 143,560 & 161,863 & 185,815 & 209,788 \\ 100,857 & 111,203 & 125,756 & 144,594 & 166,300 \\ \hline 28,121 & 32,357 & 36,107 & 41,221 & 43,488 \\ 12,482 & 13,685 & 18,131 & 17,327 & 19,510 \\ \hline 15,639 & 18,672 & 17,976 & 23,894 & 23,978 \\ 4,537 & 6,150 & 5,964 & 5,695 & 5,695 \\ \hline 11,102 & 12,522 & 12,012 & 18,199 & 18,283 \\ 3,882 & 4,288 & 4,312 & 6,210 & 6,399 \\ \hline 7,220 & 8,234 & 7,700 & 11,989 & 11,884 \\ \hline \end{tabular} ALLIANCE CONCRETE: JUST ONE MORE THING Balance Sheet Forecast (in thousands of dollars) Exhibit 3 ALLIANCE CONCRETE: JUST ONE MORE THING Discount Rate Data 1 Economists estimate the future market premium to be 6.3% and current 20 -year treasury yields were 4.38%. Alliance Case Assignment FIN 600 Capital Investment and Financing Decisions University of Kentucky - Spring 2021 The purpose of this document is to outline the proper format for writing up the Alliance case recommendation. The filename should consist of the case name, and the group name: (e.g., Alliance_Group101.docx). The Final document and spreadsheet should be uploaded to Canvas. The document should have a cover page with the case name, the group name, and a list of all group members. The recommendation should be written in a professional manner (i.e., do not use non-standard abbreviations, personal anecdotes, or colloquial expressions). The purpose of professional writing is to convey information in a clear and succinct fashion. As such, while there is a maximum of two pages, there is no minimum page requirement and extraneous material should be omitted. While not directly scored, grammatical errors, misspellings or other shortcomings that inhibit clarity will detract from your score. The case score will be determined as follows: - Executive Summary (a short paragraph) [20%] - Summarize the context of the case -Discussion [20\%] - Outline the steps you took to value Alliance - Discuss the assumptions used for Revenues and EBIT in the forecast. Do you think that the assumptions were reasonable? Explain your rationale. -Recommendation/Analysis [20%] - What is your estimated value of the firm? Did National overpay? - Discuss the sensitivity of your valuation to assumptions (e.g., sales increase and margin changes) - Sensitivity 1 Sales increase by 10% over the rate overwise used in the forecast for each year and the steady state. Note this is an increase BY 10% not to 10%. How much does NPV change - Sensitivity 2: hold EBIT margins constant throughout the forecast period and the steady state. How much does NPV change? - A graphical approach is recommended but not required - Interpret your results. - Supporting calculations (Very important!) (Excel spreadsheet) [40%] - Attach supporting calculations in an Excel document with the same name - Format calculations such that each table can be read independently (i.e. label all variables with descriptive names) - Your calculations should be easily followed by a third party! - Using cell references for key inputs is highly recommended. (Beware of hard- coding values) Helpful Hints: 1. First calculate the FCF using 2006 as year 1. To forecast NWC, take the NWC in 2006 as a \% of Sales and hold that \% constant through the forecast years. 2. For PPE, note that net PPE includes Depreciation and Capex. Calculate Net PPE turnover (Sales/PPE) in 2006, and hold that constant throughout the forecast period. In each year, Net PPE equals Sales/PPE Turnover. Include this amount in your calculation of FCF. 3. The spreadsheet uses the concept of a "Steady State". Normally, we calculate the terminal value by taking the FCF in the last forecast year and multiplying by 1+g. In this case, the steady state is a replacement for C1 in the Terminal Value calculation. Once you have calculated the FCF for the steady state, use this as the numerator in the Growing Perpetuity formula to calculate Terminal Value. (i.e., Do NOT multiply it by 1+g ) -3- UV0740 Exhibit 1 ALLIANCE CONCRETE: JUST ONE MORE THING Income Statement Forecast (in thousands of dollars) \begin{tabular}{llllc} 2002 & 2003 & 2004 & 2005 & Forecast \\ \hline \end{tabular} Revenue Cost of goods sold Gross margin General and administrative EBIT (earnings before interest and taxes) Interest Tax Net income \begin{tabular}{rrrrr} 128,978 & 143,560 & 161,863 & 185,815 & 209,788 \\ 100,857 & 111,203 & 125,756 & 144,594 & 166,300 \\ \hline 28,121 & 32,357 & 36,107 & 41,221 & 43,488 \\ 12,482 & 13,685 & 18,131 & 17,327 & 19,510 \\ \hline 15,639 & 18,672 & 17,976 & 23,894 & 23,978 \\ 4,537 & 6,150 & 5,964 & 5,695 & 5,695 \\ \hline 11,102 & 12,522 & 12,012 & 18,199 & 18,283 \\ 3,882 & 4,288 & 4,312 & 6,210 & 6,399 \\ \hline 7,220 & 8,234 & 7,700 & 11,989 & 11,884 \\ \hline \end{tabular} ALLIANCE CONCRETE: JUST ONE MORE THING Balance Sheet Forecast (in thousands of dollars) Exhibit 3 ALLIANCE CONCRETE: JUST ONE MORE THING Discount Rate Data 1 Economists estimate the future market premium to be 6.3% and current 20 -year treasury yields were 4.38%