I. Objective of the Assignment

This assignment presents a method on how to design a spreadsheet for decision support.The decision model will be Cost-Volume-Profit analysis using a Contribution Margin Income Statement and two XY graphs (scatter plots).

II. Procedures to Design a Spreadsheet for Decision Making

1. Identify the problem and/or decision that need to be supported with analysis.
2. Design, formulate or choose a decision model that uses relevant data and information.
3. Identify and collect the input data needed by the decision model to solve the problem and/or make the decision. This will require you to make assumptions about the future such as price, quantity, demand, supply, etc...
4. Construct the spreadsheet with a data input section and an information output section which will calculate the decision information. Input the data and perform the spreadsheet calculation.
5. Review and analyze the information and then make decisions or choices using projections about the future.
6. Implement the decision.
7. Review and evaluate the results of the decision process.

III. Spreadsheet Functions and Techniques Used in this Exercise

1. Cell formatting
2. Absolute vs. relative cell addresses
3. Input and output sections
4. XY graphs (scatter plots)
5. Spreadsheet formulas
6. If statements
7. Sensitivity analysis
8. Spin Buttons

IV. Deliverable and Requirements

Your job is to reproduce the following information (contained in the 5 sections) in an Excel spreadsheet. You will submit an Excel Spreadsheet file that includes the five sections contained in the example spreadsheet. Only Section 1 of the spreadsheet will contain input data.Sections 2, 3, 4, and 5 will contain ALL cell formulas or graphs. Therefore, your task is to figure out how to program the spin buttons, spreadsheet cell formulas, and graphs. When the spreadsheet is complete you should be able to change the data in section 1 with the spin control buttons and the changes will be reflected in the output information in sections 2, 3, 4, and 5.

V. Narrative

Tom James is starting an online dating service and wants to analyze the Cost-Volume-Profit (CVP) relationships. TJ's Love Connection charges clients $20 for each date arranged.Fixed costs for advertising, computer hardware, software, rent, utilities, furniture, Web site, security, and file back-up are $5,000 per month. Variable costs for entering data and finding a proper match for each client are $10 per date. The total income tax rate is 40%. TJ projects that he will arrange 325 dates for the first month of operations. His relevant operating range is 325 to 766 units (dates) per month which will be analyzed in 10% increments. In section 3 - column 1, the starting unit value of 325 is transferred from the input section and each successive value is incrementally increased. Taxes and Profit/Loss are calculated with an if-then formula. The spin control buttons can increase or decrease the various input variables (see Developer/Insert/Form Controls/Spin Button in Excel).

18,000.00 16,000.00 14,000.00 12,000.00 - Sales 10,000.00 V Cost 8,000.00 F Cost T Cost 6,000.00 4,000.00 2,000.00 0.00 Dollars 325.00 357.50 393.25 432.58 475.83 523.42 575.76 633.33 696.67 766.33 UnitsProfit Graph 10,000.00 8,000.00 6,000.00 CM 4,000.00 Opr Inc Dollars 02,000.00 Taxes * Net Inc 0.00 AT 200.00 300.00 400.00 500.00 600.00 700.00 800.00 -2, 000.00 -4, 000.00 UnitsContribution Margin Income Statement 1. Input Data Section Spin Control Buttons: Unit Price $20.00 Price Tax Rate 40 Variable Cost/Unit $10.00 Total Fixed Cost $5,000.00 Var. Cost Quantity Tax Rate 40% Quantity Sold 325 Fix Cost Increment 10 Increment 10% 22. Output Section for Income Statement Unit Quantity 325 Price $20.00 Sales $6,500.00 Variable Cost $3,250.00 Contribution Margin $3,250.00 Fixed Cost $5,000.00 Operating Income -$1,750.00 Taxes $0.00 (Hint: Taxes & Profit/Loss are decided with an If-Then formula) Net Income $1,750.00 3. Output Section for Sensitivity Analysis and XY Graphs X Y1 Y2 Y3 Y4 X Y1 Y2 Y3 Y4 Units Sales V Cost F Cost T Cost Units CM Opr Inc Taxes Net Inc P/L? 325.00 6,500.00 3,250.00 5,000.00 8,250.00 325.00 3,250.00 -1,750.00 0.00 -1,750.00 Loss 357.50 7,150.00 3,575.00 5,000.00 8,575.00 357.50 3,575.00 -1,425.00 0.00 -1,425.00 Loss 393.25 7,865.00 3,932.50 5,000.00 8,932.50 393.25 3,932.50 -1,067.50 0.00 -1,067.50 Loss 432.58 8,651.50 4,325.75 5,000.00 9,325.75 432.58 4,325.75 -674.25 0.00 -674.25 Loss 475.83 9,516.65 4,758.33 5,000.00 9,758.33 475.83 4,758.33 -241.67 0.00 -241.67 Loss 523.42 10,468.32 5,234.16 5,000.00 10,234.16 523.42 5,234.16 234.16 93.66 140.49 Profit 575.76 11,515.15 5,757.57 5,000.00 10,757.57 575.76 5,757.57 757.57 303.03 454.54 Profit 633.33 12,666.66 6,333.33 5,000.00 11,333.33 633.33 6,333.33 1,333.33 533.33 800.00 Profit 696.67 13,933.33 6,966.66 5,000.00 11,966.66 696.67 6,966.66 1,966.66 786.67 1, 180.00 Profit 766.33 15,326.66 7,663.33 5,000.00 12,663.33 766.33 7,663.33 2,663.33 1,065.33 1,598.00 Profit 3Output Section For Income Statement Unit Quantity Price Sales Variable Cost Contribution Margin Fixed Cost Operating Income Taxes 325 20.0 6500.0 32500 3250.0 5000.0 -1750.0 0.0 Sensitivity Analysis 10 Units 325.0 357.5 393.25 432.58 47583 523.42 575.76 633.33 696.67 766.33 Sales 6500.0 7150.0 7865.0 86851.5 9516.65 10468.32 11511.5 12666.66 13933.33 15326.66 Variable Cost 3250.0 3575.0 38325 432575 4758.33 523416 5757.75 6333.33 6966.66 7663.33 Fixed Cost 5000.0 5000.0 5000.0 5000.0 5000.0 5000.0 5000.0 5000.0 5000.0 5000.0 Total Cost 8250.0 750.0 9268.25 9825.75 10516.66 10757.57 11511.5 11633.33 11966.66 12663.33 Contribution Margin 3250.0 3575.0 38325 432575 4758.33 523416 5757.75 6333.33 6966.66 7663.33 Operating Income -1750.0 -1425.0 -1067.5 -674.25 -241.67 234.16 757.57 1333.33 1966.66 2663.33 Taxes 0.0 0.0 0.0 0.0 0.0 93.66 303.03 533.33 786.67 1065.33 Input Data Section Description Value 1 Unit Price 20.0 2 Variable Cost/Unit 10.0 3 Total Fixed Cost 5000.0 4 Tax Rate 40% 5 Quantity Sold 325 6 Increment 10%