Task 4 The following are instructions for creating breakeven point analysis models in Excel This section will introduce how to create breakeven point (BEP) analysis models. 1. Open the assignment Excel

workbook to the worksheet "Task 4_BEP Intro" 2. Read the "Instructions." The instructions provide you an overview of the components required in the task. 3. Review the Problem Statement and Table provided in the "Variables" section. 4. Complete the following steps Model formulation steps in the "Model" section. Model Formulation Steps 5. Data Table a. Populate the Data Table using the decision variables provided in Table 1. 6. Results Table a. BEP Units: Excel Formula =Fixed Cost/(Revenue - Variable Cost) b. BEP Revenue: Excel Formula = Revenue * BEP Units c. Volume Analysis units: Excel Formula = cell reference Volume in the Data Table d. Costs: Excel Formula: = Fixed Cost + Variable Cost * Volume Analysis units e. Revenue: Excel Formula: = Revenue * Volume Analysis units f. Profit: Excel Formula: = Revenue - Costs Results Table Format Results Table BEP units BEP Revenue Volume Analysis units Costs Revenue Profit $ 750.00 20 510.00 300.00 (210.00) 7. Graphing the BEP Cost-Volume Analysis a. Create a Graph Table to support chart construction. i. X-axis iii. Revenue 1. First row Excel Formula: = 0 2. Second row Excel Formula: Cell reference BEP Units value in the Results table. 3. Third row Excel Formula: =2*Cell reference BEP Units value in the Results table. ii. Costs 1. All rows Excel Formula: = Fixed Cost +Variable Cost * X-value 1. All rows Excel Formula: = Revenue * X-value/nGraph Table Graph Table 7.a.i.2 Revenue (S) x $400 $200 $ Figure 1 Cost-Volume Analysis $1,600 $1,400 $1,200 $1,000 $800 $600 50 $ 0 Costs Figure 1 Example of Breakeven Point Cost-Volume 750.00 $ 7.a.ii Result Statements b. Select the cells in the graph table (do not include the line with X and Ys). c. Insert >> Charts >> Line d. Place the chart directly below the blue highlighted chart here cell. e. Size chart to 4 x 5 inches f. Format your chart to match the Figure 1 Y Revenue 50 Units 750.00 7.a.iii Costs Revenue 100 8. Interpreting the results a. The BEP units and revenue indicate the volume and expected revenue at the breakeven point (point where costs equal revenue). b. The volume analysis permits the evaluation of project volumes to determine profitability. 9. Answer the question(s) in this section. a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height can be used to ensure the entire answer is displayed. 10. Task 4 is complete.