Develop a quarterly sales budget for the following

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 .

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?

1 Enhanced E-Reader: Cash Flow Statement Enhanced E-Reader: Cash Flow Statement
Year: 1 2 3 4 5 6 Year: 1 2 3 4 5 6
Relevant benefits: ($Millions) Relevant benefits: ($Millions)
Incremental value, New E-Reader $4.00 $7.00 $9.00 $14.00 $19.00 $22.00