7 Use Excels regression tool to perfrom a regression analysis for the budged

# 7 use excels regression tool to perfrom a regression

This preview shows page 151 - 161 out of 163 pages.

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