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 .
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
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)
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?
Q3 Q4
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
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.
how Excel can be used in the development process.

#### You've reached the end of your free preview.

Want to read all 175 pages?

• Fall '15
• Threesome