Question: Please download and open the attached file. The same file will be used for question 3 - 15. Do not close it until you have

Please download and open the attached file. The same file will be used for question 3 - 15. Do not close it until you have passed the assessment. eCommerce Model Question.xlsx 3 Review Later Please complete the assumptions section of the financial model. What is the closing customer base in 2020? 26,580 64,150 88,000 52,400 12 H M N > Startup year 2016 OK 2017 OK 2018 OK 2019 OK 2020 OK 2021 OK Terminal year 2022 OK O 0 5,000 15,000 10,000 30,000 2,500 20,000 15,000 10,000 47,500 B D 1 Corporate Finance Institute. Al rights reserved. 2 3 Balance Sheet Check 4 5 Assumptions 6 7 Site Traffic (Avg monthly visits) 8 Email 9 Organic Search 10 Paid Search 11 Afiliates 12 Total Monthly Visits 13 Growth Rate 14 15 Site Traffic (Annual) 12 16 Email . 17 Organic Search 18 Paid Search 19 Aflates 20 Total Annual Visits . 21 22 Conversion Rates 23 Email 24 Organic Search 25 Paid Search HH Startup eCommerce 5,000 40,000 15,000 5,000 65,000 36.896 10,000 50,000 10,000 5,000 75,000 15.496 15,000 65,000 10,000 2,500 92,500 23.396 25,000 75,000 5,000 2,500 1,07,500 16.2% 30,000 85,000 5,000 2,500 1,22,500 14.0% 58396 8.0% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 3.0% 3.5% Ready 90% + M N Startup year 2016 OK 2017 2019 2018 OK 2020 OK 2021 OK Terminal year 2022 OK OK OK 0 0 12 D 1 Corporate Finance Instute. All rights reserved. 2 3 Balance Sheer Check . 28 29 Orders placed 30 Email 31 Organic Search 32 Paid Search 33 Alates 34 Total Orders placed . 35 36 Order Details 37 Average tem value (gross) 38 of tems per order 39 Average markdown 40 Average promotion/discount 41 Cost of Goods Sold (Gross Product Marin) 42 43 Average Gross order value 44 Average Net order value 45 46 Customers 47 Churn Rate 48 Opening active customer base 49 Plus new customers Startup eCommerce Ready 200 5.0 60% 10% 20% 200 5.1 60% 8% 20% 200 5.2 60% 6% 20% 200 5.3 80% 4% 20% 200 5.4 60% 2% 20% 200 5.5 60% 2% 20% 200 5.6 60% 2% 20% 20% 20% 20% 20% 20% 0 20% 14 90% Startup year 2016 2018 2017 OK 2019 2020 OK Terminal year 2022 2021 OK OK 0.00 0.00 3.00 5.00 DIVO! 0.00 0.00 3.00 5.00 DIVO! 0.00 0.00 3.00 500 DIV/! 0.00 0.00 3.00 5.00 #DIV/0! 0.00 0.00 3.00 5.00 #DIVO! 0.00 0.00 3.00 5.00 MDIV/0! 0.00 0.00 3.00 5.00 #DIV/0! 1 Corporate Finance Institute. Al rights reserved. 2 3 Balance Sheet Check 54 Marketing Expenses 55 Variable . 56 Email (cost per click) 57 Organic Search (cost per click) . 58 Paid Search (cost per dick) 59 Alates (cost per click) 60 Average 61 62 Order Fulfilment 63 Variable 64 Freightshipping per order 65 Labor handing per order 66 Total 67 68 Fixed 69 Warehouse rent 70 71 General & Administrative 72 Fixed 73 Oficerent 74 Salaries, wages & Beneft 75 Professional fees HH Startup eCommerce Ready 12.00 5.00 17.00 12.20 5.20 17.40 12.40 5.40 17.80 1260 5.60 18 20 12.80 5.80 18.60 13.00 6.00 19.00 13.20 6.20 19.40 1,00,000 1,00,000 1,50,000 1,50,000 1,50,000 2.50,000 2,50,000 2,00.000 1,00,000 5,00,000 1,00,000 1,00,000 6,00.000 1,00,000 1,00,000 7,00,000 1,00,000 8,00,000 2,00,000 2,00,000 9,00,000 2,00,000 2,00,000 10,00,000 2,00,000 2,00,000 11,00,000 2,00,000 A. al 90% Startup year 2016 OK 8,00,000 2017 OK 9,00,000 2018 OK 11,00,000 2019 OK 14,00,000 2020 OK 17,00,000 2021 OK 18,00,000 Terminal year 2022 OK 19,00,000 5 120 5 100 5 90 Son 5 90 5 90 5 90 1 Corporate Finance Institute. All rights reserved 2 3 Balance Sheet Check 77 Total 78 79 Balance Sheet Items 80 Current Assets 81 Accounts Receivable Days 82 Inventory Days 83 Inventory Turns 365 84 . 85 Current Liabilities 86 Accounts Payable Days 87 88 Capital Expenditures 89 Technology Development 90 Ofice Equipment 91 Acquisitions . 92 93 Depreciation 94 Technology - Straight-line Years 95 Ofice Equipment-Sraight line Years 96 97 Financing 98 Interest Rate 60 50 40 30 30 30 30 5,00,000 2,00,000 0 1,00,000 50,000 0 1,00,000 50,000 0 5,00,000 50,000 0 1,00,000 2,00,000 0 1,00,000 1,00,000 0 10,00,000 5,00,000 0 3 5 3 5 3 5 5 5 5 5 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% Startup eCommerce Ready 90% Please download and open the attached file. The same file will be used for question 3 - 15. Do not close it until you have passed the assessment. eCommerce Model Question.xlsx 3 Review Later Please complete the assumptions section of the financial model. What is the closing customer base in 2020? 26,580 64,150 88,000 52,400 12 H M N > Startup year 2016 OK 2017 OK 2018 OK 2019 OK 2020 OK 2021 OK Terminal year 2022 OK O 0 5,000 15,000 10,000 30,000 2,500 20,000 15,000 10,000 47,500 B D 1 Corporate Finance Institute. Al rights reserved. 2 3 Balance Sheet Check 4 5 Assumptions 6 7 Site Traffic (Avg monthly visits) 8 Email 9 Organic Search 10 Paid Search 11 Afiliates 12 Total Monthly Visits 13 Growth Rate 14 15 Site Traffic (Annual) 12 16 Email . 17 Organic Search 18 Paid Search 19 Aflates 20 Total Annual Visits . 21 22 Conversion Rates 23 Email 24 Organic Search 25 Paid Search HH Startup eCommerce 5,000 40,000 15,000 5,000 65,000 36.896 10,000 50,000 10,000 5,000 75,000 15.496 15,000 65,000 10,000 2,500 92,500 23.396 25,000 75,000 5,000 2,500 1,07,500 16.2% 30,000 85,000 5,000 2,500 1,22,500 14.0% 58396 8.0% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 8.0% 3.0% 3.5% 3.0% 3.5% Ready 90% + M N Startup year 2016 OK 2017 2019 2018 OK 2020 OK 2021 OK Terminal year 2022 OK OK OK 0 0 12 D 1 Corporate Finance Instute. All rights reserved. 2 3 Balance Sheer Check . 28 29 Orders placed 30 Email 31 Organic Search 32 Paid Search 33 Alates 34 Total Orders placed . 35 36 Order Details 37 Average tem value (gross) 38 of tems per order 39 Average markdown 40 Average promotion/discount 41 Cost of Goods Sold (Gross Product Marin) 42 43 Average Gross order value 44 Average Net order value 45 46 Customers 47 Churn Rate 48 Opening active customer base 49 Plus new customers Startup eCommerce Ready 200 5.0 60% 10% 20% 200 5.1 60% 8% 20% 200 5.2 60% 6% 20% 200 5.3 80% 4% 20% 200 5.4 60% 2% 20% 200 5.5 60% 2% 20% 200 5.6 60% 2% 20% 20% 20% 20% 20% 20% 0 20% 14 90% Startup year 2016 2018 2017 OK 2019 2020 OK Terminal year 2022 2021 OK OK 0.00 0.00 3.00 5.00 DIVO! 0.00 0.00 3.00 5.00 DIVO! 0.00 0.00 3.00 500 DIV/! 0.00 0.00 3.00 5.00 #DIV/0! 0.00 0.00 3.00 5.00 #DIVO! 0.00 0.00 3.00 5.00 MDIV/0! 0.00 0.00 3.00 5.00 #DIV/0! 1 Corporate Finance Institute. Al rights reserved. 2 3 Balance Sheet Check 54 Marketing Expenses 55 Variable . 56 Email (cost per click) 57 Organic Search (cost per click) . 58 Paid Search (cost per dick) 59 Alates (cost per click) 60 Average 61 62 Order Fulfilment 63 Variable 64 Freightshipping per order 65 Labor handing per order 66 Total 67 68 Fixed 69 Warehouse rent 70 71 General & Administrative 72 Fixed 73 Oficerent 74 Salaries, wages & Beneft 75 Professional fees HH Startup eCommerce Ready 12.00 5.00 17.00 12.20 5.20 17.40 12.40 5.40 17.80 1260 5.60 18 20 12.80 5.80 18.60 13.00 6.00 19.00 13.20 6.20 19.40 1,00,000 1,00,000 1,50,000 1,50,000 1,50,000 2.50,000 2,50,000 2,00.000 1,00,000 5,00,000 1,00,000 1,00,000 6,00.000 1,00,000 1,00,000 7,00,000 1,00,000 8,00,000 2,00,000 2,00,000 9,00,000 2,00,000 2,00,000 10,00,000 2,00,000 2,00,000 11,00,000 2,00,000 A. al 90% Startup year 2016 OK 8,00,000 2017 OK 9,00,000 2018 OK 11,00,000 2019 OK 14,00,000 2020 OK 17,00,000 2021 OK 18,00,000 Terminal year 2022 OK 19,00,000 5 120 5 100 5 90 Son 5 90 5 90 5 90 1 Corporate Finance Institute. All rights reserved 2 3 Balance Sheet Check 77 Total 78 79 Balance Sheet Items 80 Current Assets 81 Accounts Receivable Days 82 Inventory Days 83 Inventory Turns 365 84 . 85 Current Liabilities 86 Accounts Payable Days 87 88 Capital Expenditures 89 Technology Development 90 Ofice Equipment 91 Acquisitions . 92 93 Depreciation 94 Technology - Straight-line Years 95 Ofice Equipment-Sraight line Years 96 97 Financing 98 Interest Rate 60 50 40 30 30 30 30 5,00,000 2,00,000 0 1,00,000 50,000 0 1,00,000 50,000 0 5,00,000 50,000 0 1,00,000 2,00,000 0 1,00,000 1,00,000 0 10,00,000 5,00,000 0 3 5 3 5 3 5 5 5 5 5 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% Startup eCommerce Ready 90%

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

Please download and open the attached file. The same file will be used for question 3 - 15. Do not close it until you have passed the assessment. eCommerceModel Questions.xlsx 3 Please complete the...

Real Estate Investment Modeling I believe some background data and access to the models would make answering the questions feasible; however, I am only able to attach pictures(or post links) which...

I need help, the 2 excel spreadsheets are needed to answer question 5 H N 0 de were FINANCIAL STATEMENTS Balance Sheet Check 2011 OK E Historical Results 2012 2013 OK OK 2015 2014 OK 2020 Forecast...

M 1 Coporate Finance instute Al ghts reserved Historical Results Forecast Period 2 FINANCIAL STATEMENTS 3 Balance Sheet Check 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 OK OK OK OK OK OK OK OK...

'ZY ab vus IDA A A * 12 9 % - I lili III IM alil 4 fx 2 B C D L M N 2 FINANCIAL STATEMENTS 3 Balance Sheet Check 2011 OK Historical Results 2012 2013 OK OK 2014 OK 2015 OK K Forecast Period 2017 2018...

What is the share price when COGS increases by 5%? the excel spreadsheet is used to answer the question H M C D Copii Alghts reserved FINANCIAL STATEMENTS Balance Sheet Check E Historical Results...

Please download and open the attached file. This file will be used for following questions. Sensitivity Model Questions.xIsx 5 Review Later What is the share price when the discount rate is 13% and...

Please download and open the attached file. This file will be used for following questions. Sensitivity Model Questions.xlsx 6 What is the share price when COGS increases by 5%? 22.71 26.87 29.29...

Please help me in excel! If the Delivery Truck is the most economical but the slowest shipping method and Express Air is the fastest but the most expensive one, the company appropriately spent...

Please open the attached file and answer only questions 9 and 10. one and ahalf page ISSUES IN ACCOUNTING EDUCATION Vol. 27, No. 2 2012 pp. 441-459 American Accounting Association DOI:...

In a nuclear reaction, calculate the percentage of uranium 235U enrichment to get the reproduction factor around 2.10, assume fission cross section for 235U is 584 b for thermal neutrons, while that...

A person wants to save for retirement and is considering investing in a bond. The bond has a face value of $100,000, a coupon rate of 6%, and matures in 5 years. The current market interest rate is...

oept either of them? \ table [ [ Year , Cash Flow ( A ) , Cash Flow ( B ) ] , [ 0 , - $ 4 8 , 0 0 0 , - $ 6 0 . 0 0 0 ] , [ 1 , 1 6 , 0 0 0 , 1 2 . 0 0 0 ] , [ 2 , 1 9 . 0 0 0 , 1 4 , 0 0 0 ] , [ 3 ,...

A company is preparing completing their Cash Budget. The following data has been prepared for cash receipts and payments. January February March Cash receipts $1,061,200 $1,182,400 $1,091,700 Cash...