Income Statement for the year ended 2012 2013 2014 Sales 498541 367450 389864

Income statement for the year ended 2012 2013 2014

This preview shows page 143 - 155 out of 175 pages.

Income Statement for the year ended: 2012 2013 2014 Sales $ 498,541 $ 367,450 $ 389,864 Cost of Goods Sold Inventory, 1/1 $ 116,081 $ 85,919 $ 112,969 Purchases $ 115,372 $ 147,970 $ 187,540 Available for sale $ 231,453 $ 233,889 $ 300,509 Inventory, 12/31 $ 85,919 $ 112,969 $ 129,125 Cost of goods sold $ 145,534 $ 120,920 $ 171,384 Gross profit $ 353,007 $ 246,530 $ 218,480 Expenses Advertising $ 6,166 $ 5,915 $ 6,770 Office lease $ 8,750 $ 9,110 $ 9,544 Insurance $ 3,906 $ 3,754 $ 4,010 Office supplies $ 2,110 $ 2,680 $ 3,862 Salaries $ 62,378 $ 72,924 $ 94,347 Communications $ 3,708 $ 5,507 $ 7,014 Travel $ 1,070 $ 6,310 $ 8,733 Depreciation $ 4,192 $ 4,192 $ 4,192 Operating expenses $ 92,280 $ 110,392 $ 138,472 Operating income $ 260,727 $ 136,138 $ 80,008 Taxes $ 65,182 $ 34,035 $ 20,002 Net income $ 195,545 $ 102,104 $ 60,006 3 Sensitivity analysis measures the impact of changes. What would be percentage effect on net profit b =(J39-73512)/73512 4 What is a pro forma financial statement? Define the term forecasting .
Image of page 143
5 6 The following are weekly sales volume figures. Compute a three week moving average. Round the a Use the TREND function as an ARRAY function to predict weekly sales for the time series and to predi Week Sales volume Moving Avg 1 748 2 660 3 814 4 693 5 572 6 374 7 297 8 418 9 451 10 825 11 770 12 792 13 770 14 660 15 704 16 17 18 7 Differentiate linear and nonlinear relationships. 8 Next, create a second Scatter Diagram using the same data points. Insert a 3rd order polynomial tren Define baseline data . Considering the sales volume data in the previous problem, plot Weeks by Sales volume in a Scatter D
Image of page 144
Image of page 145
shown in the income statement. sume a 6% sales increase over the previous year. Percentage of Sales Sales Bu 2-year average 2015 Projection Q1 Q2 be of a 2% price increase of the COGS purchases)
Image of page 146
average to the nearest whole number. ict sales for weeks 16-18. Round to the nearest whole number. nd line. Does this trend line fit the data better? Diagram. Insert a linear trend line including R 2. Does a straight line fit the data well?
Image of page 147
Image of page 148
Q3 Q4
Image of page 149
Image of page 150
Image of page 151
1 Describe the key components of developing an effective business case. 2 The following represent key elements in developing a business case and show h Year: 1 2 3 4 5 New E-Reader: Sales Forecast ($Millions) Total sales, New E-Reader $8 $14 $18 $28 Cost of Goods Sold @ 50% of sales $4 $7 $9 $14 Incremental Gross Profit, New E-Reader $4 $7 $9 $14 In this case the relevant benefit of the new product is the incremental profit. incremental value $4 $7 $9 $14 $0 The relevant cost factors should be identified and estimated over the timeframe of the case. 3 Relevant costs: Lost value, Current Product $6.00 $6.00 $6.00 $6.00 Advertising $2.00 $1.00 $0.50 $0.50 New product management team $1.00 $1.00 $1.00 $1.00 Market research expenses $0.50 $0.00 $0.00 $0.00 Incremental maintenance $0.00 $0.50 $0.50 $0.50 4 Total costs $9.50 $8.50 $8.00 $8.00 5 Calculate net income. EBITDA ($5.50) ($1.50) $1.00 $6.00 6 Less: Depreciation $1.00 $1.00 $1.00 $1.00
Image of page 152
Income before taxes ($6.50) ($2.50) $0.00 $5.00 7 36% ($2.34) ($0.90) $0.00 $1.80 Net income ($4.16) ($1.60) $0.00 $3.20 New equipment investment $10.00 8 Estimate cash flow Net cash flow ($13.16) ($0.60) $1.00 $4.20 9 Determine the cumulative net cash flow and undiscouted payback period. Cumulative Net Cash Flow ($13.16) ($13.76) ($12.76) ($8.56) Undiscounted payback period Years =G25-(G58/H55) Usually there's a discount rate that needs to be figured into the calculations. Discount Rate 3.0% Discounted Cash Flow $ (12.78) $ (0.57) $ 0.92 $ 3.73 $ - $ (12.78) $ (0.57) $ 0.92 $ 3.73 $ - Cumulative Discounted Cash Flow $ (12.78) $ (13.34) $ (12.43) $ (8.70) $ (8.70) Discounted Payback Period Years 10 Review the fundamentals of using the Solver add-in by viewing the following vid Then implement Solver in the problem on the Unit10b tab.
Image of page 153
how Excel can be used in the development process.
Image of page 154
Image of page 155

You've reached the end of your free preview.

Want to read all 175 pages?

  • Fall '15
  • Threesome

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture