Sensitivity Analysis of this excel spreadsheet. Where it says Emir.
Final Pro Forma & Analysis INSTRUCTIONS: Put your pro forma data from the following worksheet here for your analysis. Inputs necessary are in orange. Light gray is outputYear 012345 Key InputsSales- 0200,000,000.00240,000,000.00288,000,000.00345,600,000.00414,720,000.00 Initial Sales200,000,000.00Operating Cost- 047,560,000.0057,072,000.0068,486,400.0082,183,680.0098,620,416.00 Sales Growth20%Fixed Cost- 047,560,000.0047,560,000.0047,560,000.0047,560,000.0047,560,000.00 Cost share (operating expenses)24%Depreciation- 050,000,000.0050,000,000.0050,000,000.0050,000,000.0050,000,000.00 Fixed Cost47,560,000.00EBIT- 054,880,000.0085,368,000.00121,953,600.00165,856,320.00218,539,584.00 Upfront Investment in NFA250,000,000.00 tax- 011,524,800.0017,927,280.0025,610,256.0034,829,827.2045,893,312.64 Salvage Value480,000.00OCF- 093,355,200.00117,440,720.00146,343,344.00181,026,492.80222,646,271.36 Upfront investment in NWC40,000,000.00 NWC as a % of sales20%Net Fixed Assets250,000,000.00200,000,000.00150,000,000.00100,000,000.0050,000,000.00- 0 Tax rate21%NCS250,000,000.00- 0- 0- 0- 0- 0 Salvage Value- 0- 0- 0- 0- 0- 379,200.00 Leverage ratio84% Debt-to-Assets84%NWC40,000,000.0040,000,000.0048,000,000.0057,600,000.0069,120,000.00- 0 Equity-to-Assets16%change (NWC)40,000,000.00- 08,000,000.009,600,000.0011,520,000.00- 69,120,000.00 Cost of debt4.700% CFFA- 290,000,000.0093,355,200.00109,440,720.00136,743,344.00169,506,492.80292,145,471.36 Cost of Equity8.519% beta0.82 3-month Bill (Risk free)3.00%NPV @ WACC396,369,256.20alex Market return (annual)10%IRR37% WACC4.48% Summarize your recommendations regarding this project in the space below. Is is a good project? Why or Why not? Sales due to imoact of "singles day sales" reaching record breaking numbers https://www.macroaxis.com/invest/ratio/BABA/Debt-to-Equity-Ratio (Key input) source Sales Growth, Beacuase sales incearsed by 50% from 2019-2020, and almost another 50% in 2021, we can assume a 20% increase will account for this upward trend as well as pit falls along the way From Macroaxis.com Fixed cost, assuming op expense is 24% 20% of fixed cost 20% of initial sales Upfront investment of NWA/inital Sales) Given in key information Leverage ratio Based on Alibaba D/E Debt to asset- Calcuated on pro forma Cost of Debt- https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwj8gKrQ_5rwAhVOKVkFHb85AJ4QFjACegQIBRAD&url=https%3A%2F%2Ffinbox.com%2FNYSE%3ABABA%2Fexplorer%2Fcost_debt_aftax&usg=AOvVaw0GIzRrKjEekmDnna3pfW_W Cost of equity- Calculated on pro forma Beta - Macroaxis.com 3 month- Given in key information Market return. Macroaxis.com http://macroaxis.com/https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwj8gKrQ_5rwAhVOKVkFHb85AJ4QFjACegQIBRAD&url=https%3A%2F%2Ffinbox.com%2FNYSE%3ABABA%2Fexplorer%2Fcost_debt_aftax&usg=AOvVaw0GIzRrKjEekmDnna3pfW_Whttp://macroaxis.com/http://macroaxis.com/ Details for Inputs in Pro Forma Use this sheet to provide information about the sources and the information in the Pro Forma analysis Add information below the gray data list for any computations. Label clearly. Key InputsSOURCE: Initial SalesSales due to imoact of "singles day sales" reaching record breaking numbers Sales GrowthSales Growth, Beacuase sales incearsed by 50% from 2019-2020, and almost another 50% in 2021, we can assume a 20% increase will account for this upward trend as well as pit falls along the way Cost share (operating expenses)From Macroaxis.com Fixed CostFixed cost, assuming op expense is 24% Upfront Investment in NFA Salvage Value20% of fixed cost Upfront investment in NWC20% of initial sales NWC as a % of salesUpfront investment of NWA/inital Sales) Tax rateGiven in key information Leverage ratioLeverage ratio Based on Alibaba D/E Debt-to-AssetsDebt to asset- Calcuated on pro forma Equity-to-Assets Cost of debtCost of Debt- https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwj8gKrQ_5rwAhVOKVkFHb85AJ4QFjACegQIBRAD&url=https%3A%2F%2Ffinbox.com%2FNYSE%3ABABA%2Fexplorer%2Fcost_debt_aftax&usg=AOvVaw0GIzRrKjEekmDnna3pfW_W Cost of EquityCost of equity- Calculated on pro forma betaBeta - Macroaxis.com 3-month Bill (Risk free)3 month- Given in key information Market return (annual)Market return. Macroaxis.com WACCcalcuated on pro forma http://macroaxis.com/https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwj8gKrQ_5rwAhVOKVkFHb85AJ4QFjACegQIBRAD&url=https%3A%2F%2Ffinbox.com%2FNYSE%3ABABA%2Fexplorer%2Fcost_debt_aftax&usg=AOvVaw0GIzRrKjEekmDnna3pfW_Whttp://macroaxis.com/http://macroaxis.com/ Sensitivity Analysis PROVIDE A SENSITIVITY ANALYSIS TO CHANGES IN THE DISCOUNT RATE. INCLUDE OTHER SENSITIVITY ANALYSIS THAT MAY BE USEFUL IN YOUR PARTICULAR CASE. PLACE NPV PROFILE GRAPH FOR CHANGES IN DISCOUNT RATE, HERE "I"NPV @ "I"Emir Other Sensitivity Analysis?? Scenario Analysis Use this sheet to provide information, graphs, and explanations for the changes in NPV and IRR as you change the inputs (keep "I" = WACC) Indicate variables you are controlling in each scenario in column heading, below. Vary up to five inputs. Rank most (1) to least (10) profitableNPVIRRScenariose.g. sales Qe.g. sales P…..…… A B C D E F G H I J WACC?