Compute the Current Ratio on the following Current Assets 1400815 Current

Compute the current ratio on the following current

This preview shows page 32 - 37 out of 43 pages.

Compute the Current Ratio on the following:Current Assets$ 1,400,815 Current Liabilities$ 693,849 Current Ratio24Compute the Quick Ratio on the following:Current Assets$ 1,400,815 Inventory$ 666,748 Current Liabilities$ 693,849 Quick Ratio
Background image
12Using the two most recent years shown create a two-year percentage of sales forecast for the items shown in the income statement.Use the 2-year percentage of sales forecast to develop a pro forma budget for the following year. Assume a 6% sales increase over the previous year.Develop a quarterly sales budget for the following year.Percentage of SalesSales BudgetIncome Statement for the year ended:2012201320142-year average2015 ProjectionQ1Q2Q3Q4Sales$ 498,541 $ 367,450 $ 389,864 Cost of Goods SoldInventory, 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 ExpensesAdvertising$ 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 3Sensitivity analysis measures the impact of changes. What would be percentage effect on net profit be of a 2% price increase of the COGS purchases)=(J39-73512)/73512456The following are weekly sales volume figures. Compute a three week moving average. Round the average to the nearest whole number.Use the TREND function as an ARRAY function to predict weekly sales for the time series and to predict sales for weeks 16-18. Round to the nearest whole number.WeekSales volumeMoving Avg1748266038144693557263747297841894511082511770127921377014660157041617187Differentiate linear and nonlinear relationships. What is a pro formafinancial statement?Define the term forecasting.Define baseline data.
Background image
8Next, create a second Scatter Diagram using the same data points. Insert a 3rd order polynomial trend line. Does this trend line fit the data better?Considering the sales volume data in the previous problem, plot Weeks by Sales volume in a Scatter Diagram. Insert a linear trend line including R2. Does a straight line fit the data well?
Background image
1Describe the key components of developing an effective business case.2The following represent key elements in developing a business case and show how Excel can be used in the development process.Year:123456New 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 $0 The relevant cost factors should be identified and estimated over the timeframe of the case.3Relevant 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 4Total costs$9.50 $8.50 $8.00 $8.00 5Calculate net income.EBITDA($5.50)($1.50)$1.00 $6.00 6Less: Depreciation$1.00 $1.00 $1.00 $1.00 Income before taxes($6.50)($2.50)$0.00 $5.00 736%($2.34)($0.90)$0.00 $1.80 Net income($4.16)($1.60)$0.00 $3.20 New equipment investment$10.00 8Estimate cash flowNet cash flow($13.16)($0.60)$1.00 $4.20 9Determine the cumulative net cash flow and undiscouted payback period.Cumulative Net Cash Flow($13.16)($13.76)($12.76)($8.56)Undiscounted payback periodYears=G25-(G58/H55)Usually there's a discount rate that needs to be figured into the calculations.Discount Rate3.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) $ (8.70)Discounted Payback PeriodYears10Review the fundamentals of using the Solver add-in by viewing the following video: Then implement Solver in the problem on the Unit10b tab.
Background image
1Enhanced E-Reader: Cash Flow StatementEnhanced E-Reader: Cash Flow StatementYear:123456Year:123456Relevant benefits:($Millions)Relevant benefits:($Millions)Incremental value, New E-Reader$4.00 $7.00 $9.00 $14.00 $19.00 $22.00 Incremental value, New E-Reader$4.00 $7.00 $9.00 $14.00 $19.00 $22.00 Relevant costs:Relevant costs:Lost value, Current Product$6.00 $6.00 $6.00 $6.00 $6.00 $7.00 Lost value, Current Product$6.00 $6.00 $6.00 $6.00 $6.00 $7.00 ConstraintsAdvertising$2.00 $1.00 $0.50 $0.50 $0.50 $0.50 Advertising$2.00 $1.00 $0.50 $0.50 $0.50 $0.50 New product management$1.00 $1.00 $1.00 $1.00 $1.00 $1.00 New product management$1.00 $1.00 $1.00 $1.00 $1.00 $1.00 Market research expenses$0.50 $0.00 $0.00 $0.00 $0.00 $0.00 Market research expenses$0.50 $0.00 $0.00 $0.00 $0.00 $0.00 Incremental maintenance$0.00 $0.50 $0.50 $0.50 $0.50 $0.50 Incremental maintenance$0.00 $0.50 $0.50 $0.50 $0.50 $0.50 Total costs$9.50 $8.50 $8.00 $8.00 $8.00 $9.00 Total costs$9.50 $8.50 $8.00 $8.00 $8.00 $9.00 EBITDA($5.50)($1.50)$1.00 $6.00 $11.00 $13.00 EBITDA($5.50)($1.50)$1.00 $6.00 $11.00 $13.00 Less: Depreciation$1.00 $1.00 $1.00 $1.00 $1.00 $1.00 Less: Depreciation$1.00 $1.00 $1.00 $1.00 $1.00 $1.00 Income before taxes($6.50)($2.50)$0.00 $5.00 $10.00 $12.00 Income before taxes($6.50)($2.50)$0.00 $5.00 $10.00 $12.00 Less: Taxes @ 36%($2.34)($0.90)$0.00 $1.80 $3.60 $4.32 Less: Taxes @ 36%($2.34)($0.90)$0.00 $1.80 $3.60 $4.32 Net income($4.16)($1.60)$0.00 $3.20 $6.40 $7.68 Net income($4.16)($1.60)$0.00 $3.20 $6.40 $7.68 Plus: Depreciation$1.00 $1.00 $1.00 $1.00 $1.00 $1.00 Plus: Depreciation$1.00 $1.00 $1.00 $1.00 $1.00 $1.00 Minus: Investment$10.00 $0.00 $0.00 $0.00 $0.00 $0.00 Minus: Investment$10.00 $0.00 $0.00 $0.00 $0.00 $0.00 Net Cash Flow($13.16)($0.60)$1.00 $4.20 $7.40 $8.68 Net Cash Flow($13.16)($0.60)$1.00 $4.20 $7.40 $8.68 Cumulative Net Cash Flow($13.16)($13.76)($12.76)($8.56)($1.16)$7.52 Cumulative Net Cash Flow($13.16)($13.76)($12.76)($8.56)($1.16)$7.52 Undiscounted payback period:5.13Undiscounted payback period:5.13Discount Rate:0.1Discount Rate:0.1Discounted Cash Flow($11.96)($0.50)$0.75 $2.87 $4.59 $4.90 Discounted Cash Flow($11.96)($0.50)$0.75 $2.87 $4.59 $4.90 Cumulative Discounted Cash Flow($11.96)($12.46)($11.71)($8.84)($4.24)$0.65 Cumulative Discounted Cash FlowErr:504Err:504Err:504Err:504Err:504Err:504Discounted payback period:5.87Discounted payback period:5.87($11.96)($12.46)($11.71)($8.84)($4.24)$0.65 ($11.96)($12.46)($11.71)($8.84)($4.24)$0.65 2
Background image
Image of page 37

You've reached the end of your free preview.

Want to read all 43 pages?

  • Fall '15
  • Threesome

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture