编程讲解 Computer Applications for Business

Learning Objective:This assignment is designed to introduce you to Spreadsheets using MS Excel. For thisassignment, you will upload your Excel file to Sakai so that it can be accessed by just clicking onthe file's link. The main topics exercised in this assignment are: formulas, formatting andcharting.To get credit for this assignment:1 Deliver the assignment9.xlsx file to Sakai on time.2 Your TA should be able to open your file by clicking on its link.Directions:• Follow the requirements listed on the next page.• You must NOT use Excel's built-in functions for solving this assignment.• Use formulas to complete the Caterpillar, Trips and Formulas worksheets of this assignmentusing the information listed on the next pages.• Enter your name on cell H1 of each of three worksheets.• Submit your assignment9.xlsx file to Sakai using the Sakai-> Assignments link.Requirements:Caterpillar spreadsheet1. Complete the missing information (yellow cells) with the following accounting formulas:a) Gross Profit: Total Revenue minus Cost of Revenue.b) Total Operating Expenses: The sum of Research Development plus Selling General andAdministrative plus Non Recurring plus Others.c) Operating Income or Loss: Gross Profit minus Total Operating Expenses.Page 2 of 3d) Earnings Before Interest and Taxes: Operating Income or Loss plus Total OtherIncome/Expenses Net. (Note: The / symbol is just part of the expressionIncome/Expenses.)e) Income Before Tax: Earnings Before Interest and Taxes minus Interest Expense.2. Create the following charts:a) 3D Clustered Column Chart: Include Gross Profit and Total Operating Expenses for thethree years available. Move the chart to its own spreadsheet, name it Column Chart.Apply Chart Style 3 and Layout 1.b) 3D Pie Chart: Include the Income Before Tax data for the three years available. Relocatethe chart so that it fits in the area G9:J18. Apply Chart Style 2 and Layout 3.c) Line chart: Include Interest Expense and Income Tax Expense for the years 2013 and2015. Move the chart to its own worksheet naming it Line Chart. Apply Chart Style 5 andLayout 2.Trip spreadsheetA businessperson needs to travel through five states for five days. Complete the missing cells(yellow cells) by creating appropriate formulas with the information provided. Tips apply toMeals ($) and Lodging ($) only while taxes apply to Total gas ($), Meals ($) and Lodging ($). Tipsand Taxes have different rates for each State.The Total Cost row should add up the items from Total Gas ($) through Taxes ($) for each Stateand Day. The total column simply adds up the values for their respective rows.Apply the following cell styles: Total style to the Total Cost row items, Accent 1 to the headingson row 3, 40% Accent 3 style to the headings on row 4, Accent 5 style to the labels on columnC.Also apply Accounting number format to the $ denominated values inside the main table –include the Total Cost and Price per gallon values - and set their number of decimals to 2.Formulas spreadsheetEnter a formula in the cells indicated – yellow background – using the formulas listed below.1. Surface area of a cone: 𝝅 × 𝒓 × √𝒓𝟐 + 𝒉𝟐2. Displacement: 𝒗 × 𝒕 − (1/2) × a × 𝒕𝟐 Page 3 of 33. Escape velocity: √𝟐 × 𝒈 × 𝒎/ 𝒓4. Monthly payment of a loan: 𝑳 × (𝒓 × (𝟏 + 𝒓)𝒏) / ((𝟏 + 𝒓)𝒏 − 𝟏 )5. Present value of a future amount: 𝑭 / (𝟏 + 𝒓)𝒏References:1. Chapter 13 of the Fluency6 textbook2. Recitations and Lectures