Part I: Excel (60 marks) Steps to Perform: Step 1 2 3 5 6 7 8 Instructions Start Excel. Download and open the file named grades_excel_for_al_xlsx. Rename the given worksheet as grades_Winter2022. Enter a heading for this document titled 'Student Grades for CIS1201 Winter 2022 in G2; merge G2 with cells H2, 12, J2, K2, and L2, such that the heading appears in the center of G2:L2. Increase its font to 16, change the font to Arial, and bold it. Note that A5:A104 are empty. Use the Fill Series feature to enter student ids beginning with 1111111 for A5, increase by 1 such that A104 gets 1111210. Note that the grades stored in the given sheet have an inconsistent format. Format all grades in the range B5:S104 with two decimal places and apply bold and center. The entire range of values should look the same after this step. Add a new column between Chapter11 and Final Exam and enter its description as Textbook assessment. This column will store the average grade a student achieved in the textbook chapter activities. Note that there are 11 chapters (columns H to R) - you must use a function that drops the lowest grade and uses the top 10 grades from these 11 columns to find the average grade and store it in Textbook assessment column. Format all grades in this column with two decimal places. The average must be in the percentage number format. Note that columns C and D store student's lab exam I and lab exam II grades. The max grade for these 2 columns is 10 but the instructor would like all grades to be stored in percentage (out of 100). Enter 10 in column C2; Use the copy-and-paste-special method to update the existing grades in columns C and D to be stored out of a max of 100 now (e.g. 9 should be updated 90). Reapply any formatting if it is removed, so the entire table is consistent. Create column U and call it 'Overall Grade'. Use a formula as given below to calculate the overall grade in a percentage number format (same as Step 6): Overall Grade = 10% of Lab Attendance (Column B) + 10% of Lab Exam I (Column C) + 10% of Lab Exam II (Column D) + 25% of (Assignment1+Assignment2+Assignment3) + 10% of Textbook Assessment + 35% of Final Exam / 100 Points Possible 0 2 2 2 2 6 5/nStep 9 10 11 12 13 14 15 16 17 Create column V and call it "Letter Grade'. Use a formula as given below to calculate the letter grade: OVERALL GRADE (COLUMN U) >= 80 < 80 AND >= 70 Instructions B с D F LETTER GRADE (COLUMN V) <70 AND >= 60 < 60 AND >= 50 <50 Hint: Use function IF in a nested way. Create a new worksheet and name it Overall statistics_W2022. Using cell referencing, copy the values in columns with Id. Overall Grade and Letter Grade from grades Winter2022 to this worksheet. ABCDE Count 52 23 Create a heading for this worksheet titled "Overall Statistics of Student Grades CIS1201 Winter 2022". This heading must have the same format as the heading from Step 3. F In worksheet Overall statistics_W2022, create a column, name it Grades, and fill it with letters A, B, C, D, and F. Create another column, name it Count and fill it with the total number of students that scored grades A, B, C, D or F. You must get the following count Grades A 11 8 6 Hint: Use function COUNTIF on C5:C104 of this worksheet. Draw a 3D-column graph using Grades as the X-axis and Count as the Y-axis. Title the graph as "Distribution of Grades - CIS1201 Winter2022"; title the X-axis as "Letter grades" and Y- axis as "Total Number of Students". In Rows 106, 107, 108 and 109, use functions to calculate and display the Mean, Median, Max value and Min Value of the overall grades in Column B of worksheet Overall statistics_W2022. Create a third worksheet and name it grades_Sorted_FinalExam. Add columns id, Final Exam, Overall Grade and Letter Grade. Sort this worksheet on the Final Exam grades, in order of largest to smallest. On the grades_Sorted_FinalExam sheet, insert the Draft.png picture on the Sheet Background. This can be downloaded from the assignment page on Courselink. Ensure that the worksheets are in the following order: grades_Winter2022, Overall statistics_W2022, grades_Sorted_FinalExam. Make sure that the workbook is saved as an Excel workbook, not a macro-enabled workbook. Close Excel, and then submit the workbook as directed. Total Points Points Possible 10 3 2 6 6 4 4 2 0 60

Tourists seeking a luxury tour on the Palace on rail in India can opt for either the Luxury service or the Maharajah service. The manager of the Palace on rail has produced the following table to show the bookings she received last season: | Type of Booking Tourist's home Maharaja Germany Holland 13 29 24 Luxury Service 44 36 19 27 The data is also provided in the "MBF Summative data.xx" file under the "Task 4-Data"

3. Use the Histogram tool to develop a frequency distribution and histogram for the number of months as a customer of the bank in the Excel file Credit Risk Data. Use your judgment for determining the number of bins to use. Compute the relative and cumulative relative frequencies and use a line chart to construct an ogive.

Assignment: Grade Analysis As Dr. Dae Jeong's teaching assistant, you maintain the gradebook for the ACC 200 Financial Accounting class. Dr. Jeong wants you to create a pie chart that shows the percentage of students who earn each letter grade. You will also create a bar chart to show a sample of the students' test scores. Furthermore, Dr. Jeong wants to see if a correlation exists between attendance and students' final grades; therefore, you will create a scatter chart depicting each student's percentage of attendance with their respective final grade average. 1. Open 603m2Grades and save it as e03m2Grades_LastFirst. 2. Use the Insert tab to create a pie chart from the Final Grade Distribution data located below the student data in the range F35-G39 and move the pie chart to its own sheet named Distribution. 3. Customize the pie chart with these specifications: Apply the Style 12 chart style • Type ACC 200 Final Grades: Fall 2024 for the chart title. • Explode the A grade slice by 7%. Change the F grade slice to Dark Red • Remove the legend. Add the Alt Text The pie chart shows the percentage of the class that earned each letter grade. Most students earned B and C grades (including the period) 4. Add data labels and customize the labels with these specifications: Display these data labels: Percentage and Category Name in the Inside End position. Remove other data labels. Change the font size to 18 and apply Black, Text 1 font color 5. Create a clustered bar chart using the ranges AS:DS and A18:D23 in the Grades worksheet and move the bar chart to its own sheet named Sample Scores 6. Customize the bar chart with these specifications: Apply the Style 5 chart style./nfmu.blackboard.com GY 4. Add data labels and customize the labels with these specifications: • Display these data labels: Percentage and Category Name in the Inside End position. Remove other data labels. - Change the font size to 18 and apply Black, Text 1 font color. a 8-Fall 8. Add the Alt Text The scatter chart shows the relationship of each student's final gratie and their attendance record. (including the period). 9. Apply these label settings to the scatter chart: • Type Final Average-Attendance Relationship for the chart title and bold the title. • Add the following primary horizontal axis title: Percentage of Attendance. • Add the following primary vertical axis tite: Student Final Averages. a 5. Create a clustered bar chart using the ranges AS:DS and A18:023 in the Grades worksheet and move the bar chart to its own sheet named Sample Scores. 6. Customize the bar chart with these specifications: Apply the Style 5 chart style. Type Sample Student Test Scores for the chart title. Format the plot area with the Light Gradient-Accent 2 fill color. • Position the legend on the right side and change the font size to 11. Format the Final Exam data series with Blue-Gray, Text 2 fill color. Add data labels in the Outside End position. • Select the category axis and display the categories in reverse order in the Axis Options category in the Format Axis task pane so that O'Hair is listed at the top and Sager is listed at the bottom of the bar chart. 10. Select the vertical axis. Change the Minimum Bound to 40, the Maximum Bound to 100, and select Number format with zero decimal places. 11. Select the horizontal axis. Change the Minimum Bound to 40 and the Maximum Bound to 100. 12. Apply the Parchment texture fill to the plot area. Q • Select the category axis and change the font size to 12. • Add the Alt Text The bar chart shows test scores for six students in the middle of the list. Quinn earned the highest scores on all tests. (including the period). 7. Create a scatter chart using the range ES:F31 in the Grades worksheet, the attendance record and final averages from the Grades worksheet. Cut the chart and paste it in cell A42 Adjust the width to 5.5" and the height to 5.96". 13. Add a linear trendline chart element. 14. Insert Column sparklines in the range H6:H31 using the range B6:D31 as the data source. Display the low points. Format the sparklines axis by using the same minimum value for all sparklines and the same maximum value for all sparklines Change the row height to 22 for rows 6 through 31. 15. Save and close the file. Exit Excel. Based on your instructor's directions, submit: e03m2Grades LastFirst in this location. + 69 G

Lab Two Project Instructions ITM 300 1. Chart and Lookup Table worksheets a. Download and Open the Excel file Lab Two of ITM 300, briefly view the 3 worksheets (Chart, Lookup Table, and Data) and then save the workbook as (your true) your LastnameFirstname_Lab_2. b. Complete the Task 1, Column chart and the Task 2, pie chart at the Chart worksheet by instructions on the page. c. Click the Loopup Table worksheet, Assign the range name tours to the data for the package, tour description, departure, and base cost (Cell B1:E10) by “Define Name…” menu. 2. Insert Functions and Formulas at DATA worksheet At Data Worksheet, you need to insert lookup functions by using the tours table that look up the tour code, and then return the trip description, departure date, and base cost of the trip for Columns D, E and F. Then you need to insert a formula to calculate the cost with taxes and fees and the monthly payment. a. Click cell D10 on the Data sheet, and then insert a VLOOKUP function that looks up the tour code (Col. C), using table tours as the Table_array, and returns the tour description. b. Same as step a, Click cell E10, and using VLOOKUP function to get the departure date. c. Same as step a, Click cell F10, and using VLOOKUP function to get the base cost of the trip. d. Click cell G10, and then insert a formula that adds taxes and fees to the base cost of the trip (in cell F10) by using the percentage value in the Input area, cell H4. Use an absolute cell reference when you need. e. Click cell H10, and then insert the PMT function to calculate the payments for students who want to pay for their trips in three (3) installments. Use the interest rate and months in the input area. Use appropriate relative, mixed, and/or absolute cell references in the formula. f. Hide the Tour Code column. 3. Final Page Setup & Project Submission a. Insert a footer for each worksheet with your full name on the left side, the file name in the center, and the Date on the right side. b. Submit your Lab Two Excel file, LastnameFirstname_Lab_2, at “Submit Project Here” of Blackboard before the 11:59 PM Pacific Time of the due date.

1. Consider an auto wash shop with 2 machines. There are 6 spaces available for the cars to wait. Any car that arrives and finds the shop full, leaves the shop. The arrival rate is exponential with a rate of 2 cars per hour. The service time of a repairman is normally distributed with a mean of 10 minutes and a standard deviation of 2 minutes. The number of waiting spaces can be changed by the management. Identify range of the following variables and the parameters for this system: .State variables . Decision variables .System parameters

Assignment 2 The 2021 Intel Form 10-K can be found at the following Web site SEC.gov. Use 2021 Financial Statements. Be precise on your calculations. When you are finished with this assignment you will complete the quiz on D2L title Assignment 2 to record your answers. The questions will be presented as multiple choice or true/false. You will have only 30 minutes to enter your answers to D2L which means you need to complete this assignment before recording your answers. Assume I do not know anything about these metrics. Answer the following questions so that a non-financial minded person can understand: 1. Compute the following KPIs for just 2021. i) CURRENT RATIO ii) QUICK RATIO iii) DEBT / EQUITY RATIO 2. Compare your results calculated in item (1) to the semi-conductor industry listed below. Explain your results in terms of your comparisons. How does Intel compare and what does that mean for Intel's financial position related to its peers? Financial ratio Solvency Radios Debt-to-equity ratio Interest coverage ratio Liquidity Ration Current Ratio Quick Ratio Cash Rafo 2020 0.67 4.70 3.32 2:41 1.61 3. Explain why the current ratio for the semi-conductor industry would be higher than the overall market average of 1.9? (Hint: think about the components of the current ratio for a semi-conductor company versus other types of companies) 4. Prepare a common size balance sheet for 2021 and 2020. Identify any significant trends between the two years. Provide a possible explanation for the identified trends.

1. (15) (a) APEX Machine Tool and Die has 5 key departments, A through E, and the departments are arranged as shown in the diagram below. Develop a classic "From-To" distance chart for the layout shown. Assume that the each department tooling is located at the "X" in each of the department areas. i. ii. iii. Product 20 1 2 3 4 A E x 65" Routing A-B-C-D-E A-D-E A-B-C-E A-C-D-E B X 100 feet- 25 X 10' (b) There are 4 products produced at the APEX facility. Routing and forecasting information is shown in the chart below. Batch Size 50 17 50 50 50 X 25' OP 50 10' 100 feet Production Forecast (Units/week) 200 900 400 650 Create a "From-To" Chart that shows the number of material handling trips/week between departments. Given that the cost to transport items is $2 per foot, create a "From-To" chart showing material handling cost/week between departments. Create an AR for APEX based on the material handling cost from the chart developed in ii above. Use the standard A,E,I,O,U for AR charts

Part #1: Download and Work with the Data Open the data in Excel and Explore the Data Using excel on your location computer, apply what you learned in class to clean and aggregate the data into more useful and meaningful information. Tips: Excel is a very powerful tool that can be a bit overwhelming. For this assignment, make use of column filters, SUM, AVERAGE, and SUBTOTALS functions. 1. In Excel, put your cursor focus on the first cell for the "Student List' table. 2. In the "Data" tab, click the "Filter" button. 3. After clicking the "Filter" button, you should see the header row change to now contain arrows to indicate dropdown filters. Full Student List with Attendance and Midterm Grades Student (Last Name, First Nam Attendan Mid Term Gra Lee, Eli 24 Brown, Jaxon Young, Kimiko n.a 26 13 14.2 13.7 Late Submissi The syntax for the SUBTOTAL function in Excel is- By using the dropdown filters, you can gain more insight into the data by isolating a single collection of data. Section TTh 4-6 PM Online TTh 4-6 PM 4) To find the Average Attendance for a specific section, first add a SUBTOTAL row to the bottom of the table. SUBTOTAL(function_num,ref1,ref2,...) The sub functions that can be performed by declaring the "function_num" are as follows --

1 7 points Copy and paste the data from the spreadsheet of class height data into excel, and use the data analysis tool to answer the questions. Report your answers to two decimal places, and include leading zeros for answers less than 1. For the last blank, enter is or is not. The mean height of students who prefer the pronoun She is type your answer... standard deviation for students who prefer the pronoun She is type your answer... cm. The mean height of students who prefer a pronoun other than She is type your answer.... The standard deviation is type your answer... value for these two groups is type your answer.... cm. The cm. cm. The F-statistic type your answer... test is type your answer... that the variance type your answer... significantly different (type is or is not). The critical F value is .The p-value for the F , which means