Create an excel spreadsheet to calculate fixed and variable cost data for evaluating alternative approaches. Use the results to answer questions about your findings:
American sports have asked you to evaluate two alternative cost approaches for its new web site. It would like you to calculate fixed and variable costs at different numbers of orders. The background data for your analysis appear in exercise 3-33 page 109
American Sports, an outdoor sports retailer, is planning to add a website for online sales. The estimated costs of two alternative approaches are as followings:
When you have completed your spreadsheet, answer the following questions:
1. At what numbers of orders are the total costs for the two approaches the same? What does this mean?
2. Which alternative should be elected if the expected number of orders is less than the break even level of orders? If the expected number of orders is greater than the break even level of orders>
3. What conclusion regarding cost predictions can be drawn from your analysis?
Step by step
1. Open a new excel spreadsheet
2. In column A, create a bold faced heading that contains the following:
Row 1: Chapter 3 Decision Guideline
Row 2: American Sports
Row 3: Analysis of Alternative Cost Approaches
Row 4: Todays Date
3. Merge and Center the four headings rows across columns A-K
4. In row 7, create the following bold faced, right justified column headings:
Column A: Number of Orders
Column B: Alternative 1
Column C: Alternative 2
5. In column A, rows 8-12, enter order levels from 40,000 to 80,000 in 10,000-unit increments
6. Use the Scenario data to create formulas in columns B and C for calculating the total costs (fixed plus variable costs) for each alternative at the order level in column A.
7. Format all amounts as follows:
Number tab: Category:
Decimal places:
Use 1000 separator (.):
8. Modify the page setup by selecting file, page setup,
Page tab: orientation:
Margins tab: Top:
Bottom:
9. Select the data in columns A-C, rows 7-12, and start the Chart Wizard either by inserting a chart (Insert, Chart) or by clicking the Chart Wizard icon on the toolbar
Step 1 of 4- chart type
a. Custom types tab:
b. Chart type: smooth lines
c. Click "Next>" button
Step 2
d. Data Range tab:
e. Modify data range to =SheetName!$B$7:ScS12
f. Series in: columns
g. Series tab:
h. Category (X) axis labels = SheetName!$A$8:$A$12
i. Click "next>" button
Step 3
j. Titles tab:
k. Chart title: Analysis of Alternative Cost Approaches
l. Category (X) axis: Major Gridlines (Checked)
m. Value (Y) axis: Major Gridlines (Checked)
n. Gridlines tab:
o. Category X axis: major gridlines (Checked)
p. Value (Y) axis: major gridlines (Checked)
q. Click next button
Step 4
r. As object in Sheet Name checked
s. Click finish button
10. Move the chart so the upper left corner is on the left margin, row 14. Left click the upper left handle and drag it to the designated location
11. Resize the chart so the lower right corner fills cell K37. Left click the lower right handle and drag it to the designated location
12. Format the Y-axis amounts (Total cost) to display dollar symbol by doing the following: double click any cost amount on the Y-axis to open the "format axis" dialog box.
Scale tab: minimum: 300,000
Number tab: category: currency
Decimal places 0
Symbol: $