In: Finance
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:
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.
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 |