Martinakom

Not Rated (0)

$12/per page/Negotiable

About Martinakom

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:
Accounting,Applied Sciences See all
Accounting,Applied Sciences,Architecture and Design,Biology,Business & Finance,Calculus,Chemistry,Computer Science,Geology Hide all
Teaching Since: Jul 2017
Last Sign in: 398 Weeks Ago, 4 Days Ago
Questions Answered: 5023
Tutorials Posted: 5024
Category > Business & Finance Posted 14 Aug 2017 My Price 7.00

ACC 3400: Summer 2017 Excel project 2: In cost accounting determining the relationships between cost, volume and profit are very important.

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.  

Attachments:

Answers

Martinakom
Not Rated (0)
Status NEW Posted 14 Aug 2017 04:08 PM My Price 7.00

Hel-----------lo -----------Sir-----------/Ma-----------dam----------- Â-----------  -----------Tha-----------nk -----------you----------- fo-----------r y-----------our----------- in-----------ter-----------est----------- an-----------d b-----------uyi-----------ng -----------my -----------pos-----------ted----------- so-----------lut-----------ion-----------. P-----------lea-----------se -----------pin-----------g m-----------e o-----------n c-----------hat----------- I -----------am -----------onl-----------ine----------- or----------- in-----------box----------- me----------- a -----------mes-----------sag-----------e I----------- wi-----------ll -----------be -----------qui-----------ckl-----------y

Not Rated(0)