Purpose: - Department of Computer Science and Engineering



CS&E 1111/1112 Lab 7: spreadsheet designPurpose: 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.Lab 7: Pre-Lab Assignment –complete before coming to lab:spreadsheet design – columbus air problemYou 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 a 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. These values are as follows:Employee wagesYou have 3 pilots, who are each paid wages of $90,000 a yearYou 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 CostsMaintenance 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 gallonsTotal annual fuel consumption on the Los Angeles route was 1,370,000 gallonsTotal 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 yearCalculating previous year’s profitUsing 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 your completed result as ColumbusAir.xlsx. Submit ColumbusAir.xlsx during your closed lab period.ChecklistColumbusAir.xlsxBring To Lab: Make sure to bring your ColumbusAir.xlsx file to lab, as you will be using it for the in-lab assignment. Please bring your CSE1111 Class Notes to lab. The online version of the text may be used as needed for reference during class or you may bring the appropriate text pages. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download