Develop a quarterly sales budget for the following year Percentage of Sales

Develop a quarterly sales budget for the following

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

Develop a quarterly sales budget for the following year. Percentage of Sales Sales Budget Income Statement for the year ended: 2012 2013 2014 2-year average 2015 Projection Q1 Q2 Q3 Q4 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 be of a 2% price increase of the COGS purchases) =(J39-73512)/73512 4 5 6 The 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. 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. What is a pro forma financial statement? Define the term forecasting . Define baseline data .
Image of page 32
8 Next, 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 R 2. Does a straight line fit the data well?
Image of page 33
Image of page 34
Image of page 35
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
Image of page 36
Image of page 37

You've reached the end of your free preview.

Want to read all 44 pages?

  • Fall '15
  • Threesome

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture