Please Let me know the cost for this assignment
ACC 345 Business Valuation Model Summary- Start Here To begin follow these steps: 1. Enter the numbers from your company's balance sheet and income statement for each year, starting with the most recent year through the prior five years (Example: if the most recent is 2017 then go back through 2013). 2. Ratios auto-calculate but you may wish to make an adjustment if necessary. You will only use the ratios for explanatory or analysis purposes in your report. There is nothing more to do with them in this workbook. 3. Create your prospective analysis by changing the growth rate for Revenue, the percentage of Revenue for Gross Profit and Operating expenses, and then add Other Income or expense items. You can do this in the cells highlighted in yellow. This will give you your projected net income, which you will then use to discount to present value on the "dcf" tab later. The default number for Revenue in the prospective analysis is the most recent year's Revenue number plus 2.5%. You may change it. 4. On the "discount rate" tab you are welcome to leave the number as is or go through and make adjustments. In most cases you will need access to data that is unavailable or requires a paid subscription, which is why you're allowed to keep the default values. If you're able to obtain any of those figures then you may use them. The detail was provided to expose you to the concepts, but not actually require the research since it may be cost prohibitive. 5. The "dcf" tab feeds your projected Net Income figures from the "prospective analysis" tab. To that number you will add back depreciation since it's a non-cash item and then subtract expected capital expenditures or and planned debt reductions. You may estimate these if you're unable to find any projection by the company. It is not required that these be fully accurate since you don't have access to management's plans. Enter those numbers in the yellow highlighted cells. You shouldn't have to change any other cells in that tab. 6. On the last tab, "valuation summary", the only values you need to change are the cells in yellow for the DLOC and the DLOM. You may leave these as the default values since these also require access to data that may be only acquired via subscription or purchase. If you're able to find material supporting a change in those values then you're free to do so. The goal in introducing them in this manner is to get you exposed to the concepts, not the actual calculation as that is beyond the scope of this course. Consider these factors when working through the model: 1. The financial statements you encounter in the annual report will look differently than they do in this model. Categories will be different than what you find in the annual report, so just use your best judgement when classifying them and if you need to lump certain costs together then do so. (Example: your company shows Cost of Sales of $100k, G&A of $50k, and Marketing expense of $10k. Combine the G&A and Marketing in the single line on the income statement called "General, Administrative and other non-operating expenses" in the amount of $60k. This places Marketing into the "Other" catch all category. 2. You may insert any "Key Assumptions" that you want to convey using the space below the balance sheet, income statement, or prospective analysis. This could be anything from combining certain line items to explaining apparent anomalies. 3. Make sure to net your interest income and expense on the income statement. So in some years you may have a positive balance and a negative in others. 4. The "Normalization adjustments" listed on the "income statement" tab are referring to the adjustments discussed in module three. To recap - Normalization adjustments are changes that you as an analyst can make in order to "normalize" any anomalies or non-recurring items that may have been reported in the financial statements. For example, if your company was exposed to a natural disaster and you know management does not expect that type of major expense in the future then you can add it back under this section. Another example would be a class-action lawsuit that resulted in a major settlement. While companies are always subject to lawsuits, one that results in a material settlement may be removed if it's unexpected to occur again in the near future. balance sheet Company ABC Inc. Balance Sheets (in millions) December 31, 2014 through 2018 Common-size analysis 2013201420152016201720132014201520162017 Assets Current Assets Cash and cash equivalents$ -$ -$ -$ -$ -- 0%- 0%- 0%- 0%- 0% Accounts receivable, net------ 0- 0- 0- 0- 0 Inventory------ 0- 0- 0- 0- 0 Other current assets------ 0- 0- 0- 0- 0 Total current assets------ 0- 0- 0- 0- 0 Property, plant & equipment, net------ 0- 0- 0- 0- 0 Other assets Intangibles------ 0- 0- 0- 0- 0 Other assets------ 0- 0- 0- 0- 0 Total other assets------ 0- 0- 0- 0- 0 Total Assets$ -$ -$ -$ -$ -- 0%- 0%- 0%- 0%- 0% Liabilities and Stockholders' Equity Current Liabilities Accounts payable$ - 0$ - 0$ - 0$ - 0$ - 0- 0%- 0%- 0%- 0%- 0% Accrued expenses & other current liabilities------ 0- 0- 0- 0- 0 Current portion of debt and leases------ 0- 0- 0- 0- 0 Total current liabilities------ 0- 0- 0- 0- 0 Long-Term Liabilities Long-term debt and lease obligations------ 0- 0- 0- 0- 0 Other long-term liabilities------ 0- 0- 0- 0- 0 Total long-term liabilities------ 0- 0- 0- 0- 0 Total Liabilities------ 0- 0- 0- 0- 0 Stockholders' Equity Common stock, less treasury------ 0- 0- 0- 0- 0 Additional paid in capital------ 0- 0- 0- 0- 0 Retained earnings------ 0- 0- 0- 0- 0 Other comprehensive income (loss)------ 0- 0- 0- 0- 0 Total Stockholders' Equity------ 0- 0- 0- 0- 0 $ -$ -$ -$ -$ -- 0%- 0%- 0%- 0%- 0% 0.000.000.000.000.00 $ -$ -$ -$ -$ - *Key Assumptions: income statement Company ABC Inc. Statements of Income (in millions) December 31, 2014 through 2018 Common-size analysis 2013201420152016201720132014201520162017 Sales$ -$ -$ -$ -$ -- 0%- 0%- 0%- 0%- 0% Cost of Sales------ 0- 0- 0- 0- 0 Gross Profit------ 0- 0- 0- 0- 0 General, administrative and non-operating expenses------ 0- 0- 0- 0- 0 Operating Income------ 0- 0- 0- 0- 0 Other Income (Expense) Interest (expense)------ 0- 0- 0- 0- 0 Gain (loss) on sale of assets------ 0- 0- 0- 0- 0 Other------ 0- 0- 0- 0- 0 ------ 0- 0- 0- 0- 0 Normalization adjustments Non-recurring items------ 0- 0- 0- 0- 0 Legal settlements------ 0- 0- 0- 0- 0 Other------ 0- 0- 0- 0- 0 ------ 0- 0- 0- 0- 0 Net income, before tax$ -$ -$ -$ -$ -- 0%- 0%- 0%- 0%- 0% *Key Assumptions: ratios Company ABC Inc. Financial and Operating Ratios December 31, 2014 through 2018 20132014201520162017 Liquidity Ratios Current Ratio- 0- 0- 0- 0- 0 Quick Ratio- 0- 0- 0- 0- 0 Working Capital$ -$ -$ -$ -$ - Activity Ratios Receivable Turns- 0- 0- 0- 0- 0 Days in Receivables- 0- 0- 0- 0- 0 Revenues/Working Capital- 0- 0- 0- 0- 0 Revenues/Fixed Assets- 0- 0- 0- 0- 0 Revenues/Total Assets- 0- 0- 0- 0- 0 Inventory Turns- 0- 0- 0- 0- 0 Days in Inventory- 0- 0- 0- 0- 0 Payables Turns- 0- 0- 0- 0- 0 Days in Payables- 0- 0- 0- 0- 0 Coverage/Leverage Ratios Fixed Assets/Equity- 0- 0- 0- 0- 0 Profitability Ratios Return on Equity- 0%- 0%- 0%- 0%- 0 Return on Total Assets- 0%- 0%- 0%- 0%- 0 Net Profit on Revenues- 0%- 0%- 0%- 0%- 0 N/A - Not applicable Change in salesERROR:#DIV/0!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!-100.00% prospective analysis Company ABC Inc. Projected Income Statement (In millions) 20182019202020212022Terminal Revenue$ - 0$ - 0$ - 0$ - 0$ - 0$ - 0 Growth2.5%2.5%2.5%2.5%2.5%2.5% Gross profit- 0- 0- 0- 0- 0- 0 Percentage of revenue7.0%7.0%7.0%7.0%7.0%7.0% Operating expenses- 0- 0- 0- 0- 0- 0 Percentage of revenue5.0%5.0%5.0%5.0%5.0%5.0% Other income (expense) Interest income (expense)- 0- 0- 0- 0- 0- 0 Other- 0- 0- 0- 0- 0- 0 - 0- 0- 0- 0- 0- 0 Percentage of revenue0.0%0.0%0.0%0.0%0.0%0.0% Net income$ - 0$ - 0$ - 0$ - 0$ - 0$ - 0 *Key Assumptions: discount rate Company ABC Inc. Development of Discount Rate and Capitalization Rate RateNote Risk-free long term U.S. Government bond rate2.6%(A) Equity risk premium6.0(B) Industry premium estimate1.5(C) Specific company risk3.0(D) Cost of equity (Discount rate)13.1Sum of (A) - (D) Less: Long-term sustainable growth rate(2.5)(E) Capitalization rate10.6% (A) Yield on the twenty-year U.S. Treasury bond as of December 31, 20XX, per the U.S. Treasury (B) Long-horizon expected return of large stocks over risk free securities, U.S. Equity Risk Premium (6.0%) (C) SIC code XX, 1.5% (D) Appraiser's judgement concerning company-specific risk (E) Estimated long-term growth rate based on inflation, Federal Reserve Bank of Philadelphia Sources: United States Treasury ***You may use other sources to update any of these values; list the applicable source if used. Existing values are actual figures obtained from sources used in prior years. You may use these as default values since a detailed development of the discount rate is beyond the scope of this class. dcf Company ABC Inc. Discounted Cash Flow Method (In millions) Projected for Years Ending December 31, Terminal 20182019202020212022Value Forecasted Net Income$ - 0$ - 0$ - 0$ - 0$ - 0$ - 0 Plus: Depreciation- 0- 0- 0- 0- 0- 0 Less: Capital expenditures- 0- 0- 0- 0- 0- 0 Debt reduction- 0- 0- 0- 0- 0- 0 Net Cash Flow$ - 0$ - 0$ - 0$ - 0$ - 0$ - 0 Present value of cash flows$ - 0$ - 0$ - 0$ - 0$ - 0 Discount rate:13.1% Terminal period cash flows$ - 0 Capitalization rate:10.6%÷10.6% Capitalized terminal cash flow$ - 0 Net present value of terminal cash flow, discounted into perpetuity$ - 0