Home / Questions / RapidBus is a commuter bus company On regional route A RapidBus currently charges $10 per ...
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 $550 (use scenario manager to save this as: Scenario Two)
c Fixed cost increases by $15,000 and the variable rate per passenger decreases to $350 (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
Mar 24 2020 View more View Less
The income statement shows net sales which is equal toa.earnings before taxes minus taxes.b.gross revenues minus returns and allowances.c.net sales minus cost of goods so...
Aug 10 2020apply your understanding of stakeholder orientation and/or systems theory to outline what you believe are the most important issues relating to firms’ collection, storage...
Feb 07 2020An electric iron for smoothing clothes was being used when there was a loud bang and a flash of flame from the electric flex next to the iron. An inspection showed that...
May 16 2020A Plastic manufacturing company is considering replacing an older machine which was fully depriciated for tax purpose with a new machine costing $40,000 The new machine w...
May 29 2020MULTIPLE CHOICE QUESTIONS1.Power is distributed within a police organizationa.evenlyb.proportionallyc.by rankd. by merit2.One of the shortcomings of many of the defini...
Jan 11 2020Select a value of Rx in Figure P2−71 so that vL = 2 V. Repeat for 4 V and 6 V. Caution: Rx must be positive. >
Jul 28 2020Can you think of a good or service for which demand has changed due to the pandemic? Which determinant of demand would have caused this change? Recall that the determinan...
Apr 08 2021Carl puts 10,000 into a bank account that pays an effective annual interest rate of 4% for ten years, with interest credited at the end of each year. If a withdrawal is m...
Apr 13 2020Describing the five steps in the process of communication. As you describe each step, illustrate it with an example. (Learning Objective 1)
Aug 23 2020Choose ONE example of an organisation or issue you are familiar with to explore: In what ways is gender relevant for work and organisation in the 21st century? You may ch...
May 25 2020