7
Use Excel's regression tool to perfrom a regression analysis for the budged and sales data in the
Place the results In B97.
Create an X-Y Scatter Diagram (Scatterplot) for the data.
Insert a trend line.
Also include the re
Advertising
Budget

8
Using the TREND function as an ARRAY function, for the data in the above item predict the unit
$200,000
$210,000
9
Use LINEST to perform a regression analysis on the budget and unit data above.
Treat unit sales
10
The following adds additional data (Sales Price) to the dataset the previous problems used.
Treating Advertising Budget and Sales Price, use the Regression tool in Data Analysisto conduct
Specify F137 as the Output Range.
Sales Price
Unit Sales
$3,500
$88
16,523
$10,073
$110
6,305
$11,825
$85
1,769
$33,550
$28
30,570
$37,200
$101
7,698
$55,400
$71
9,554
$55,565
$7
54,154
$66,501
$82
54,450
$71,000
$62
47,800
$82,107
$24
74,598
$83,100
$91
25,257
$90,496
$40
80,608
$100,000
$45
40,800
$102,100
$21
63,200
$132,222
$40
69,675
Advertising
Budget

$136,297
$8
98,715
$139,114
$63
75,886
$165,575
$5
83,360
11
Are both predictors statistically signinicant?
A=Yes; B=No.
12
What is the Multiple Correlation?
13
Is the Multiple Correlation statistically significant?
A=Yes; B=No.

field.

Upper bound
Lower bound
CONFIDENCE.T
Upper bound
Lower bound
us example, can it be said that the budgeted dollars caused the sales?
e above dataset.
Treat the advertising budget as the independent variable.
egression equation and the R
2
on the chart.

t sales for the following advertising budget dollar amounts.
s as the dependent variable.
a regression analysis with Unit Sales as the dependent variable.

1
What is the contribution margin?
2
For the scenario detailed below, compute total sales, total variable costs, contribution margin, and cont
Unit Sales Price:
$11.25
USP
Increase
Number Sold:
1250
$
11.25
1.00
Sales: 1250 DVDs @ $11.25:
Less: Variable costs associated with production:
Employee costs (1000 DVDs @ $0.50):
$625
Materials costs
(1000 DVDs @ $5):
$6,250
Packaging costs (1000 DVDs @ $1):
$1,250
Total variable costs:
Contribution margin:
Contribution margin ratio:
3
What is unit contribution?

4
Calculate the unit contribution from the data presented in the previous scenario.
5
Define breakeven analysis.
6
What is the breakeven point?
7
For the following data, calculate the breakeven point in sales and in units.
Volume
Fixed
Variable
Total
Total
(units)
Costs
Costs
Costs
Sales
1
$50
$15
$65
$20
2
$50
$30
$80
$40
3
$50
$45
$95
$60
4
$50
$60
$110
$80
5
$50
$75
$125
$100
6
$50
$90
$140
$120
7
$50
$105
$155
$140
8
$50
$120
$170
$160
9
$50
$135
$185
$180
10
$50
$150
$200
$200
11
$50
$165
$215
$220
12
$50
$180
$230
$240
13
$50
$195
$245
$260
14
$50
$210
$260
$280
15
$50
$225
$275
$300
16
$50
$240
$290
$320

17
$50
$255
$305
$340
18
$50
$270
$320
$360
19
$50
$285
$335
$380
8
For the data in the above example, create a graph showing the breakeven point.
9
Which of the following is not an assumption made in contribution analysis?
A - Revenues and expenses are linear acroll the relevant range of volume.
B - Costs can be accurately allocated fixed and variable cost categories.
C - Sales mix is constant.
That is from one period to the next, total sales are based on the same percent o
D - Worker productivity is constant.

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

Want to read all 163 pages?

- Fall '15
- Threesome