Details of this Paper

Balance sheet and investments

Description

solution


Question

Can you please complete this spreadsheet using excellent excel modeling techniques? I am looking to have as many formulas to fill the projections as possible. I believe the sheet is self-explanatory. The deadline is the this time tomorrow (EST) if at all possible.;Attachment Preview;Fullbridge Program iPads in China template.xlsx Download Attachment;iPad Distributor in China - DCF Analysis;Assumptions;Initial Investment;Income Statement Components;Projected sales volume 2011;Sales volume growth rate (2012-2015);iPad Retail Price;Change in retail price per year;iPad Wholesale Price;Change in wholesale price per year;Initial Gross Margin;Operating Expenses;Initial Operating Margin;$10,000,000;100,000 units;15%;$700;-5%;$500;-3%;29%;11% as a percent of sales (note includes depreciation);18%;Balance Sheet Components;Depreciation (% of Cumulative Gross PP&E);Year 1 Capital Expenditures;Growth in Capital Expenditures per year;Net Working Capital;Tax rate;Discount Rate;Perpetuity Growth Rate;10% assuming a 10 year depreciation schedule;$5,000,000;5%;4% as a percent of sales;35%;11.76%;3.00%;2011;2012;Projected;2013;2014;2015;2012;2013;2014;2015;$-;$-;$-;$-;Summary Income Statement;Sales;iPad (volume);iPad (price);iPad (revenue);Gross Profit;Operating Profit;Taxes;Net Income;100,000;$700;$70,000,000;$20,000,000;$12,300,000;$(4,305,000);$7,995,000;Key Balance Sheet Data;Net working Capital;$2,800,000;Cumulative Gross PP&E - Beginning of Year;Plus Capital Expenditures;Cumulative Gross PP&E - End of Year;$$5,000,000;$5,000,000;2011;Free Cash Flow;EBIAT (Operating Profit * (1-Tax Rate));+ Depreciation (10% of Cumulative Gross PP&E-EOY);- Capital Expenditures;- Change in Net Working Capital;= Free Cash Flow;Terminal Value (growing perpetuity);Total Free Cash Flows;$7,995,000;$500,000;$(5,000,000);$(2,800,000);$695,000;$695,000;Discount Rate;Perpetuity Growth Rate;NPV of Future Cash flows;Less Initial Investment;Enterprise Value;$-;View Full Attachment;Additional Requirements;Min Pages: 1;Level of Detail: Only answer needed

 

Paper#18212 | Written in 18-Jul-2015

Price : $47
SiteLock