Question

In: Finance

CS&E 1111                                      PRELab 7: spreadshe

CS&E 1111                                      PRELab 7: spreadsheet design

Purpose:

The purpose of this lab is to expand your problem solving and spreadsheet design skills by performing a task that requires solving a complex problem. As part of this lab, you will be asked to design your own workbook solution. There is no one right way to do this; however some ways make it easier than others to update information and create multiple cases. Correct spreadsheet design will facilitate this type of common business analysis. Frequently, information will change or you will need to explore several possible scenarios.

Go to Carmen click on Modules then click on McGraw –Hill campus then click on McGraw-Hill new window then click on Simnet Grader. Starting files have green headers.

Select Prelab Prelab 7 Columbus Air as a Download resource. Be sure to Save and Enable Editing. It will be a blank workbook but you must download the the blank workbook in order to modify and upload it to Simnet. When you have completed the Columbus Air file you will Upload and Save it then you will Submit-all using Simnet. You must use the Download resource file and not another version to complete the Lab.

There is no SIMnet SIMpath assignment this week just the Resources Columbus Air portion.

Lab 7: Pre-Lab Assignment –complete before coming to lab:

spreadsheet design – columbus air problem

You are the owner of a small airline company called Columbus Air, which operates three airplanes on three different routes (Cancun, Los Angeles, and New York). As the owner, you are preparing a profit analysis for your company. Use the information that follows to setup the Columbus Air workbook solution for calculating profit. Be sure to read the entire data and calculation sections PRIOR to attempting this problem Remember that an extra 10 minutes spent planning your design could save you hours in execution and what-if analyses later!

Data Section

Information has been gathered from the previous year about your expenses and your revenues. ( Hint: think about cell references) These values are as follows:

  • Employee wages
    • You have 3 pilots, who are each paid wages of $90,000 a year. There is 1 pilot per route.
    • You have 15 union employees, who are paid wages of $45,000 a year. There are 6 employees each assigned to the Los Angeles and Cancun routes, and 3 assigned to the New York route.
  • Operating Costs
    • Maintenance expenses: Each plane is required to have one major maintenance session a year which costs $100,000 per plane.
    • Fuel costs: Last year you bought jet fuel at an average of $10 per gallon. Fuel consumption was prorated based on the miles per route and the number of trips made.
      • Total annual fuel consumption on the Cancun route was 700,000 gallons
      • Total annual fuel consumption on the Los Angeles route was 1,370,000 gallons
      • Total annual fuel consumption on the New York route was 40,000 gallons.
  • Airfare revenues
    • A ticket to New York costs $300 roundtrip, and you sold 30,000 tickets last year.
    • A ticket to Los Angeles costs $820 rountrip, and you sold 17,500 tickets last year.
    • A ticket to Cancun costs $1,300 rountrip and you sold 7,500 tickets last year

Calculating previous year’s profit

Using the data given, calculate your annual profits for the past year for each route and your total overall profit. This will involve calculating total revenues, expenses, and taxes as outlined below. Such information will enable you, as the owner, to make informed decisions about your company. Make sure to design your workbook so that anyone using it can easily change numbers to perform What-if Analyses. Please explicitly list all inputs so they are easy to identify and update and use cell references whenever possible in your formulas. Failure to do so will result in loss of credit, even if the numeric values are correct.

  • Use the data that you have been given to calculate your total revenues for each route.
  • Calculate expenses by cost component (wages and operating costs) for each route. These may later individually vary, so be sure to list each separately.
  • Calculate the net profit for each route. This is the amount of money the airline made, revenues less expenses.
  • Calculate revenues, and expenses by category for all 3 routes combined. Then calculate the net profit for all 3 routes combined (Hint: Net profits before taxes are $ 10,755,000)
  • Calculate taxes based on the net profit for all three routes. You do not need to calculate this value per route. Tax rates are determined based upon your net profit. The tax owed is calculated as the applicable tax rate times the net profit. Make a table on a new worksheet to contain the following tax rates:
    • If your company makes a net profit of less than $1,000,000 you are taxed at 15% ,
    • If your company makes a net profit of at least $1,000,000 but less than $5,000,000 you are taxed at 20%,
    • If your company makes a net profit of $5,000,000 or more you are taxed at 25%.
  • After you make the new worksheet, use a lookup function to calculate the amount you have paid in taxes and your total profits after-taxes.
  • Save and close the workbook. Then Upload and Save it to Simnet under Resources Prelab 7 Columbus Air and then Submit it. You will get 2 attempts to submit your file. Be sure to use only the file you downloaded from Simnet and made changes to, that you submit. You must submit your file to Simnet to get credit for Prelab. It is your responsibility to check that your file has been submitted.

Solutions

Expert Solution

AMOUNT ($)
Particulars New York Route Los Angeles Route Cancun Route
Revenue
(No of Tickets Sold* Ticket Price)
                    90,00,000                       1,43,50,000                        97,50,000
Less Expense:
Fuel                      -4,00,000                      -1,37,00,000                       -70,00,000
Wages of Pilot                         -90,000                               -90,000                            -90,000
Union employee wages                      -1,35,000                            -2,70,000                         -2,70,000
Maintenance cost                      -1,00,000                            -1,00,000                         -1,00,000
Total expense                     -7,25,000                      -1,41,60,000                      -74,60,000
Profit                     82,75,000                             1,90,000                        22,90,000

The net profit for combined is 8275000+190000+290000 = 10755000

Total Profits 10755000
Tax @25% 2688750
After tax profits 8066250

Related Solutions

Prelab 7: DC Circuits 1 What are the units of Resistance? Watch the video tutorial about...
Prelab 7: DC Circuits 1 What are the units of Resistance? Watch the video tutorial about series and parallel circuits. Explain what ammeters and voltmeters do – Why are they hooked up to the circuit differently, and why don't they affect the circuit? What is the resistance of a resistor when the colored bands read yellow, red, green and gold ? What is a thermistor? Three resistors with values of 1,488 Ohms, 1,101 Ohms and 860 Ohms are in parallel...
Model a BCD to 7-Segment Decoder (1111 1000) Create a top-level Verilog module, named bcdto7segment_dataflow with...
Model a BCD to 7-Segment Decoder (1111 1000) Create a top-level Verilog module, named bcdto7segment_dataflow with 4-bit data input (x[3:0]), anode enable output signals (an[3:0]), and 7-bit output (seg[6:0]) using dataflow modeling (Hint: You will have to derive seven expressions for the 7 segments on paper). Assign appropriate logic to an[3:0] in the model so you can display only on the right most display.
Discussion 3 Solve and write an essay. 1111 réponses non lues.1111 réponses. Essay: 2 [ x...
Discussion 3 Solve and write an essay. 1111 réponses non lues.1111 réponses. Essay: 2 [ x - (4 + 2x) + 3 ] - 2x - 2 = 4 [2x - (3 - x) + 5] + 6x + 28 Students, you have to do an essay for this discussion. The essay is consist of a introduction, a body, and a conclusion. You have write this essay like you are teaching your children, co-worker, or a stranger how to work...
We survey a group of students to determine if they are CS Majors Group/Pet Preference cs...
We survey a group of students to determine if they are CS Majors Group/Pet Preference cs Other Men 22 21 Women 11 19 a) Setup a Hypothesis test for "Are men and women equally likely to be CS majors ?" b) Run the z-score. (z* =1.960) c) What do you conclude ?
What are the five Cs of credit?
What are the five Cs of credit?
Identify the UCS, UCR, CS, and CR in the following examples: UCS UCR CS CR 1....
Identify the UCS, UCR, CS, and CR in the following examples: UCS UCR CS CR 1. Jamie was talked into riding on the roller coaster when she was 12. The ride absolutely terrified her. Now whenever she goes to the amusement park, she breaks into a cold sweat if she even looks at the roller coaster 2. Kim was sick all night after eating a bad fried oyster. Now, she says, the smell of ANYTHING frying makes her feel nauseated....
Std reduction potential of alkali metals E˚(M+/M) is constant within group 1, i.e. Li=-3.04 and Cs=-3.03....
Std reduction potential of alkali metals E˚(M+/M) is constant within group 1, i.e. Li=-3.04 and Cs=-3.03. Explain why this happens. (hint use thermodynamic cycles to identify key factors)
Question 1 The random variables X & Y are independent. E[X] = 5, E[Y] = 7,...
Question 1 The random variables X & Y are independent. E[X] = 5, E[Y] = 7, Var[X] = 4, Var[Y] = 6. Calculate following expectation and variance. ( ?[?] = ? ,???[?]=?2) ?? (a) E[5X+6] (b) E[6Y+3] (c) E[X-Y+11] (d) Var[3X+10000] (e) Var[-4Y-1234567] (f) Var[2X-3Y]
I need a prelab on diversity: plants it needs to have a purpose and a protocol....
I need a prelab on diversity: plants it needs to have a purpose and a protocol. at least 1pg. * can't post the article due to copyright so here at some hints/keywords. ----- For the purpose of this lab, however, the plants will be divided into five main groups. Although these groups do not have any real taxonomic function, they are useful starting point for learning about plants. These are the groups: the green algae, the nonvascular plants, the vascular...
Using cs Cstring and c character as arguments in functiuon strfind(cs,c). The function should return the...
Using cs Cstring and c character as arguments in functiuon strfind(cs,c). The function should return the index of wanted letter in string. For example, strfind("hello world", 'o') would return 4. If the character is not found, the funtion returns -1. Please code in c++
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT