https://publicpagestutorbin.blob.core.windows.net/%24web/%24web/assets/Vector_3_18e566da35.png

Excel Homework Help | Excel Assignment Help

Excel in Exams with Expert Excel Homework Help Tutors.

https://publicpagestutorbin.blob.core.windows.net/%24web/%24web/assets/Frame_1_7db546ad42.png

Trusted by 1.1 M+ Happy Students

4.4Trust Pilot

4.4Edu Reviewer

5App Review

4.8Student

Place An Orderand save time

^*Get instant homework help from top tutors—just a WhatsApp message away. 24/7 support for all your academic needs!

Quality Excel Assignment Help At Your Fingertips

Excel is a common subject if you are pursuing advanced computer science studies. Though CS is popular, Excel is quite the opposite. It's one of the most misunderstood subjects and disliked by a large number of students. This subject is closely associated with math and calculations. Indeed, it is not a cakewalk due to its complexity. However, with Excel homework help from experts, acing Excel can be far easier than you think. Our experienced professionals empower you with quality Excel assignment help, enabling you to overcome difficulties and improve your Excel knowledge.

Online Excel assignments assistance is one of the best ways to provide personalized guidance based on your academic requirements. Whether it's doubts, complex concepts, inability to analyze data, or creating formulas for diverse activities, homework help Excel can help you overcome your study challenges. From basic functions to advanced techniques, we provide clear explanations and practical examples to help you master Excel. TutorBin ensures you get expert help at your fingertips, paving the way to achieve academic success.

Excel Homework Help- Subjects Our Experts Cover

Considering the importance of strengthening students' understanding of Excel skills, TutorBin provides specialized support in various critical areas of this discipline. Our team of professionals offers comprehensive assistance with diverse Excel assignments, irrespective of their complexities. Experts recommend seeking homework help excel without any hesitation. It not only saves you time but also helps you to perform better during your academic years.

Excel basics
VLOOKUP
IF Function
Pivot Tables
Charts
Excel Analytics
Macros
Data Analysis
Cell Referencing
Keyboard Shortcut
Conditional Formatting
Excel VBA
Data Validation
Power Query
Data Cleanup
Index Match
IFERROR
Statistics

Homework Help Excel- Topics Covered By Experts

Whatever topic you find challenging, our Excel assignment help experts are there to help. Our subject matter specialists are adept at offering thorough guidance on your chosen topic. TutorBin professionals ensure our expert assistance helps you meet your academic requirements and become proficient in Excel.

Data entry and formatting
Creating basic & advanced formulas
Application of advanced formulas
Navigating the Excel interface
Managing worksheets
Creating tables and graphs
Presenting data in basic visual charts
Sorting and filtering data in ascending or descending order
Subtraction, multiplication, and division
Making use of the Formula tab and Functions library
Using formulas to manipulate basic data
Building a PivotTable to extract insights from data
Effective use of the AutoFilter feature
Record and edit macros

Help With Excel Assignments For Students: Why Do You Need It?

Research indicates that teachers often assign homework or tasks based on their assumptions about students' abilities. However, students' varying learning capacities cause stress as they struggle to complete their assignments appropriately. With extensive EdTech sector experience, TutorBin understands the problems students face while studying Excel. Whenever you request us to "do my Excel homework for me," we know you need help overcoming these.

Restricted Conceptual Understanding:

Understanding the underlying concepts behind Excel features such as pivot tables, macros, and data analysis can become a reason for difficulty. It hinders your ability to implement your theoretical knowledge in real-world scenarios.

Complexity of Assignments:

The Excel interface and functionality often seem overwhelming for beginners. Students face confusion due to the complexities of concepts and their applications, which leads to frustration and prevents them from successfully completing assignments.

Lack of Personalized Guidance:

Professors often assist students, presuming that everyone understands the same, but students have different academic requirements. Not one solution fits all. Students often fail to understand because of a lack of personalized guidance.

Limited Access to Resources:

One major problem students face while studying Excel is their minimal access to ample learning materials and tutorials. It makes Excel learning challenging, as students find it difficult to expand their knowledge beyond basic operations.

Difficulty in Data Analysis:

Students with weak analytical skills feel daunted when tasked with analyzing large datasheets and asked to perform complex calculations in Excel. Their lack of analytical knowledge hinders them from applying analysis techniques appropriately.

Integration Challenges:

One of the most complex things students often experience is successfully integrating Excel with other software tools or platforms. In most cases, students could not accomplish their data import/export and automation goal due to unfamiliarity with these processes.

Error Detection & Handling:

One of the most common yet critical problems students experience while working on Excel formulas and functions is error detection and rectification. The complicated nature of the Excel interface often causes students to fail to point out their mistakes.

Software Version Compatibility:

Students encounter issues when their assignments require specific features that are unavailable in their version of Excel. The lack of functions compatible with their software version makes assignment completion tougher than anticipated.

Time Constraints:

Balancing Excel assignments with other academic commitments can be challenging, requiring a lot of time and effort. Students with limited time to refine their skills and priorities find complex Excel assignments problematic and stressful.

Lack of Practice:

Experts have experienced that a large number of students do not practice Excel sufficiently outside of their coursework. It results in a limited understanding, obstructing them from performing well and achieving high grades in their college years.

"Do My Excel Homework"- Is it Right To Ask?

Questions and concerns about the security and legality of homework help are common among students. Let's address your doubts about the legitimacy of using TutorBin's "pay someone to do Excel homework" service. It is similar to offline tutoring from experts, where you can obtain professional assistance to improve your learning experience and boost your performance. Some professors don't prefer it, but getting our experts' guidance is not considered cheating. There is no legal prohibition in the laws of countries.

Moreover, obtaining professional help from TutorBin is completely safe as the site is encrypted with high-security software and never discloses your information to any third party. You can ask me to do my Excel homework for me, and we are sure this learning approach will enhance your academic competency and ensure success.

Online Excel Assignments Help Benefits For College Students

Expert Guidance In Excel

When students ask TutorBin to "do my Excel homework," they enjoy assistance from subject specialists. From concept clarifications and resolving doubts to tackling complex problems, our experts support students in every possible way. Their extensive expertise and professional experience empower students to overcome study struggles and strengthen their knowledge base. Moreover, students achieve a better learning experience with the help of valuable insights from professionals.

Customized Learning Facility

Personalized guidance from subject matter experts is indispensable for students aiming for academic excellence. Our experts know that not every student requires the same assistance, as their problems vary. Our expert team ensures that whenever students reach out to TutorBin, they need customized guidance from a subject specialist. TutorBin professionals fulfill students' needs and work on problem areas. This approach boosts confidence in students and helps them meet their academic goals.

Accurate & Plagiarized Free

One of the significant benefits of nursing writing help is the assurance of receiving 100% accurate and unplagiarized solutions. TutorBin is associated with qualified experts who meticulously craft solutions tailored to the specific requirements of each assignment. Our expert team develops assignments from scratch, ensuring academic integrity. Team TutorBin also provides students with a Original work report to ensure the originality of their homework.

Assured Timely Assistance

A significant benefit of requesting "do my Excel homework for me" is the guarantee of timely support. TutorBin experts understand the significance of time and the cruciality of deadlines for students, especially in complex subjects like Excel. Therefore, our subject specialists put emphasis on prompt delivery. They ensure you don't have to wait long and submit it on time.

24/7 Academic Assistance

A major advantage of our "do my Excel homework" service is its 24/7 support from experts. Students can access immediate assistance whenever they face academic hurdles or require clarification on complex topics. It guarantees that students receive expert guidance and invaluable insights constantly, irrespective of different time zones. This accessibility nurtures a conducive learning atmosphere, assuring quick solutions without stressing about guidance.

Precise learning on specific topics

Our experts know that students often feel compelled to opt for assistance for entire coursework when specific guidance is unavailable. TutorBin has considered this and has designed a precise learning opportunity for students. Students can obtain targeted help on specific topics or subjects with this facility. Instead of assistance for the whole course, you can seek "pay someone to do my Excel assignment" for particular study areas, keeping it convenient and cost-effective for you.

Cost-effective Learning Guidance

Affordable learning support ensures students can access high-quality academic resources and assistance without straining their finances. If you are thinking of seeking online Excel homework help but can't do it due to a tight budget, TutorBin is there for you. Our expert assignment help team ensures every student who reaches us can enhance their skills and knowledge without stressing about finances.

Full Secured account Guaranteed

When it comes to homework help Excel, Secured account matters a lot to students. Seeking academic help from TutorBin offers the advantage of full Secured account, as our site is encrypted with advanced security software. Our team protects students' information and ensures it is not shared with any third party. TutorBin values your trust and is committed to restricting information disclosure to maintain the reliability of our clients.

College Nursing Help- Frequently Asked Questions

We receive numerous requests like "do my Excel homework." Our Excel homework help experts diligently respond to these requests, striving to meet students' academic requirements. But sometimes, students feel concerned and have doubts regarding different matters. Here, we have tried to answer some of the questions you often ask our experts. We hope the answers to these frequently asked questions bring you better clarity.

How Can I Pay Someone To Do Excel Homework?

For expert guidance, visit a top Excel homework help site like TutorBin. Sign up at www.tutorbin.com and contact professionals for assistance through the order form. Experts will connect to you within a few minutes.

Will It Be Cheating If I Request an Expert To Do My Excel Assignment?

Asking for help with an Excel assignment is just like getting assistance from tutors. It is not copying a task. Experts help you understand your questions and guide you in answering them. Asking for study assistance can not be considered cheating, as there is no legal prohibition against it.

How Much Does Excel Assignment Cost?

The cost of Excel assignment assistance starts at $6, though it varies depending on the deadline proximity, question complexity, and expert availability. However, with offers and discounts, students get assistance at an affordable price.

Recently Asked Excel Questions

Expert help when you need it

Q1: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 parametersSee Answer
Q2:2. A warehouse distribution system employs 10 trucks to customer locations. Three trucks can be loaded at the same time in the warehouse. Loading time is exponential with a mean of 1 hour. Travel time to the customers is also exponential with a mean of 1.5 hours. Unloading time is exponential with a mean of 1.2 hours. Return time is also exponential with a mean of 1.4 hours. Loading and unloading times can be changed by adding more workers. Determine the followings: ● State variables ● Decision variables ● System parameters.See Answer
Q3:Task 4 Tourists seeking a luxury tour on the "Palace on rail' in India can opt for either the 'Luxur 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: Tourist's home country France Germany Holland England Type of Booking Maharajah Service 13 29 24 8 Luxury Service 44 36 UNIVERSITY SCHOOL OF BUSINES AND TECHNOLOGY 19 27See Answer
Q4: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"See Answer
Q5: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.See Answer
Q6: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 --See Answer
Q7:At the bottom of the table, add the equation for SUBTOTAL with the AVERAGE sub function and select all data in the Attendance column. Collins, Willow Scott, Hunter Harris, David Totals 22 26 22 -SUBTOTAL(1,CS:C64) 14.2 14 14.9 Based on the section you filter the table with, your SUBTOTAL will automatically update with the average attendance for that section. 5) For ease of understanding, update the precision for the SUBTOTAL cells to 2 decimal places. Right click on the cell, select "Format Cell". In the dialog box select Category as "Number" and Decimal Places as "2" - IMF 1-3 PM MF 1-3 PM MF 1-3 PM 6) Some students in the MF and TTh sections have not attended any classes and they have a NULL or Non-Number value listed in the attendance column. NULL and Non-Number values will not be calculated in the SUBTOTAL Average. You must include those students in your averages to get the correct average, so to make sure they are included into your SUBTOTAL, enter 0 into those cells. 14.9 This is an important data cleansing step. 7) Perform the same techniques in "5)" and "6)" to clean and calculate the Average Midterm Score for each section. 8) To calculate the Total Late Midterms, use the SUBTOTAL function with a sub function of COUNTA MF 1- -SUBTOTAL(3,ES:E64) 9) Start filling out the template table. The final column to be calculated is the Average Midterm Grade (%). To calculate the percentage, divide the average score for each section with the total possible score and select the column category type as "Percentage". The total possible score is 10. The category type can be selected from the same "Format Cells" dialog window as detailed in *5)".See Answer
Q8: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 chartsSee Answer
Q9: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 GSee Answer
Q10: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 60See Answer
Q11: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 meansSee Answer
Q12:In the spreadsheet layout shown, what value would: ActiveCell.Offset(ActiveCell.Offset(0, -1), Cells(1, 3)) refer to? 1 2 3 4 5 A 3 8 -2 9 B 4 -4 11 7 C 1 3 5 2/nHow will the worksheet appear after the MakeArray subroutine is executed? A. C. 12 1 12 2 A A Option Explicit Sub MakeArray () Dim A (2, 2) As Double Dim i As Integer, j As Integer A(1, 1) -3.4 <=-9.7 5.8 4.5 Range ("A1: B2") - A End Sub A(1, 2) A(2, 1) A(2, 2) 3.4 5.8 0 OC 0 B 3 -9.7 4.5 04 3.4 B. 12 D. 1 2 A A 3.4 -9.7 4.5 -9.7 B B 5.8 4.5 5.8 3.4See Answer
Q13: New Perspectives Excel 365/2021 | Module 1: End of Module Project 1 Natal Biotech GETTING STARTED WITH EXCEL GETTING STARTED 1. 2. O 3. 4. O PROJECT STEPS As the operations manager, you use Excel to maintain project data and schedule facilities usage. You are finalizing the Projects and Scheduling worksheets for the current week. 5. Save the file NP_EX365_2021_EOM1-1_FirstLastName_1.xlsx as NP_EX365_2021_EOM1-1_FirstLastName_2.xlsx Edit the file name by changing "1" to "2". If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. Begin on the Projects worksheet by increasing the font size of the range A2:A3 to 12 point. Enter the values shown in Table 1 into the corresponding cells in the range B8:C11. Table 1: Data for the Range B8:C11 With the file NP_EX365_2021_EOM1-1_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. If cell B6 does not display your name, delete the file and download a new copy. 8 9 1 0 1 1 B Yvonne Byrom Fiona Hernandez Taisha Zygmont Jared Sandor Adjust the width of Column B using AutoFit. In cell E6, enter the following: AFS51EZ24 C 202 1 202 3 202 4 202 4 Select the range E6:E11, and then use the Flash Fill button (in the Editing group on the Home tab) to automatically enter codes into the remaining cells in the range. (Hint: You must use the Flash Fill button to properly receive credit for this step.) Hide Column C. CENGAGE New Perspectives Excel 365/2021 | Module 1: End of Module Project 1 6. 7. 8. 9. 10. 11. Switch to the Scheduling worksheet. In cell A3, change the cell content to Week of February 22, 2024 (instead of Week of February 15, 2024). Cut the contents and formatting of the range D15:E18 and paste them into the range A13: B16. In cell B13, create a formula using the COUNT function to determine the number of projects that have booked facility hours by counting the values in the range C6:C11. Adjust the zoom level of the Scheduling worksheet to 120%. Change the page orientation of the Scheduling worksheet to Portrait. Some weeks you need to track facilities-related information, but that's not necessary this week. Move the Facilities worksheet between the Projects and Scheduling worksheets, then hide the Facilities worksheet. 12. When you hand off your worksheet to the facilities team, another colleague will add information about contractors working on site. To create a placeholder worksheet for this data, insert a new worksheet in the workbook, rename the worksheet Contractors, and if necessary, move the new worksheet after the Scheduling worksheet. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workboo and then exit Excel. Follow the directions on the website to submit your completed project. CENGAGE New Perspectives Excel 365/2021 | Module 1: End of Module Project 1 Final Figure 1: Projects Worksheet Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved. A Natal Biotech 1 2 Current Projects 3 Week of February 15, 2024 4 5 Project Title 6 Algae Fuel Synthesis 51 7 Biomarkers 264 8 Immune Therapy 33 9 Protein Folding 5852 10 Water Reclamation 402 11 Gene Splicing 288 12 13 B Project Lead Ernestine Zellers Hester Alvarado Yvonne Byrom Fiona Hernandez Taisha Zygmont Jared Sandor Documentation Projects Scheduling Final Figure 2: Scheduling Worksheet D Branch White Red Red Informatics Blue Red Contrac... (+) ⠀ CENGAGE E Project Code AFS51EZ24 B264HA23 IT33YB21 PF5852FH23 WR402TZ24 GS288JS24 F A New Perspectives Excel 365/2021 | Module 1: End of Module Project 1 A 1 Natal Biotech 2 Project Schedule 3 Week of February 22, 2024 4 5 Project Code 6 AFS51EZ24 7 B264HA23 8 IT33YB21 9 PF5852FH23 10 WR402TZ24 11 GS288JS24 12 13 Total projects for week 14 Median hours per project 15 Greatest project hours 16 Fewest project hours 17 18 ◄ | Projects Scheduling Contractc ... B Resource Lab 2 Lab 1 Computing 2 Computing 1 Lab 1 Lab 2 ◄ CENGAGE 6 42 168 13 с Hours 48 24 168 13 96 36 DSee Answer
Q14: New Perspectives Excel 365/2021 | Module 3: End of Module Project 1 Barrel Barnard Consulting PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS GETTING STARTED 2. 3. 4. PROJECT STEPS 5. O 1. Chanelle Larson is a professional assistant with Barrel Barnard Consulting. Chanelle is responsible for collecting consultant hours for some of Barrel Barnard's projects and processing the payments. She wants to automate some tasks and calculate summary information automatically on the worksheet that consultants use to track their hours. Switch to the Timesheet worksheet. In cell C5, insert the NOW function to record the current date. 6. O 7. Save the file NP_EX365_2021_EOM3-1_FirstLastName_1.xlsx as NP_EX365_2021_EOM3-1_FirstLastName_2.xlsx Edit the file name by changing "1" to "2". If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. With the file NP_EX365_2021_EOM3-1_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. If cell B6 does not display your name, delete the file and download a new copy. Each worksheet covers a period of 20 work days. In cell C4, create a formula using the WORKDAY function to calculate the date 19 days from the start date (cell C3). Use AutoFill to complete the labels in the table of hours. Use the values in the range A9:A10 to extend the week numbering to the range A11:A12. Use the value in cell B8 to autofill the remaining weekday abbreviations in the range C8:F8. Add formulas to complete the table of hours used. In cell B17, create a nested formula with the IF and SUM functions that check if the total number of hours worked in week 1 (cells B9:F9) is equal to 0. If it is, the cell should display nothing (indicated with two quote marks: ""). Otherwise, the cell should display the total number of hours worked in week 1. Copy the formula from cell B17 to fill the range B18: B20. In cell H2, create a formula using the WORKDAY function to calculate the date 5 days after the period end date (cell C4). Create lookup functions to complete the summary section. In cell 16, create a formula using the VLOOKUP function to display the number of hours worked in the selected week. Look up the week number in cell 15 in the range A17:G20, and return the value in the 2nd column. Use absolute references for cell 15 and the range A17:G20. Copy the formula from cell 16 to the range 17:111 and then edit the formula in cell 17 to return the value in the 3rd column, the formula in cell 18 to return the value in the 4th column, the formula in cell 19 to return the value in the 5th column, the formula in cell I10 to return the value in the 6th column, and the formula in cell I11 to return the value in the 7th column. CENGAGE New Perspectives Excel 365/2021 | Module 3: End of Module Project 1 8. 9. Add formulas to complete the balances section. In cell K8, create a formula using the SUM function that calculates the total of the range D17:D20 and subtracts it from the value in cell J8. In cell K9, create a formula using the SUM function that calculates the total of the range E17:E20 and subtracts it from the value in cell 19. 10. In cell K10, create a formula using the SUM function that calculates the total of the range F17:F20, subtracts it from the value in cell J10, adds the total of the range G17:G20, and subtracts 160. 11. Copy the formula in cell J11 to cell K11. 12. In cell J16, create a formula using the AVERAGE function that calculates the average of the range B9:F12. 13. In cell J17, create a formula using the MAX function that returns the maximum value from the range B9:F12. 14. In cell J18, create a formula using the MIN function that returns the minimum value from the range B9:F12. Your workbook should look like the Final Figures on the following pages. The value in cell C5 has been intentionally blurred as it will never be constant. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project. CENGAGE New Perspectives Excel 365/2021 | Module 3: End of Module Project 1 Final Figure 1: Timesheet Worksheet Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 A Week # 1 2 3 4 1 2 Week # Hours Worked 3 B Reporting Period Hours 4 Namel Period start Period end Date completed Mon 8 8 0 9 28.00 40.00 32.00 44.00 2/1/2024 2/28/2024 Tue 8.5 7.5 8 8.5 Holiday Documentation Timesheet Hours Worked Wed 8.00 D + 7.5 9 8.5 8 Hours Used Other Hours Used Vacation E Thu 4 7.5 8.5 9.5 Sick 10.00 F Fri 0 8 7 9 Comp 2.00 G Total Hours For Week 40.00 40.00 40.00 44.00 CENGAGE H Timesheet due 3/6/2024 Summary Week # Worked Holiday Vacation Sick Comp Total I 28.00 0.00 0.00 10.00 2.00 40.00 Average Maximum J Minimum Balances Hours worked per day Period Start 56.00 24.00 6.50 86.50 7.2 9.5 0 Period End 56.00 14.00 8.50 78.50See Answer
Q15: New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 Tetra Sammander Insurance ANALYZE AND CHART FINANCIAL DATA GETTING STARTED 1. 2. 3. 4. 5. PROJECT STEPS Geneva Wood is the owner of Tetra Sammander Insurance. Geneva is preparing year- end financial data to summarize and visualize the company's position and modeling costs of a new life insurance product that the company is considering offering. She has created charts to illustrate some of her data and is using functions to calculate the future value of insurance policies. 6. O 7. O Save the file NP_EX365_2021_EOM4-2_FirstLastName_1.xlsx as NP_EX365_2021_EOM4-2_FirstLastName_2.xlsx Edit the file name by changing "1" to "2". If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. With the file NP_EX365_2021_EOM4-2_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. If cell B6 does not display your name, delete the file and download a new copy. Switch to the Revenues worksheet. In the range F6:F10, add line sparklines based on the data in the range B6:E10 and then add markers. Apply the Green, Accent 6 sparkline color and the Green, Accent 6, Darker 25% marker color. Apply a Gradient Fill, Green Data Bar conditional formatting rule into the range G6:G10. Geneva wants a clustered column chart representing the total revenue for each insurance type by year. Create a 2-D clustered column chart based on the range A5:E9. Resize and reposition the chart so that the upper-left corner is located within cell H5 and the lower-right corner is located within cell 024. Enter Yearly Revenues by Insurance Type as the chart title, then bold the title, and change the font size to 16 point. Apply a Green, Accent 6 border to the plot area of the chart and change the width of the border to 1.5 point. Apply a Glow Shape Effect to the chart area using 5 point Green, Accent Color 6. In the 2024 Revenues by Insurance Type 3-D pie chart (in the range H26:043), change the legend position to bottom. Add data labels that contain only percentages (not values) and that use the Center position. In the Total Revenues by Year 2021-2024 line chart (in the range A11:G28), change the minimum bound of the vertical axis to 2500000, change the major units of the vertical axis to 500000 change the minor units to 250000, and then add Inside tick marks for the minor units on the vertical axis. Apply a solid fill using the Orange, Accent 2, Lighter 80% fill color to the chart area. Apply the Green, Accent 6, Darker 50% font color to the chart title, and change the font to 16 point bold. Apply the Green, Accent 6 color to the data line, and change the width to 2.25 point. CENGAGE New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 8. 9. In the Insurance Type Contribution to Annual Revenue 2021-2024 stacked column chart (in the range A29:G46), remove the data series labeled Trends from the chart. Apply a Shape Fill to the chart area using the Orange, Accent 2, Lighter 80% fill color. Apply a Solid Line border to the chart area with a White, Background 1, Darker 35% border color. Create a 2-D Column chart based on the range A5:E10. Move the 2-D Column chart to the Revenue Summary worksheet, and then resize and reposition the chart so the upper-left corner is located within cell A5 and the lower-right corner is located within cell K22. Select Switch Row/Column to edit the chart data source. Change the chart type to create a Custom Combination Combo chart. Represent the following data series as a Clustered Column chart: Life, Homeowners, Auto, and Travel, and represent the Total data series as a Line chart using the Secondary Axis. [Mac hint - Keep the main chart a Clustered Column chart, select the Total data series, change the chart type to a Line chart, and plot the series on the Secondary Axis.] 10. Enter Policy Type and Total Yearly Revenue as the chart title. Add axis titles to the chart, enter Revenue by Policy Type as the left vertical axis title, and then enter Total Revenue as the right vertical axis title. [Mac hint - Add Primary Vertical and Secondary Vertical Axis titles.] Delete the horizontal axis title placeholder, if necessary. 11. Switch to the Payments worksheet, select the range A6:B156 and create a Histogram chart. Resize and reposition the chart so that the upper-left corner is located within cell D5 and the lower-right corner is located within cell N23. 12. For the horizontal axis, change the bin width to 250, add an overflow bin with a value of 1500, and change the number format to the Currency number format with decimal places to 0. Enter Distribution of Payout Amount per Policy in 2024 as the title of the chart, and format the chart title using bold. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project. CENGAGE New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 Final Figure 1: Revenues Worksheet Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved. 2 5 6 18 19 20 21 7 8 Auto 9 Travel 10 Total 11 12 13 19 14 15 16 17 31 32 33 34 35 36 37 38 39 42 43 44 Insurance Type 45 46 47 Life $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $- $ B 2021 2022 2023 2024 528,153 $ 828,672 $919,825 $ 670,553 772,046 936,491 1,475,911 1,793,231 1,690,384 1,085,226 1,658,226 1,038,049 23,831 21,185 33,642 24,189 $ 3,014,414 $ 2,871,574 $ 4,087,604 $ 3,526,022 2021 Tetra Sammander Insurance Revenues by Product Area 2021-2024 2021 Total Revenues by Year 2021-2024 2022 2022 2023 Insurance Type Contribution to Annual Revenue 2021-2024 Revenues Revenue Summary Payments + 2023 Life Homeowners Auto Travel Trends 2024 Total $ 2,947,203 4,977,679 5,471,885 102,847 $13,499,614 G 2024 Final Figure 2: Revenue Summary Worksheet H $2,000,000 $1,800,000 $1,600,000 $1,400,000 $1,200,000 $1,000,000 $800,000 $600,000 $400,000 $200,000 $- I J CENGAGE 2021 K Yearly Revenues by Insurance Type 2022 29% L Life Homeowners Auto Travel 1% M 2024 Revenues by Insurance Type 19% 51% 2023 N Life Homeowners Auto Travel 2024 O New Perspectives Excel 365/2021 | Module 4: End of Module Project 2 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 < Revenue by Policy Type 4 A 4 5 6 ZA10661 7 AC90797 8 XJ12955 9 YK84968 10 WW23072 11 DG41533 12 HN13664 13 QE93359 14 SU88195 15 TS61649 16 HE37809 17 IJ58657 18 IQ60494 19 LX33566 20 1040160 21 RJ28180 22 JA60855 23 NY74376 24 AK48460 B $2,000,000 $1,800,000 $1,600,000 $1,400,000 $1,200,000 $1,000,000 $800,000 $600,000 $400,000 $200,000 $- Policy Number 2021 Final Figure 3: Payments Worksheet Payout $329.38 $0.00 $0.00 $25,000.00 $0.00 $0.00 $0.00 $0.00 $430.44 $771.45 D Tetra Sammander Insurance Revenue Summary 2021-2024 $0.00 $0.00 $735.07 $25,000.00 $0.00 $604.52 $0.00 $0.00 Life Revenues Revenue Summary Payments Policy Type and Total Yearly Revenue D E Tetra Sammander Insurance Payments per Policy (weighted sample) 2024 120 100 E 80 60 40 2022 20 Homeowners 0 [$0, $250] $783.59 Revenues Revenue Summary Payments + F Auto G 2023 H H ⠀ 4 Travel -Total I 2024 J CENGAGE K $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $- L Distribution of Payout Amount per Policy in 2024 ($250, $500] ($500, $750] ($750, $1,000] ($1,000, $1,250]) ($1,250, $1,500] M K Total Revenue > $1,500 NSee Answer
Q16:1 A state driver's license exam center would like to examine its operation for poten- tial improvement. Arriving customers enter the building and take a number to determine their place in line for the written exam which is self administered by one of five elec- tronic testers. The testing times are distributed as EXPO(8); all times are in minutes. Thirteen percent of the customers fail the test (it's a hard test with lots of questions). These customers are given a booklet on the state driving rules for further study and leave the system. The customers who pass the test select one of two photo booths where their picture is taken and the new license is issued. The photo booth times are distributed TRIA(2.5, 3.6, 4.3). The photo booths have separate lines, and the customers tend to en- ter the line with the fewest waiting customers. If there is a tie, they enter the nearest booth, Booth 1. These customers then leave the system proudly clutching their new li- censes. The center is open for arriving customers eight hours a day, although the services are continued for an additional hour to accommodate the remaining customers. The cus- tomer arrival pattern varies over the day and is summarized below: Hour Hour 1 2 3 1 2 3 4 Arrivals per Hour 22 35 40 31 Hour 5 6 7 Run your simulation for ten days keeping statistics on the average number of test fail- ures per day, electronic-tester and photo-booth utilization (utilization for the testing re- source overall, but separate utilizations for each photo booth), average number in queue, and average customer system time for those customers passing the written exam. Arrival Per hour 22 35 40 Hour 5 6 7 Arrivals per Hour 35 43 29 8 22 Arrival per hour 35 43 29 22See Answer
Q17: New Perspectives Excel 2019 | Module 7: SAM Project 1a Valerian State College SUMMARIZING YOUR DATA WITH PIVOTTABLES sam' GETTING STARTED Open the file NP_EX19_7a_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as NP_EX19_7a_FirstLastName_2.xlsx by changing the "1" to a "2". If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the file NP_EX19_7a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. 2. Lael Masterson works in the Student Activities Office at Valerian State College in Illinois. Lael has started compiling information on students who are interested in helping run student organizations at Valerian State, and she needs your help completing the workbook. Switch to the Student Representatives worksheet. In cell E2, enter a formula using the HLOOKUP function as follows to determine a student's potential base hourly rate (which is based on the number of years of post-secondary education): a. Use a structured reference to look up the value in the Post-Secondary Years column. Retrieve the value in the 2nd row of the table in the range P13:U14, using an absolute reference. Because base hourly rate is tiered based on the number of years of education, find an approximate match. b. Fill the formula into the range E3:E31, if necessary. Student organizations sometimes require transportation for off-campus activities, and school policy requires students to be over 23 years old to serve as transport. Lael wants to determine how many of the active students will be eligible to transport other group members. In cell J2, enter a formula using the IF function and structured references as follows to determine if Kay Colbert can serve as authorized transport: a. The function should use a reference to the Age column to determine if the student's age is greater than 23, and should return the text Yes if true and No if false. b. 3. Fill the formula into the range J3:J31, if necessary. To be eligible for the leadership training program offered by the office, a student must have at least 2 years of post-secondary education or have gone through the organization finance training. In cell K2 enter a formula using the IF and OR functions and structured references as follows to determine if Kay Colbert can join the leadership training program: a. The IF function should determine if the student's Post-Secondary Years is greater than or equal to 2 OR if the student's finance certified status is "Yes", returning CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project la 4. 5. 6. 7. the text Yes if a student meets one or both of those criteria or the text No if a student meets neither of those criteria. b. Fill the formula into the range K3:K31, if necessary. Experienced students may serve as mentors if they are at least age 21 and have at least 3 years of post-secondary education. In cell L2, enter a formula using the IF and AND functions and structured references as follows to determine if Kay Colbert is eligible to serve as a mentor: a. b. The IF function should determine if the student's age is greater than or equal to 21 AND the student's post-secondary years are greater than or equal to 3, and should return the text Yes if a student meets both of those criteria or the text No if a student meets none or only one of those criteria. Fill the formula into the range L3:L31, if necessary. Lael is always on the lookout for students who might be interested in running for office in student groups. In cell M2, enter a formula using a nested IF function and structured references as follows to determine first if a student has already been elected to office in a student group, and if not, whether that student meets the qualifications to run in the future: If the value in the Elected column is equal to the text "Yes", the formula should display Elected as the text. a. b. Otherwise, the formula should determine if the value in the Finance Certified column is equal to the text "Yes" and return the text Yes if true And No if false. Students who work with student organizations are also considered for employment at the Student Activities Office. Students with more than 4 years of post-secondary education are qualified for more complex Tier 2 jobs. In cell N1, enter the text Tier as the column heading. In cell N2, enter a formula using the IF function and structured references as follows to determine which work tier Kay Colbert is qualified for: a. The IF function should determine if the student's Post-Secondary Years is greater than or equal to 4, and return the value 2 if true or the value 1 if false. 8. 9. b. Fill the formula into the range N3: N31, if necessary. Lael wants a quick way to look up students by their Student ID. In cell Q3, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, the text Invalid Student ID should display. Lael wants to determine several totals and averages for active students. In cell Q8, enter a formula using the COUNTIF function and structured references to count the number of students who have been elected to offices in student organizations. 10. In cell R8, enter a formula using the AVERAGEIF function and structured references to determine the average number of post-secondary years for students who have been elected. 11. In cell R9, enter a formula using the AVERAGE function and structured references to determine the average number of years of post-secondary education of all students as shown in the Post-Secondary Years column. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1a 12. Switch to the Academic Groups worksheet. In cell A14, use the INDEX function and structured references to display the value in the first row and first column of the AcademicGroups table. 13. In cell A17, use the SUMIF function and structured references to display the total membership in 2023 for groups with at least 40 members. 14. Lael is also planning for student groups that the office will be working with in the coming year. She decides to create a PivotTable to better manipulate and filter the student group data. Switch to the Academic PivotTable worksheet, then create a PivotTable in cell A1 based on the AcademicGroups table. Update the PivotTable as follows so that it matches Final Figure 2: a. b. Change the PivotTable name to: AcademicPivotTable Add the Activities field and the Group Name field (in that order) to the Rows area. C. Add the 2021, 2022, and 2023 fields (in that order) to the Values area. d. e. f. g. h. Change the display of subtotals to Show all Subtotals at Top of Group. Change the report layout to Show in Outline Form. Update the Sum of 2021 field in the Values area to display the name 2021 Membership with the Number number format with 0 decimal places. Update the Sum of 2022 field in the Values area to display the name 2022 Membership with the Number number format with 0 decimal places. Update the Sum of 2023 field in the Values area to display the name 2023 Membership with the Number number format with 0 decimal places. 15. Lael wants to summarize data for all student groups in a PivotTable. To do so, she must first update the AllGroups table. Switch to the All Groups worksheet then edit the record for the Astronomy Society to use 76 as the 2023 field value. 16. Switch to the All Groups PivotTable worksheet. Refresh the PivotTable data, then verify that the 2023 Membership value for the Astronomy Society in row 6 reflects the change you made in the previous step. 17. Apply the Light Blue, Pivot Style Medium 2 PivotTable style to the PivotTable. 18. Add the Office field to the Filters area of the Pivot Table. Filter the table so that only organizations with private offices are visible. 19. Filter the PivotTable as follows: a. Create a Slicer based on the Activities field value. b. Resize the slicer so that it has a height of 2.2" and a width of 3.2". C. Move the slicer so that its upper-left corner appears within cell F3 and its lower- right corner appears within cell J14. d. Use the slicer to filter the PivotTable so that only Fraternal groups are visible. 20. Lael also wants to summarize membership data for all organizations using a PivotChart to help determine which groups are showing the most interest from students. Switch to the Activities PivotTable worksheet. Based on the PivotTable on the Activities CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project la PivotTable worksheet, insert a PivotChart using the Clustered Column chart type and format it as follows: a. b. C. Resize and reposition the PivotChart so that the upper-left corner is located within cell F3 and the lower-right corner is located within cell 019. Add the chart title Membership by Type to the PivotChart using the Above Chart option. Filter the PivotChart so that only the membership data for groups with educational, field, and fraternal activities in each type of group displays in the chart. (This filter may be automatically applied when you create the table.) Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1a Final Figure 1: Student Representatives Worksheet R T U- Total Students Average Post-Secondary Years M 1 Student ID Name Age Post-Secondary Years 2 G62918 Kay Colbert 6 2022 Yes 3 P49234 Michael Crozier 25 7 16.50 2023 Yes Yes Yes No Yes ase Rate Class Finance Certified Grad Student Elected Qualified Driver Leadership Training Mentor Officer Qualified ♥ Tier ♥ 16.50 Yes Yes Yes Yes Yes Yes Yes Elected 2 Student ID Student Name G62918 Kay Colbert 4 W99035 Brandon Miles 21 3 15.75 2022 Yes Yes No Yes Yes Elected 1 5 V42056 Michael Alvarez 22 4 15.75 2023 Yes No No No Yes Yes 6 T59828 Ida Smith 19 2 15.75 2024 No No Yes No Yes No Elected 1 7 F72412 Betty Garza 24 6 16.50 Yes No Yes Yes Yes Yes 2 8 W20999 Leroy Pirkle 28 10 17.50 2022 Yes No Yes Yes Yes Yes 2 Elected Students 9Q18870 Warren Stewart 18 15.00 2026 No No No No No No 1 All Students 10 W75774 Stacy Wiggins 24 6 16.50 Yes Yes Yes Yes Yes Elected 2 11 N25220 Billy Herald 3 15.75 2022 No No No No Yes Yes No 1 12 094361 Margaret Cruz 23 5 16.50 Yes Yes Yes 2 2023 Yes 13 GB7578 Chester Keese 18 0 15.00 2024 No No No No No NO No 1 Postsecondary Years 14 B68170 Claudette Littell 19 1 15.25 No No Yes No Yes 1 15 M89375 Florence Miller 21 3 15.75 2023 Yes No No No Yes Yes Yes 1 Yes Yes Yes Yes 2 16 563900 Kimberly Gerace 26 8 17.50 2023 Yes 17 196036 Roman Goble 22 4 15.75 No No No Yes Yes Yes 2 18 C82505 19 Y75358 Jocelyn Allen 22 4 15.75 2022 Yes No Yes No Yes Yes Elected 2 Ron Morse 20 15.75 2022 Yes No No Yes Yes 20 60811 Roberta Ervin 21 3 15.75 2026 No No No No Yes Yes No 1 21 Q56783 James Rusnak 19 1 2022 Yes No Yes No No Elected 1 22 Q75281 Suzanne Lawrence 24 2 15.75 2025 Yes No Yes Yes Yes No Elected 1 23 V73596 ShannonGarner 18 0 15.00 2024 No No No No No No No 1 24 L86947 Emory Little 25 5 16.50 2023 Yes Yes Yes Yes Yes Yes Elected 2 25 W90960 Laura LI 24 6 10.30 2022 Yes Yes Yes Yes Yes Elected 2 26 P40886 No No No No Floretta Cauthen 18 15.00 2025 No 27 UBB627 Beverly Berry 23 5 16.50 2023 Yes No No No Yes Yes 2 28 Y88831 Stephanie Papa 15.25 Yes No Yes No 1 29 Р83373 Wallace Rivera 29 9 17.50 30 853454 Larry Russell 31 A60088 Aaron Sifford 19 25 15.25 16.50 2023 Yes 2022 No Yes No Yes Yes Yes 2 No No No No No 1 Elected 2 Yes Yes Yes Yes Documentation Student Representatives Academic Groups Academic PivotTable All Groups All Groups PivotTable Activities PivotTable Final Figure 2: Academic Groups Worksheet 11 3.8 2 15.25 15.75 16.5 17.5 B E பட F G 1 Group Name 2 Computing Club 3 Astronomy Society 4 Humanities and English Club Type Activities Office 2021 2022 2023 Academic Field Public 54 81 93 Academic Field Private 37 51 76 Academic Professional None 47 54 64 5 Environmental Management Club Academic Service Private 45 44 52 6 Communication Studies Club 7 Nursing Club Academic Professional Public Academic Service 30 32 51 Private 44 47 41 8 History Club 9 Psychology Association for Students 10 Investigative Forensics Club Academic Professional None Academic Professional Private Academic Field 48 40 40 29 26 23 Public 6 8 10 Academic Professional None 5 6 5 11 Accounting and Finance Forum 12 13 Largest Academic Club, 2023: 14 Computing Club 15 16 2023 membership in large groups: 17 417 10 Academic Groups Academic Pivo CENGAGESee Answer
Q18: New Perspectives Excel 2019 | Module 7: SAM Project 1b Brevard County Swim Clubs SUMMARIZING YOUR DATA WITH PIVOTTABLES sam GETTING STARTED Open the file NP_EX19_7b_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as NP_EX19_7b_FirstLastName_2.xlsx by changing the "1" to a "2". If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the file NP_EX19_7b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. 2. 3. Brittany Lambert is a volunteer for the Brevard County Swim Clubs in Melbourne, Florida, and has offered to compile data on the swim club employees and teams. She needs your help completing the workbook and analyzing the data. Switch to the All Employees worksheet. In cell E3, enter a formula using the HLOOKUP function as follows to determine an employee's potential pay rate, which is based on their years of experience: a. b. Use a structured reference to look up the value in the Years of Experience column. Retrieve the value in row 2 of the table in the range P14:U15, using an absolute reference. Because hourly pay rate is tiered based on the number of years of experience, find an approximate match. Fill the formula into the range E4:E32, if necessary. Each summer, the swim clubs host a camp for young swimmers. To work as a counselor at one of the summer camps, an employee must be at least 21 years old. Brittany wants to determine how many employees are eligible to be camp counselors. In cell J3, enter a formula using the IF function and structured references as follows to determine if Tiffany Adams can work as a camp counselor: a. b. The function should use a reference to the Age column to determine if the employee's age is greater than or equal to 21, and should return the text Yes if true and No if false. Fill the formula into the range J4:J32, if necessary. To be eligible to work with the swim team, an employee must have at least two years of experience or have completed CPR training. In cell K3 enter a formula using the IF and OR functions and structured references as follows to determine if Tiffany Adams can work with the swim team: a. The IF function should determine if the employee's Years of Experience is greater than or equal to 2 OR if the employee's CPR trained status is "Yes", returning CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1b 4. 5. the text Yes if an employee meets one or both of those criteria or the text No if an employee meets neither of those criteria. b. Fill the formula into the range K4:K32, if necessary. Employees may serve as swim team coaches if they are at least age 23 and have at least three years of experience. In cell L3, enter a formula using the IF and AND functions and structured references as follows to determine if Tiffany Adams is eligible to serve as a swim team coach: a. b. The IF function should determine if the employee's age is greater than or equal to 23 AND the employee's years of experience are greater than or equal to 3, and should return the text Yes if an employee meets both of those criteria or the text No if an employee meets none or only one of those criteria. Fill the formula into the range L4:L32, if necessary. Brittany wants to identify employees who are eligible to take a CPR course at the clubs' expense. Employees who can work as camp counselors are eligible for the course. In cell M3, enter a formula using a nested IF function and structured references as follows to determine first if an employee has already been trained in CPR, and if not, whether that employee meets the qualifications to take the course: a. b. If the value in the CPR Trained column is equal to the text "Yes", the formula should display Trained as the text. Otherwise, the formula should determine if the value in the Camp Counselor column is equal to the text "Yes" and return the text Yes if true and No if false. 6. 7. 8. 9. C. Fill the formula into the range M4: M32, if necessary. Employees who work at the swim clubs are also considered for employment at the Brevard County Parks and Recreation Department. Employees with more than four years of experience are qualified for more complex Level 2 jobs. In cell N2, enter the text Level as the column heading. In cell N3, enter a formula using the IF function and structured references as follows to determine which employment level Tiffany Adams is qualified for: a. The IF function should determine if the employee's Years of Experience is greater than or equal to 4, and return the value 2 if true or the value 1 if false. b. Fill the formula into the range N4:N32, if necessary. Brittany created a formula with the VLOOKUP function to look up an employee names by their Employee ID. She also wants to alert users when they enter an incorrect ID number. In cell Q4, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, the text Invalid Employee ID should be displayed. Brittany wants to determine several totals and averages for active employees. In cell Q9, enter a formula using the COUNTIF function and structured references to count the number of employees who can work as lifeguards. 10. In cell R9, enter a formula using the AVERAGEIF function and structured references to determine the average number of years of experience for lifeguards. 11. In cell R10, enter a formula using the AVERAGE function and structured references to determine the average number of years of experience of all employees as shown in the Years of Experience column. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1b 12. Switch to the Melbourne Swim Teams worksheet. In cell B14, use the INDEX function and structured references to display the value in the first row and first column of the Swim Teams table. 13. In cell B16, use the SUMIF function and structured references to display the total wins for teams in the Youth league. 14. Brittany wants to display the Melbourne swim teams data by league and year. She asks you to create a PivotTable to better manipulate and filter the data. Switch to the Melbourne PivotTable worksheet, and then create a PivotTable in cell A1 based on the Swim Teams table. Update the PivotTable as follows so that it matches Final Figure 3: Change the PivotTable name to: MelbournePivotTable Add the League field and the Team Name field (in that order) to the Rows area. a. b. C. Add the 2020, 2021, and 2022 fields (in that order) to the Values area. d. e. f. g. h. Change the display of subtotals to Show all Subtotals at Bottom of Group. Change the report layout to Show in Outline Form. Update the Sum of 2020 field in the Values area to display the name 2020 Wins with the Number number format with 0 decimal places. Update the Sum of 2021 field in the Values area to display the name 2021 Wins with the Number number format with O decimal places. Update the Sum of 2022 field in the Values area to display the name 2022 Wins with the Number number format with 0 decimal places. 15. Brittany wants to summarize data for all teams in a PivotTable. To do so, she must first update the AllTeams table. Switch to the All Teams worksheet then edit the record for the Brevard Blue Wave (row 26) to use 10 as the 2020 field value. 16. Switch to the All Teams PivotTable worksheet. Refresh the PivotTable data, then verify that the 2020 Wins value for the Brevard Blue Wave reflects the change you made in the previous step. 17. Apply the Lavender, Pivot Style Medium 13 PivotTable style to the PivotTable. 18. Add the League field to the Filters area of the Pivot Table. Filter the table so that only teams in the Youth league are displayed. 19. Continue to filter the PivotTable as follows: a. Create a Slicer based on the Level field value. b. Resize the slicer so that it has a height of 2.3" and a width of 1.5". C. Move the slicer so that its upper-left corner appears within cell F3 and its lower- right corner appears within cell G14. d. Use the slicer to filter the PivotTable so that only Level 4 teams groups are visible. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1b 20. Brittany also wants to summarize and compare the performance of the swim teams by level. Switch to the Swim Levels PivotTable worksheet. Insert a PivotChart using the Stacked Column chart type and format it as follows: a. b. Resize and reposition the PivotChart so that the upper-left corner is located within cell E2 and the lower-right corner is located within cell L22. Add the chart title Wins by Level to the PivotChart using the Above Chart option. C. Filter the PivotChart so that only wins in Levels 4, 5, and 6 are displayed. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. CENGAGE New Perspectives Excel 2019 | Module 7: SAM Project 1b Final Figure 1: All Employees Worksheet 8029 Employee ID | A213 404 Megan Beckman Catherine Coleman Carrie F Com · Agu Camp Counselor-Swim Team Team Coach CPR Con You V188 No V Trained Yes % You You Yes * Trained Yes Twe No Yes You Trained Yes No Yes Yes Trained Total Employees |„Average Years of Experience 9GETS Bryant Grossburg No No No No Martyn Jeger 11 L Jonathan Lempert 13 M993 Gina Mila 2014 2019 3030 No Yes No You Yes You No Yes No Trained Jeff News Yes Tes No Yes 14 0142 Crystal Check 23 No Too Tee Trainer Yolanda Packle 18 202 No No No No No $16.90 $16.75 $16.00 $16.50 $12.80 Michael Perwak 35 2017 Yes Yes Yes Yes Yes Trane 17 P33 Eh Pekk 24 2014 Tes No Yes Tee Tee Trained James Quirian 24 Tes Yes Yea Yes Yes Trained Ryan Quinlan 20 Yes You No Yes No Traned Andrea Shelby 2 No Yes Yes Yes Anza Tran 19 No Yes No You No No Tracy Upho 23 Yes No You You Yes JV 18 No No No No No Jose Vazque 23 No No Yes Yes No V No No No No 28 You Tox No You You You Trainer Julie Weine W909 Mary Winslow 24 34 Yes * Yes 2018 Yes Yes Yes Yes Traine Brandon Waddel Yes No You You frained Aaron Ye 2019 No You frained 31 7888 Key Yamashite Yes Trained Rochard Young Documentation 2014 Yes No Yes Trained All Employees Melboume Swim Teams Melbourne PivotTable | All Teams |-σ Final Figure 2: Melbourne Swim Teams Worksheet 1 2 A Team Name B C D E F G H I Wins Location Level▾ League ▾ 2020▾ 2021▾ 2022▾ Total 3 Melbourne Vipers Melbourne 6 Adult 6 8 9 23 4 Southern Surge Melbourne 5 Adult 5 7 10 22 19 20 56781D=22=56=222222 23 21 5 Florida Wave Runners Melbourne 3 Youth 7 7 7 7 Summer Barracudas 6 Swift Swordfish 8 Coastal Tarpons 9 Melbourne Sharks 10 Slippery Seals 11 Mighty Bluefish 12 Melbourne Wahoos 13 14 Most wins, 2020-2022: Melbourne Vipers Melbourne 2 Adult 3 7 8 Melbourne 1 Summer 4 6 6 Melbourne 6 Youth 5 6 5 Melbourne 3 Summer 2 5 6 Melbourne 4 Youth 2 4 6 Melbourne 1 Youth 3 2 4 Melbourne 2 Youth 4 2 1 28663217 9 Total Youth wins: 65 ་ Documentation All Employees Melbourne Swim Teams Melbourne PivotTable All Teams CENGAGESee Answer
Q19: New Perspectives Excel 365/2021 | Module 7: End of Module Project 1 Pierce Software SUMMARIZE DATA WITH PIVOTTABLES GETTING STARTED Save the file NP_EX365_2021_EOM7-1_FirstLastName_1.xlsx as NP_EX365_2021_EOM7-1_FirstLastName_2.xlsx O Edit the file name by changing "1" to "2". If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. With the file NP_EX365_2021_EOM7-1_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. If cell B6 does not display your name, delete the file and download a new copy. PROJECT STEPS 1. 2. 3. 4. 5. Tiana Morales is the director of the customer call center for Pierce Software. For the past three months, she has been keeping track of the support calls the center receives, and asks for your help in analyzing the data. Go to the Customer Call Log worksheet, which contains the Calls table where Tiana has been entering support call data. In the Call Type column, she needs to list the type of call corresponding to the code in the Call Code column. In cell E5, enter a formula using the HLOOKUP function to look up the call type according to the call code. Use a structured reference to look up the value in the Call Code column of the Calls table. Retrieve the value in row 2 of the CallTypes table (range A3:F4 in the Survey Questions worksheet) using a structured reference to the entire CallTypes table. Because each call type covers a range of values, find an approximate match. Fill the formula into the range E6:E143, if necessary. Customers can rate their service representative by completing a four-question survey at the end of each call. Tiana entered the scores in columns Question 1, Question 2, Question 3, and Question 4 of the Calls table. She wants to calculate the average rating for each call in the Average column. In cell J5, enter a formula using the AVERAGE function and structured references to average the values in the Question 1 to Question 4 columns of the Calls table. Fill the formula into the range J6:J143, if necessary. In the range L4:N13, Tiana set up an area to summarize some data about the service representatives handling the customer support calls. First, she wants to count the number of calls each service representative handled. In cell M5, enter a formula using the COUNTIF function and a structured reference to the Service Rep column in the Calls table to count the number of calls Barry (cell L5) handled. Fill the formula into the range M6: M13 without formatting. Tiana also wants to average the combined scores for each employee. In cell N5, enter a formula using the AVERAGEIF function and structured references to the Service Rep and Average columns in the Calls table to average the scores Barry (cell L5) received for all the calls he handled. Fill the formula into the range N6: N13 without formatting. Go to the Call Type by Rep worksheet. Tiana wants to compare the call data by type for each service rep. In cell A3, insert a PivotTable based on the Calls table. Use RepCalls CENGAGE New Perspectives Excel 365/2021 | Module 7: End of Module Project 1 6. 7. 8. 9. as the name of the PivotTable. Display the service rep names as column headings, and the call types as both row headings and values. Apply Light Yellow, Pivot Style Medium 12 to the PivotTable to coordinate with the table on the Customer Call Log worksheet. Center the data in the range B4:K10 to make it easier to read. Tiana notices that Service Rep Bruno has no calls in the How to category, which she knows is incorrect. Return to the Customer Call Log worksheet, and then change the Call Code in cell D10 to 12, which assigns a call to Bruno in the How to category. Go to the Call Type by Rep worksheet and refresh the data in the PivotTable. Go to the Scores by Date worksheet. In cell A3, insert another PivotTable based on the Calls table. Use Scores as the name of the PivotTable. Display the Date values as column headings, which automatically groups the data by month. Display the Service Rep names as row headings and the Average score data as values. Change the summary function to Average for the data in the Values area and apply the Number number format with 2 decimal places to those values. 10. In cell A3, use the text Average Scores to identify the values. In cell B3, use the text Months to identify the column headings. In cell A5, use the text Service Reps to identify the row headings. Apply Light Yellow, Pivot Style Medium 12 to the PivotTable to match the other PivotTable. 11. Tiana wants to focus on scores for calls involving technical problems in February. Create a slicer for the Scores PivotTable based on the Call Type field. Resize the slicer to a height of 2.5". Move the slicer so its upper-left corner is in cell F3 and its lower-right corner is in cell G14. Use the slicer to filter the Scores PivotTable by the Tech problem call type. 12. Create a timeline slicer based on the Date field in the Scores PivotTable. Resize the timeline slicer to a width of 7". Move the slicer so that its upper-left corner appears within cell A16 and its lower-right corner appears in cell G23. Use the timeline slicer to show data for February only. 13. Resize columns B:C to 12.00 and center the data in the range B3:C15. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project. CENGAGE New Perspectives Excel 365/2021 | Module 7: End of Module Project 1 Final Figure 1: Customer Call Log Worksheet Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved. A Pr Pierce Software 2 Customer Call Log: January-March 3 Customer Survey Scores M N Question Question Question Question 4 Call ID▾ Date▾ Service Rep▾ Call Code Call Type▾ 1 2 3 4 Average ▾ 5 20191 1/4/2024 Wagner 7 Feature request 4 5 2 4 3.75 6 20154 1/4/2024 Thanh 1 Bug report 2 1 3 1 1.75 Rep Barry Bruno Total Calls Average Score 7 21170 1/4/2024 Darego 17 Tech problem 5 5 5 5 5 5.00 Calder 8 21145 1/5/2024 Calder 16 Tech problem 4 4 5 5 5 5 4.25 Darego 9 22103 1/5/2024 Shanahan 21 21 Cancellation 4 4 4 4.00 Shanahan 22246 10 22246 1/6/2024 Bruno 12 How to 4 5 3 4 4.00 Thanh 7 11 22137 1/7/2024 Thanh 7 Feature Feature request 3 3 3.00 Volmann 12 22234 1/2/2024 Shana 22234 1/7/2024 Shanahan 13 23228 23220 1/7/2024 Wagner #/7/2024 Wayne! 14 23215 1/8/2024 Calder 15 24117 1/9/2024 Darego 16 24140 1/9/2024 Bruno 25224 1/11/2024 Thanh 23126 1/12/2024 Volmann 25210 1/12/2024 Barry 20189 1/14/2024 Wagner 20120 1/14/2024 Shand 20126 1/14/2024 Shanahan 26157 1/15/2024 Bruno 128129322222 Bug report 2 1 1 1.25 Wagner Bug report 3 1 2.00 Zimmerman 2222222ND 20 3.65 20 3.35 15 3.60 13 3.71 12 3.25 26 3.41 9 3.89 7 17 3.04 3.46 Feature request 4 3 3.00 25 Cancellation 5 4.50 Feature request 3 3 2.75 Bug report 4 4.25 Bug report 5 5 4.75 How to 3 2.00 Cancellation 1 1 1.00 20 Tech problem 3.25 Cance Cancellation 2 3 2 2.25 25201 1/16/2024 Thanh 16 Tech problem 4 4 3.50 26149 1/16/2024 Calder 19 Tech problem 5 4 5 5 4.75 23223 1/16/2024 Calder 3 Bug report 5 5 5 5 5.00 24241 1/16/2001 Prin Tech problem 2 2.00 Documentation Customer Call Log Call Type by Rep Scores by Date Survey Questions (+ Final Figure 2: Call Type by Rep Worksheet P A Pierce Software B с D E F G H I ] K L 2 Call Types by Service Rep 3 Count of Call Type Column Labels ▾ ☑ Bruno Calder Darego Shanahan Thanh Volmann Wagner Zimmerman Grand Total 4 Row Labels Barry 5 Bug report 5 5 4 4 1 7 2 1 6 35 6 Cancellation 3 4 3 1 3 2 2 2 20 7 Feature request 3 3 2 1 1 5 1 16 8 How to 6 1 1 1 1 2 2 3 17 9 Tech problem 3 7 5 6 6 10 3 3 8 51 10 Grand Total 20 20 15 13 12 26 9 7 17 139 11 12 13 14 15 16 17 18 19 Documentation Customer Call Log Call Type by Rep Scores by Date Survey Questions + ་ CENGAGE New Perspectives Excel 365/2021 | Module 7: End of Module Project 1 Final Figure 3: Scores by Date Worksheet A B C D E F G 1 Pierce Software 2 Average Monthly Scores 3 Average Scores 4 Months Call Type XX + Feb Grand Total 5 Service Reps 6 Barry 7 Bruno 4.50 4.50 4.25 4.25 8 Calder 3.63 3.63 9 Darego 3.33 3.33 Bug report Cancellation Feature request How to 10 Shanahan 3.08 3.08 11 Thanh 3.67 3.67 Tech problem 12 Volmann 4.00 4.00 13 Wagner 2.75 2.75 14 Zimmerman 2.88 2.88 15 Grand Total 3.46 3.46 16 Date 17 Feb 2024 MONTHS ▾ 18 2024 19 22222 23 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 20 21 24 Call Type by Rep Scores by Date Survey CENGAGE New Perspectives Excel 365/2021 | Module 7: End of Module Project 1 Final Figure 4: Survey Questions Worksheet A B C Pierce Software 2 Call Type Lookup Table 3 Call Code 4 Call Type 5 0 6 Bug report Feature request 6 Customer Survey Scores Very satisfied D E F G 11 16 How to Tech problem 21 Cancellation 7 5 8 4 Satisfied 9 3 Neutral 10 2 Dissatisfied 11 1 12 Very dissatisfied 13 Customer Survey Questions 14 How satisfied are you with: 16 Question 2 15 Question 1 The service rep's product knowledge How much time it took to reach a service rep The overall process of contacting Pierce Software How the service rep handled your call 17 Question 3 18 Question 4 19 20 21 22 Documentation Customer Call Log Call Type by Rep Scores by Date Survey Questions CENGAGESee Answer
Q20: CIS 2200 INTRODUCTION TO INFORMATION SYSTEMS AND TECHNOLOGIES Excel Group Project Introduction "NYP," New York Paper Co., is using its financial results for August 2019 (Table 1) as a base for projecting the company's budget for the remaining four months of the year (September-December). Table 1: Financial results for August 2019 (000) REVENUE Wedding Invitations Birthday Party Invitations Business Party Invitations Total Sales Revenue EXPENSES Printing Costs Salaries Administration Advertising Legal Fees Shipping Total Expenses Net Income before Taxes August 4,507 3,841 2,652 11,000 4,400 3,500 550 1,544 788 928 11,710 -710 Taxes Net Profit (loss) 0 -710 Enter the figures for August of 2019 in your worksheet "BUDGET” in the workbook "GROUP ## EXCEL GROUP PROJECT SPRING 24". Make sure that you use formulas where appropriate for this base month. All figures should be expressed in thousands of dollars (000), and amounts should be formatted with commas and no decimals. Assumptions/Assignments Following are the assumptions/assignments about how expenses relate to revenues and the growth assumptions for the next four months. These assumptions/assignments are unique for each group. Using wrong assumptions/assignments will cause a zero 0 grade for the project. Table 2 below presents a partial example of the assignments. The complete table is found in the worksheet "GROUP ASSIGNMENTS" in the workbook "GROUP ## EXCEL GROUP PROJECT SPRING 24". Table 2 Group Assignments Group Wedding Invitations Birthday Party Invitations Business Party Invitations Advertising Legal Fees Shipping December Goal Increase # A B C D E F G 1 5.44 8.42 3.06 4.19 4.41 6.96 20.54 2 5.03 7.15 4.73 6.60 2.89 5.96 20.20 Projected Growth Per Month1 Use VLOOKUP to copy the group's individual assignments/growth assumptions to your worksheet. Place them all together in the lower left corner of your "BUDGET" worksheet with a boxed outline. Table 3 Individual Assignments Projected Growth Per Month % Wedding Invitations 4.51 Birthday Party Invitations 8.65 Business Party Invitations 4.11 Advertising 4.99 Legal Fees 2.80 Shipping December Increase Goal 6.74 20.08 1. Sales revenue from wedding invitations, birthday party invitations, and business party invitations will grow at A%, B%, and C% per month, respectively. 2. Printing costs are calculated as 40% of the Sales Revenue for the month, and Administration expenses are calculated as 5% of the Sales Revenue for the month. 3. Salaries are fixed for the period. Please create an assumption table. 4. The other expense items will grow at the following rates (per month): Advertising will grow at D% per month, Legal Fees will grow at E% per month, and Shipping will grow at F% per month. 5. The tax rate is 28% on profits for the month. Assume that taxes are calculated and paid each month. Note that the company does not pay taxes when it loses money. 6. Conditional Formatting. If any of the numbers for Net Income before Taxes, Taxes, and Net Profit (loss) is greater than 0, then the font for that cell should be green. If any of the numbers for Net Income before Taxes, Taxes, and Net Profit (loss) is less than 0, then the font for that cell should be red; otherwise, the font for that cell should be orange. 1 For example: If the August expense was $100 and the projected growth is 5% per month than the projected September expense will be $100 x 1.05 =$105; projected October expense will be $105 x 1.05 =$110.25; projected November expense will be $110.25 x 1.05 =$115.76; and December expense will be $115.76 x 1.05=121.55. There are several parts to this project: A. Forecast the budget for the next four months of 2019 (Sept-Dec) in a worksheet "BUDGET” in the workbook “GROUP ## EXCEL GROUP PROJECT SPRING 24". Include a footer on this sheet with the names of all group members. B. Create a trend chart showing the trend over the entire period in total sales revenue, total expenses and net profits. Be sure to select the right graph type and label the chart in order to indicate that you are displaying the results in thousands of dollars. Save the chart in a worksheet "BUDGET" in the workbook "EXCEL GROUP PROJECT SPRING 24”. The chart should not be "embedded" (which looks copied with gridlines in the background), but on a new sheet clearly, large and presentable with all proper labels. Similar to the one below. 16,000 14,000 Trends in total Sales, Revenues, Expenses, and Net Profits 13,895 12.641 12,331 19,050 11,710 11,000 12,000 10,000 8,000 6,000 4,000 2,000 TOTAL ($) IN THOUSANDS 0 -2,000 August September Plot Area October MONTH TOTAL SALES REVENUES -TOTAL EXPENSES ■Net Profit (loss) November December C. Create a pie chart showing the proportional distribution of expenses in December. Be sure to label the chart well. Save the chart in a worksheet "DISTRIBUTION" in the workbook "GROUP ## EXCEL GROUP PROJECT SPRING 24". The chart should not be "embedded" (which looks copied with gridlines in the background), but on a new sheet clearly, large, and presentable with all proper labels similar to the one below: DISTRIBUTION OF EXPENSES IN THOUSANDS IN DECEMBER 2019 Legal Fees $880 6% Advertising $1,876 14% Administration $692 5% Shipping $1,205 9% Salaries $3,500 26% Printing Costs $5,538 40% Chart Area D. After you finished the budget calculations copy the entire worksheet "BUDGET" to a new worksheet "SOLVER". To copy OPEN the PASTE option. When it opens up click the icon in the top row, second from the left. Paste Paste Values 园 L123 Other Paste Options Paste Special... Your worksheet "SOLVER" should look like: A B Table 1: Financial results for August 2019 (000) D E F G REVENUE August September October November December 1 Wedding Invitations 4,507 4,704 4,910 5,126 5,350 2 Birthday Party Invitations 3,841 4,206 4,606 5,044 5,524 3 Business Party Invitations 2,652 2,780 2,913 3,053 3,200 4 TOTAL SALES REVENUES 11,000 11,690 12,430 13,223 14,074 5 EXPENSES 6 Printing Costs 4,400 4,676 4,972 5,289 5,630 7 Salaries 3,500 3,500 3,500 3,500 3,500 8 Administration 550 585 622 661 704 Advertising 1,544 1,609 1,677 1,747 1,821 10 Legal Fees 788 826 865 907 950 11 Shipping 928 978 1,030 1,085 1,143 12 TOTAL EXPENSES 11,710 12,173 12,665 13,190 13,748 13 Net Income before Taxes -710 -483 -235 34 327 14 Taxes 0 0 0 9 91 14 Net Profit (loss) -710 -483 -235 24 235 # PROJECTED GROWTH PER MO % 2 Wedding Invitations 4.38 3 Birthday Party Invitations 9.51 4 Business Party Invitations 4.81 5 Advertising 6| Legal Fees 4.21 4.79 SOLVER GOAL 7 Shipping 8 December Increase Goal 5.35 19.85 281.93 Use the NET Profit value for December (cell G20) and calculate your objective value for improved December result. The increase % for your group is in column G (see Table 2 Group Assignment above). Store the desired value in cell D30. Use Excel Solver to manipulate projected growth per month (cells C24:C29) to achieve the December goal calculated and stored in cell D30. Save the Solver solution.See Answer

Get Instant Excel Solutions From TutorBin App Now!

Get personalized homework help in your pocket! Enjoy your $20 reward upon registration!

Download on the
App Store

Download on the
Google Play

Claim Your Offer

Moneyback

Guarantee

Original Work

Reports

$20 reward

Upon registration

Full Privacy

Unlimited

Rewrites/revisions

Testimonials

TutorBin has got more than 3k positive ratings from our users around the world. Some of the students and teachers were greatly helped by TutorBin.

"After using their service, I decided to return back to them whenever I need their assistance. They will never disappoint you and craft the perfect homework for you after carrying out extensive research. It will surely amp up your performance and you will soon outperform your peers."

Olivia

"Ever since I started using this service, my life became easy. Now I have plenty of time to immerse myself in more important tasks viz., preparing for exams. TutorBin went above and beyond my expectations. They provide excellent quality tasks within deadlines. My grades improved exponentially after seeking their assistance."

Gloria

"They are amazing. I sought their help with my art assignment and the answers they provided were unique and devoid of original work. They really helped me get into the good books of my professor. I would highly recommend their service."

Michael

"The service they provide is great. Their answers are unique and expert professionals with a minimum of 5 years of experience work on the assignments. Expect the answers to be of the highest quality and get ready to see your grades soar."

Richard

"They provide excellent assistance. What I loved the most about them is their homework help. They are available around the clock and work until you derive complete satisfaction. If you decide to use their service, expect a positive disconfirmation of expectations."

Willow

TutorBin helping students around the globe

TutorBin believes that distance should never be a barrier to learning. Over 500000+ orders and 100000+ happy customers explain TutorBin has become the name that keeps learning fun in the UK, USA, Canada, Australia, Singapore, and UAE.