I am working on my project but I am so confused, and I am not sure if I am on the right track. Can you plz show me how to do it?

Project Instructions ...Complete independence does not mean arrogant isolation or a superior disdain for all help....If it is man's (sic) privilege to be independent, it is equally his duty to be inter-dependent.... M.K.Gandhi The project is a comprehensive learning tool for the corporate finance class and incorporates your understanding and application of all that you will learn in this class. It includes the creation of financial statements, financial forecasts, financial planning, valuation, calculation of the cost of capital and financial decision making and risk analysis. Your group plans to start a new business. The following is required: 1. Describe briefly what line of business you plan to enter. 2. Analyze the start up, recurring, and terminating revenues and expenses. 3. The company should be modeled after an existing business to avoid such complications as estimating development cost of new technology and methods, etc. It is very helpful to find someone in the community in this business that is willing to help you think through the process. 4. Do not, however, ask for the actual figures for their business. Asking for methods of estimating what your revenues and costs might be is the more successful strategy. Using resources, such as industry associations may help you to acquire important data. In addition, industry reports from Standard & Poors, Value Line, and others may provide insight into how the industry operates. 5. You will make assumptions on growth and other factors. Please state clearly these assumptions in your report. 6. Note that your assumptions and inputs maybe different if you are a true start-up company as opposed to an existing company that is adding a new line of business. 1 PART A: INPUTS (Below are sample hypothetical numbers that may not all apply to your company) Using Excel or some other spreadsheet software, create an input or an assumptions sheet that should include at a minimum the following: Part I: Input Data Economic Life Salvage or Terminal Value 4 $25,000 Tax Rate Cost of Capital (Show separate calculations) Units Sold Sales Price Per Unit Incremental Cost Per Unit Inflation Rate 40% 10% 1250 $200 $100 3% 0 2006 Sales rate increase input rate increase Salaries+benefits rate increase Years 1 2 3 4 2007 2008 2009 2010 5% 7% 9% 11% 3% 4% 5% 6% 3% 4% 5% 6% PART B: INITIAL EXPENSES Create an initial outlay: Net Investment Outlays Equipment (70) Freight & Inst. (30) Change In NWC (20) Net CF $(120) Depreciation Calculations: Annual Depreciation Expense Year 1 2 3 % 0.33 0.45 0.15 x Basis $100 $100 $100 = 2 MACRS Depr. $33 $45 $15 Straight Line Depr. 25 25 25 4 0.07 $100 $7 25 PART C: INCOME STATEMENT The next step is to actually gather the revenue and expense estimates for the initial outlay, five years of recurring revenues and expenses, and terminating expenses. This should be started as soon after you have made your decision on a business as possible. You do not need the class lecture on capital budgeting to do this part. You are required to place these figures in a spreadsheet to demonstrate your knowledge of PC use. This will significantly reduce your time in preparing your write-up as well. You are required to vary your cash flows over the five-year period, i.e. revenues should increase in a startup business over these five years and at least some expenses should vary with the amount of revenue, resulting in different cash flows each year. After the fifth year, you assume that you will grow at a steady long term growth rate. You will need to calculate the horizon value for the firm using that assumption. Be sure that you pay yourself something each year as an employee of the business. You should not assume that the net income is your salary. Although examples we work in class frequently do not list revenue as a differential cash flow, revenue is a differential cash flow in your project and you must include it as an item in your spreadsheet. Example: Operating Costs from Y1 to Y5 Example of costs Y1 Y2 Y3 Y4 Y5 Computers Phone/Fax/Printer/Copier Furniture Office Supplies Business Licence Software Marketing and miscelleneous costs Utilities/Service Salaries/Wages Building Lease/Rent TOTAL 3 Example: Creation of an income statement and calculation of Net Cash Flows: 0 2015 Investment Outlays: Equipment Working Capital Operating Cash Flows over Project's Life Units sold Sales (show sales growth assumptions in separate sheet) Costs (from cost sheet) Net Revenue (show assumptions in separate sheet) Depreciation (from depreciation -input sheet) Before-tax-income Taxes (40%) Net Income Add Depreciation Net Operating CF Net working capital Net PPE Change in NWC Change in PPE FCF = NOPAT-(Change in NWC+Change in PPE) Terminal Value Enterprise value 4 1 2016 2 2017 3 2018 4 2019 5 2020 PART D: COST OF CAPITAL Calculation of the cost of capital: First, figure out the target capital structure, i.e., how much debt and equity you plan to use. (this total should match your initial outlay). Cost of debt: You should also call a bank or other lending institution to find out what interest rate they would charge to make a loan to this type of business. Be sure to state that you are a student working on a project. Some students have run into complications because bank employees assume you are trying to get them to commit to a rate without providing sufficient information. Cost of Equity CAPM US Treasury Bond (30 year) Equity (Market) Risk Premium Beta (use similar companies average betatry to not use very mature companies) Cost of Equity (see Nike case for calculations) Bond Yield plus risk premium Cost of debt (before tax) + Equity (Market) Risk Premium Average the two methods to come up with cost of equity. Cost of Capital Component Common Equity Debt Preferred Stock WACC Weight Cost 5 Weighted Cost PART E: Capital Budgeting Analysis: Use Net Cash Flows calculate Payback NPV IRR MIRR PART F: RISK ANALYSIS You must apply at least one of the risk analysis techniques we discuss in the unit on \"risk analysis in capital budgeting.\" Evaluating Risk: Sensitivity Analysis Risk in capital budgeting really means the probability that the actual outcome will be worse than the expected ou For example, if there were a high probability that the $81,557 expected NPV as calculated above will actually tur out to be negative, then the project would be classified as relatively risky. The reason for a worse-than-expected outcome is, typically, because sales were lower than expected, costs were higher than expected, or the project tur out to have a higher than expected initial cost. In other words, if the assumed inputs turn out to be worse than ex then the output will likewise be worse than expected. You may use Excel to examine the project's sensitivity to changes in the input variables. Example % % Deviation WACC Deviation From NPV from Base Case WACC 81,557 Base Case -30% 7.0% $104,964 -30% -15% 8.5% $92,942 -15% 0% 10.0% $81,557Base Case 0% 15% 11.5% $70,763 15% 30% 13.0% $60,520 30% 6 1st YEAR UNIT % SALES Deviation SALVAGE Units NPV from Variable NPV Sold $81,557 Base Case Cost $81,557 875 $10,235 -30% $17,500.00 $78,483 1,063 $45,896 -15% $21,250.00 $80,020 1,250 $81,557Base Case 0% $25,000.00 $81,557 1,438 $117,218 15% $28,750.00 $83,093 1,625 $152,879 30% $32,500.00 $84,630 SCENARIO ANALYSIS: Scenario analysis extends risk analysis in two ways: (1) It allows us to change more than one variable at a time, h to see the combined effects of changes in several variables on NPV, and (2) It allows us to bring in the probabilit changes in the key variables. Suppose there is a 25% chance of selling only 900 units per year (Worst Case), a 50% chance of selling 1,250 units per year (Base Case), and a 25% chance of selling 1600 units per year (Best Case). In a scenario analysis, we will find the NPV for each scenario. We could do this simply by entering the value of unit sales for each scenario and then recording the NPV (this is what we did for the table below). Alternatively, we could use Tools, Scenarios to define the inputs for each scenario, which we did. In fact, you could even use Tools, Scenarios, and then click the Summary button on the dialog box, and it will automatically create a table similar to the one below. This is a powerful feature of Excel, and we encourage you to explore it. Scenario Analysis Squared Deviation Scenario Best Case Base Case Worst Case Probabilit y Unit Sales 25% 50% 25% 900 1250 1600 NPV times probability $14,989 $1,107,791,372.25 $81,556 $0.00 $148,123 $1,107,791,372.25 Expected NPV = sum, prob times NPV Standard Deviation = Sq Root of column F sum Coefficient of Variation = Std Dev / Expected NPV 7 $81,556 $47,070 0.58 PART G: REPORT AND Discussion of results: 1. Your analysis and write-up must include the nature of your business (management and marketing plans are not required), 2. The assumptions you have used to estimate your cash flows (i.e. when do you hire new people, how is the growth in revenue estimated, etc.) 3. Calculations of the initial outlay, after-tax cash flows, and terminating cash flows, five decision criteria (pay back, net present value, profitability index, internal rate of return, and modified internal rate of return), 4. Based on the calculated decision criteria, accept or reject the business startup. 5. Discuss the risk and possible mitigations associated with the business and provide suggestions on how to make the business profitable if a reject decision is reached. 6. The write-up will be graded for concept understanding, correct techniques, and professional presentation to include sentence structure, spelling, appearance, etc. Grading will be based on the number of errors. Three points will be deducted for each \"minor\" error - calculation error, erroneous accept/reject decision, multiple spelling errors, etc. \"Major\" errors have a deduction of 10 points - failure to follow instructions, different accept/reject decisions for NPV, PI, IRR and MIRR, misinterpretation of risk results, etc. An error made early in the process is counted only once even though it may cause errors in following calculations. In other words, I will use your erroneous figure from that point forward to evaluate your remaining items. 8 Group Project Grade: 100 points Intermediate Report Conceptual understanding of problem: Assumptions made: Analysis: Final Report (timeliness and presentation) Peer Evaluation - 15 points (timeliness, neatness, presentation of report and content will be graded) 10 points 20 points 25 points 10 points 20 points (and used as a basis of the rest of your grade) Rules of group work: o All group work will be self policed, except in extreme circumstances. o The Intermediate report will be evaluated for 15 points. At the end of the module a confidential detailed peer evaluation will be given to your classmates and the average rating of your group participants. This will affect 20% of your grade on the project and will also be used to evaluate the remaining 65 percent of your grade. Here is an example of the grading for a group with three participants A,B and C with different evaluations for the same project. Criteria Participants A B C 1Intermediate report grade (15%) 14 14 14 2Peer Evaluation (20%) 12 18 20 (Say, group received 62 of 65) 60% 90% Remainder grade 65% (12/20) of (18/20) of 100% (20/20) (based on peer evaluation) 62 62 of 62 3 Group received grade of 62/65 37.2 55.8 62 1+2+3Total grade for participant Grade on project 9 63.2 D 88 B+ 96 A Little Paris Bakery Company Dunkin Donuts Panera Bread Krispy Kreme Tim Hortons Au Bon Pain Paradise Bakery & Caf Corner Bakery Caf Ticker DNKN PNRA KKD n/a n/a n/a n/a Beta 0.079318 0.181447 1.88132 canadian companuy owned in part by panera bread owned in part by panera bread privately held Panera Bread Revenue # of store Revenue Per Store COGS COGS% Operating Profit Operating % Labor Labor % Net Income NI % Revenue Growth in thousands 2014 2013 2012 Average 2,529,195.00 2,385,002.00 2,130,057.00 2,348,084.67 1,880.00 n/a n/a 1,345.32 822,127.00 767,782.00 683,586.00 757,831.67 32.51% 32.19% 32.09% 32% 275,943.00 309,756.00 282,870.00 289,523.00 10.91% 12.99% 13.28% 12% 685,576.00 625,457.00 559,446.00 623,493.00 27.11% 26.22% 26.26% 27% 179,293.00 196,169.00 173,448.00 182,970.00 7.09% 8.23% 8.14% 8% 2013-2014 6% 2012-2013 12% 2011-2012 17% 2010-2011 18% Average 13% Krispy Kemne 490,334.00 987.00 496.79 Dunkin Donuts 748,709.00 11,275.00 66.40 37% 22% 28% 41% Total Average 1,195,709.22 398,569.74 4,714.00 Revenue in Thousands 636.17 636,171.05 Quarter Per Month Per Day 159,042.76 13,253.56 441.79 30% 27% 32% n/a 10% 2013-2014 2012-2013 2011-2012 2010-2011 Average 29% 11% 15% 2013-2014 7% 5% 2012-2013 8% 8% 2011-2012 11% 4% 2010-2011 4% 9% Average 8% 6% 6% 9% 11% 10% 9% Assumed Unit Cost Number of purchas 5 88.36 Assumed Unit Cost Number of purchas 3 147.26 # of customers assu 73.63 7.36 duinkin combines payroll with business and professionalk services on p&L, exlude from labor calculation Little Paris Bakery Projected Income 1 2 6% 1.49% 3 7% 2.37% 4 7% 2.54% 5 8% 2.33% 159,042.76 48,382.57 46,545.31 64,114.89 168,585.33 51,285.52 50,073.16 67,226.64 180,386.30 54,875.51 54,042.85 71,467.94 193,013.34 58,716.79 57,921.88 76,374.67 208,454.41 63,414.14 62,427.52 82,612.76 30,000.00 60,540.00 155.00 637.00 6,000.00 718.72 98,050.72 (33,935.83) 30,000.00 60,540.00 125.00 5,400.00 718.72 96,783.72 (29,557.08) 30,000.00 62,077.72 125.00 4,800.00 718.72 97,721.44 (26,253.49) 30,000.00 62,077.72 125.00 4,200.00 718.72 97,121.44 (20,746.77) 30,000.00 63,598.62 125.00 3,600.00 718.72 98,042.34 (15,429.58) 88,258.57 (33,935.83) (29,557.08) (26,253.49) (20,746.77) (15,429.58) 88,258.57 (33,935.83) (13,574.33) (20,361.50) (29,557.08) (11,822.83) (17,734.25) (26,253.49) (10,501.40) (15,752.10) (20,746.77) (8,298.71) (12,448.06) (15,429.58) (6,171.83) (9,257.75) 88,258.57 35,303.43 52,955.14 Sales Rate Increase Salaries + Benefits Increase Projected Sales Projected COGS Projected Labor Projected Gross Profit Selling General and Administrative Rent Utilities Phone Service Business License & Certifications Marketing and Advertising Software Total Operating Income Depreciation Earnings Before taxes Interest Expense Pretax Earnings Taxes (40%) Net Income 6 7% 2.54% Basing off of inflation increase 223,046.22 67,853.13 should we include inflation increase in p 66,934.52 88,258.57 - ase in projected COGS? Year 2014 Operating Costs from Y1 to Y5 Example of costs R ST Recurring? ST R ST/R R R ST R Office Supplies Equipment Oven "full size gas convection" Stoves POS Terminal System Computers Printer Phone/Fax/Printer/Copier Refrigerator & Door Refrigerator Freezer Refrigerated Open display Cart Hocking cake sets 2 Microwave Cookware & smallwares Furniture Tables, stools & booths Building Lease/Rent Software-Quickbooks +security Marketing and miscelleneous costs Utilities/Service Certification Business Licence COST OF SALES Food, beverage, and packaging Labor Year 2013 KRISPY CREME Revenues Y1 2400 Y2 2400 Y3 2400 Y4 2400 2400 4068 2000 700 1500 250 155 5000 5000 8200 100 13.9 200 2000 22500 30000 718.72 6000 60540 512 125 10 years 10 years 5 years 5 years 5 years 5 years 10 years 10 years 10 years 5 years 5 years 7 years 5 years 8 years 30000 718.72 5400 60540 30000 718.72 4800 62077.716 125 125 325306 306825 Cost of sales Food, beverage, and packaging shop labor Delivery labor Employee benefits Y5 121669 0.374014005 58943 0.181192477 24586 0.075578071 20825 0.064016649 115623 0.376836959 54270 0.176876069 24280 0.079133056 21259 0.069287053 9287 0.028548505 11334 0.036939624 30060 0.092405305 490334 1.065177014 34256 0.111646704 460331 Operating income Net Income Total Revenues We are being conservatives on depreciation 30000 30000 718.72 718.72 4200 3600 62077.716 63598.62004 125 http://www.premierfoodsafety.com/fmcertification/prometric?zip=30324 125 Estimation because we couldnt find a real number. But we know that we have to renew it every year TOTAL POS Terminal http://www.rakuten.com/prod/complete-retail-point-of-sale-pos-system-includes-everything-you-need/269130160.html?listingId=352354026&sclid=pla_google_ThePerfectPOS&adid=29963&rmatt=tsid:1012713%7Ccid:236419 FAX http://www.officedepot.com/a/products/101071/Panasonic-KX-FL421-FaxCopier-Machine/?cm_mmc=PLA-_-Google-_-Printers_Scanners_Faxes-_-101071-VQ6-47886840716-VQ16-c-VQ17-pla-VQ18-online-VQ19-101071-VQ20-8366 Open display http://www.webstaurantstore.com/13495/dual-service-curved-glass-merchandisers.html Building Lease/Rent We lock in the rent lease for 5 years Utilities/Service In year 3 and 5 we fixed it by inflation. Based on info house. Certification Georgia certification- Food Safety Manager- Required certification for 5 years- 9249%7Cagid:13978094569%7Ctid:kwd-113191961089%7Ccrid:60879541009%7Cnw:g%7Crnd:14405117375299045781%7Cdvc:c%7Cadp:1o2&gclid=CjwKEAiA3_axBRD5qKDc__XdqQ0SJAC6lecAEbTYmgWCge5XGHxEaudMZXlD3RvNeDJ8_o 61903716-VQ21-&gclid=CjwKEAiA3_axBRD5qKDc__XdqQ0SJAC6lecAckwikm7bi9GhaCAtj6orKyug8NZnB532SsfQopw_HxoCBbnw_wcB DJ8_o-Vy7OYbBoCjhHw_wcB Annual Depreciation Expenses Year 1 2 3 4 5 6 7 8 9 10 11 12 13 Equipments Oven "full size gas convection" Stoves POS Terminal System Computers Printer Phone/Fax/Printer/Copier Refrigerator & Door Refrigerator Freezer Refrigerated Open display Cart Hocking cake sets 2 Microwave Cookware & smallwares % x 0.2 0.2 0.4 0.1 0.1 0.1 0.2 0.2 0.2 0.2 0.2 0.2 0.3 Basis 4068 2000 700 1500 250 155 5000 5000 8200 100 139 200 2000 29312 I don't see furnitures added There are two rows for the same items: Printer??? = MACRS Straight Line Salvage Value Straight Line Depr Calcualtion Depreciation Depr./Year 813.6 10 1500 256.8 400 10 500 150.0 280 5 200 100.0 150 3 200 433.3 25 3 50 66.7 15.5 3 25 43.3 1000 10 2000 300.0 1000 10 2000 300.0 1640 10 4500 370.0 20 5 25 15.0 27.8 10 50 8.9 40 10 70 13.0 600 10 600 140.0 Deprecatio How much on= (Cost -Salvage Value)/ Life h for salvage value estimation so that we can calculate the Depreciation