Assignment

RapidBus is a commuter bus company.On regional route A, RapidBus currently charges $10 per passenger (fare).Monthly revenue for this route is $108,000.Currently variable cost is $5 per passenger and fixed cost is $45,000 per month. (complete the calculations for a month)

Required:

1. Enter this information into the "Data Table" in your Excel2Template (Hint you will need to calculate the number of passengers).

2. Set up formulas to calculate, Revenue, Cost, & Income

3. Use formulas to complete the contribution margin income statement.

4. Use scenario manager to save this original data that you added to the Data Table.[name this scenario: Original] Hint: only save the four variables in the data table.If you put a formula in any of these cells, allow scenario manager to change these values to constants.

5. Use Goal Seek to find out how many passengers are necessary to breakeven. (Hint: you will need to set income to 0)

6. Use scenario manager to save this breakeven information [name this scenario: BE Original] *note if passengers are not whole round up in all scenarios

7. Evaluate the following changes and report their affects on income. (Each scenario is independent, so return to the original scenario before making changes)

a. Fare decreases to $9, but passengers increase by 10% (use scenario manager to save this as: Scenario One)

b. Fixed cost decrease by $15,000 and the variable rate per passenger increases to $5.50 (use scenario manager to save this as: Scenario Two)

c. Fixed cost increases by $15,000 and the variable rate per passenger decreases to $3.50 (use scenario manager to save this as: Scenario Three)

8. Use goal seek to find out how many passengers are necessary to breakeven under scenario two (use scenario manager to save this as: BE Scenario Two )

9. Use goal seek to find out how many passengers are necessary to breakeven under scenario three (use scenario manager to save this as: BE Scenario Three)

10.Evaluate passenger rates on scenario two

a. Using scenario two change passengers to 20,000 (use scenario manager to save this as: 20,000 Scenario Two)

b. Using scenario two change passengers to 10,000 (use scenario manager to save this as: 10,000 Scenario Two)

11. Evaluate passenger rates on scenario three

a. Using scenario three change passengers to 20,000 (use scenario manager to save this as: 20,000 Scenario Three)

b. Using scenario Three change passengers to 10,000 (use scenario manager to save this as: 10,000 Scenario Three)

12. Create scenario summary, include revenue, cost, and income in the results (set result cells as the cells that contain this information)

13. Set up the scenario summary table to print on one page.

14. Create table (using the data in the scenario summary table) that highlights the tradeoffs between fixed and variable cost (cost structure).Hint:you will need to use the passenger combinations of scenario two and three.Note you do not need to pivot table or a data table, just a simple table that illustrates the tradeoffs.

ACC 3400: Summer 2017 Excel project 2: 30 points The purpose of this assignment is to provide an introduction to Excel's what-if analysis. In cost accounting determining the relationships between cost, volume and profit are very important. This assignment will have you use scenarios and goal seek to calculate breakeven and changes in cost and volume. Introduction to what-if analysis By using what-if analysis tools in Microsoft Office Excel, you can use several different sets of values in one or more formulas to explore all the various results. For example, you can do what-if analysis to build two budgets that each assumes a certain level of revenue. Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result. Excel provides several different tools to help you perform the type of analysis that fits your needs. Overview What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of what-if analysis tools come with Excel: scenarios, data tables, and Goal Seek. Scenarios and data tables take sets of input values and determine possible results. A data table works only with one or two variables, but it can accept many different values for those variables. A scenario can have multiple variables, but it can accommodate only up to 32 values. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result. Use scenarios to consider many different variables A scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. For example, suppose you have two budget scenarios: a worstu case and a best case. You can use the Scenario Manager to create both scenarios on the same worksheet, and then switch between them. For each scenario, you specify the cells that change and the values to use for that scenario. When you switch between scenarios, the result cell changes to reflect the different changing cell values. Worst case scenario Changing cells Result cell Best case scenario Changing cells Result cell After you have created all the scenarios that you need, you can create a scenario summary report that incorporates information from those scenarios. A scenario report displays all the scenario information in one table on a new worksheet. Scenario summary report NOTE Scenario reports are not automatically recalculated. If you change the values of a scenario, those changes will not show up in an existing summary report. Instead, you must create a new summary report. Create a scenario Before you create a scenario, you should have an initial set of values already on the worksheet. To make scenario summary reports easier to read, you should also consider naming the cells that you plan to use in scenarios. 1. 2. 3. 4. 5. 6. 7. 8. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager. Click Add. In the Scenario name box, type a name for the scenario. In the Changing cells box, enter the references for the cells that you want to specify in your scenario. For example, if you want to see how changing the values of cells B1 and B2 will affect the outcome of a formula based on those cells, enter B1,B2. NOTE To preserve the initial values for the changing cells, add a scenario that uses those values before you create additional scenarios that use different values. Click OK. In the Scenario Values dialog box, type the values that you want to use in the changing cells for this scenario. To create the scenario, click OK. If you want to create additional scenarios, repeat steps 2 through 8. After you finish creating scenarios, click OK, and then click Close in the Scenario Manager dialog box. Display a scenario When you display a scenario, you switch to the set of values that are saved as part of that scenario. The scenario values are displayed in the cells that change from scenario to scenario, in addition to the results cells. For example, using the preceding scenarios, if you display the Best Case scenario, cell B1 displays 150000, cell B2 displays 26000, and cell B3 displays 124000. 1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager. 2. Click the name of the scenario that you want to display. 3. Click Show. NOTE After you close the Scenario Manager dialog box, the values from the last scenario that you displayed remain on the worksheet. If you saved your initial values as a scenario, you can display those values before you close the Scenario Manager dialog box. Create a scenario summary report 1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager. 2. Click Summary. 3. Click Scenario summary or Scenario PivotTable report. 4. In the Result cells box, enter the references for the cells that refer to cells whose values are changed by the scenarios. Separate multiple references with commas. Use Goal Seek to find out how to get a desired result If you know the result that you want from a formula, but you are not sure what input value the formula requires to get that result, you can use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long a period you want in which to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you must secure in order to meet your loan goal. NOTE Goal Seek works with only one variable input value. If you want to determine more than one input value, for example, the loan amount and the monthly payment amount for a loan, you should instead use the Solver add-in. Let's look at the example, step-by-step. Because you want to calculate the loan interest rate needed to meet your goal, you use the PMT function. The PMT function calculates a monthly payment amount. In this example, the monthly payment amount is the goal that you seek. PREPARE THE WORKSHEET 1. Open a new, blank worksheet. 2. First, add some labels in the first column to make it easier to read the worksheet. 1. In cell A1, type Loan Amount. 2. In cell A2, type Term in Months. 3. In cell A3, type Interest Rate. 4. In cell A4, type Payment. 3. Next, add the values that you know. 1. In cell B1, type 100000. This is the amount that you want to borrow. 2. In cell B2, type 180. This is the number of months that you want to pay off the loan. NOTE Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula. Instead, you add the formula to the worksheet and specify the payment value at a later step, when you use Goal Seek. 4. Next, add the formula for which you have a goal. For the example, use the PMT function: In cell B4, type =PMT(B3/12,B2,B1). This formula calculates the payment amount. In this example, you want to pay $900 each month. You don't enter that amount here, because you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you start with a formula. The formula refers to cells B1 and B2, which contain values that you specified in preceding steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate. Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the example, returns a payment of $555.56. You can ignore that value for now. 5. Finally, format the target cell (B3) so that it displays the result as a percentage. 1. On the Home tab, in the Number group, click Percentage. 2. Click Increase Decimal or Decrease Decimal to set the number of decimal places. USE GOAL SEEK TO DETERMINE THE INTEREST RATE 1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek. 2. In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4. 3. In the To value box, type the formula result that you want. In the example, this is -900. Note that this number is negative because it represents a payment. 4. In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3. 1. NOTE The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box. 5. Click OK. Goal Seek runs and produces a result, as shown in the following illustration. Assignment RapidBus is a commuter bus company. On regional route A, RapidBus currently charges $10 per passenger (fare). Monthly revenue for this route is $108,000. Currently variable cost is $5 per passenger and fixed cost is $45,000 per month. (complete the calculations for a month) Required: 1. Enter this information into the \"Data Table\" in your Excel2Template (Hint you will need to calculate the number of passengers). 2. Set up formulas to calculate, Revenue, Cost, & Income 3. Use formulas to complete the contribution margin income statement. 4. Use scenario manager to save this original data that you added to the Data Table. [name this scenario: Original] Hint: only save the four variables in the data table. If you put a formula in any of these cells, allow scenario manager to change these values to constants. 5. Use Goal Seek to find out how many passengers are necessary to breakeven. (Hint: you will need to set income to 0) 6. Use scenario manager to save this breakeven information [name this scenario: BE Original] *note if passengers are not whole round up in all scenarios 7. Evaluate the following changes and report their affects on income. (Each scenario is independent, so return to the original scenario before making changes) a. Fare decreases to $9, but passengers increase by 10% (use scenario manager to save this as: Scenario One) b. Fixed cost decrease by $15,000 and the variable rate per passenger increases to $5.50 (use scenario manager to save this as: Scenario Two) c. Fixed cost increases by $15,000 and the variable rate per passenger decreases to $3.50 (use scenario manager to save this as: Scenario Three) 8. Use goal seek to find out how many passengers are necessary to breakeven under scenario two (use scenario manager to save this as: BE Scenario Two ) 9. Use goal seek to find out how many passengers are necessary to breakeven under scenario three (use scenario manager to save this as: BE Scenario Three) 10. Evaluate passenger rates on scenario two a. Using scenario two change passengers to 20,000 (use scenario manager to save this as: 20,000 Scenario Two) b. Using scenario two change passengers to 10,000 (use scenario manager to save this as: 10,000 Scenario Two) 11. Evaluate passenger rates on scenario three a. Using scenario three change passengers to 20,000 (use scenario manager to save this as: 20,000 Scenario Three) b. Using scenario Three change passengers to 10,000 (use scenario manager to save this as: 10,000 Scenario Three) 12. Create scenario summary, include revenue, cost, and income in the results (set result cells as the cells that contain this information) 13. Set up the scenario summary table to print on one page. 14. Create a table (using the data in the scenario summary table) that highlights the tradeoffs between fixed and variable cost (cost structure). Hint: you will need to use the passenger combinations of scenario two and three. Note you do not need to create a pivot table or a data table, just a simple table that illustrates the tradeoffs. 15. Save your Excel file in a manner that contains your name (example: Excel2_lmpleis.xls) 16. Submit your Excel file through Blackboard Please remember that the work you submit is expected to be your own and created during this semester. Please review the syllabus and/or contact me if you have any questions. Scenario Summary Current Values: Original BE original Changing Cells: Toll 10 10 10 Variable_Rate 5 5 5 Passengers 108,000.00 10,800.00 9,000.00 Fixed_Cost $45,000.00 $45,000.00 $45,000.00 Result Cells: Revenue $1,080,000.00 $108,000.00 $90,000.00 Cost $585,000.00 $99,000.00 $90,000.00 Income $495,000.00 $9,000.00 $0.00 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Scenario 1 9 5 11,880.00 $45,000.00 $106,920.00 $104,400.00 $2,520.00 Scenario 2 Scenario 3 BE scenario 2 BE scenario 3 20000 scenario 2 10000 scenario 2 10 5.5 10,800.00 $30,000.00 10 3.5 10,800.00 $60,000.00 10 5.5 6,666.67 $30,000.00 10 3.5 9,230.77 $60,000.00 10 5.5 20,000.00 $30,000.00 10 5.5 10,000.00 $30,000.00 $108,000.00 $89,400.00 $18,600.00 $108,000.00 $97,800.00 $10,200.00 $66,666.67 $66,666.67 $0.00 $92,307.69 $92,307.69 $0.00 $200,000.00 $140,000.00 $60,000.00 $100,000.00 $85,000.00 $15,000.00 20000 scenario 3 10000 scenario 3 10 3.5 20,000.00 $60,000.00 10 3.5 10,000.00 $60,000.00 $200,000.00 $130,000.00 $70,000.00 $100,000.00 $95,000.00 $5,000.00 DATA TABLE Revenue per passenger Variable Cost Per Passenger No.of. Passengers Fixed Costs 10 5 10,800.00 Working $45,000.00 Revenue Cost Income $108,000.00 $99,000.00 $9,000.00 Statement of Income Revenue Less: Variable Cost Contribution Less: Fixed Cost Income $108,000.00 $54,000.00 $54,000.00 $45,000.00 $9,000.00 10800