Category: Excel

Need help with this excel homework that will be posted today at 5pm est

Instructions:

This assignment is designed to cover the materials that are presented in lectures and labs. The assignment is administered online via eConcordia. You will need uCertify, EXCEL, EXCEL Add-Ins, and/or Power BI to do the assignments.

• You can save your assignments and work on it before the due date. You must submit your assignments on eConcordia once you do not need to change any of your answers.
• The Assignment due dates will not be extended under any circumstances.
• Only submitted files on eConcordia will be graded. (No email submission)
• This Assignment covers all topics covered in sessions 4, 5, and 6.

Access and Submit the Assignment:

To access and submit the assignment:

1. To start the quiz, select the Attempt quiz button.
2. Enter your answers. Note: The system saves your answers as your progress through the quiz.
3. You can leave the quiz, work on your assignment and return back to it as long as it is before the due date.
4. Some questions will ask you to upload the files that you have worked on to complete the assignment.
5. Once completed and ready to submit your answers, select the Finish attempt button. Verify the Summary of attempt page to ensure you have answered all the questions.
6. Select the Submit all and finish button.

After the close time, the quiz will save and submit automatically if not already submitted.

If you encounter any technical issues (e.g., internet connectivity) while taking the quiz, contact eConcordia at and copy your instructor.

Please note that the system does not accept commas “,” or letters in questions with a numeric answer and will mark your answer as incomplete if you enter them in your answer.

How it is used in data entry:

Entering Data: You can type information like names, numbers, addresses, etc., into cells.

Organizing Data: Data is arranged in tables, making it easy to read and manage.

Using Formulas: Excel allows calculations using formulas (e.g., SUM, AVERAGE).

Sorting & Filtering: Helps to quickly find specific data or arrange it in order.

Data Validation: Ensures correct data is entered (e.g., only numbers, limited options).

Editing & Updating: Easy to correct mistakes or update records.

Hi,

I have four Excel assignment files. Each has specific instructions. Ill upload them, and I need you to follow the steps each assignment asks, things like frequency tables, histograms, descriptive stats, and correlations, then complete the final worksheets for each.

Hi,

I have four Excel assignment files. Each has specific instructions. Ill upload them, and I need you to follow the steps each assignment asks, things like frequency tables, histograms, descriptive stats, and correlations, then complete the final worksheets for each.

Your task is to build a simple loan analysis model by selecting different payment plans and calculating the corresponding payment details.

This assignment evaluates your ability to:

• use Data Validation to control user input
• apply XLOOKUP to retrieve data from a master table
• use financial functions such as PMT correctly
• ensure consistency between rate and payment periods
• perform basic calculations using cell references
• organize inputs and outputs clearly in a structured format

All necessary details will be provided. Kindly use Excel STAT to complete this assignment.

Instructions attached seperatly as well to view it better!

• Bao Sang is a project assistant for the Five Points Group, a management consulting company in Chicago, Illinois. She tracks the consultants’ projects and billings in an Excel workbook and asks for your help in updating and analyzing the data.
  Go to the Kang Projects worksheet, which lists project data for Kendra Kang. Bao asks you to format the data as follows to match the data for the other consultants:
  • Format the range B3:G13 as an Excel table with headers using the Gold, Table Style Medium 5 table style.
  • Use Kang as the name of the table.
• Bao wants you to add a new project for Kendra Kang. Add the record shown in Table 1 to the end of the Kang table.
• She also wants you to focus on Kendra Kang’s projects with contract totals over $600,000.
  Use a custom Number filter to display only projects with contract totals greater than 600,000.
• Go to the Valles Projects worksheet, which contains a table named Valles for Gabriel Valles’ consulting projects. Unfreeze the panes in the worksheet because the worksheet is not long or wide enough to scroll.
• Bao asks you to organize the projects by project type and contract amount and to display the average amount of contracts and billings. Modify the Valles table as follows:
  • Remove the filter from the Valles table to display all of the data.
  • Sort the data in ascending order first by the Project Type field and then in descending order by the Contract Total field.
  • Insert a Total row in the Valles table.
  • Use the Total row to calculate the average contract total and average billed to date amounts.
  • In cell B12, change the text to Average to indicate what the amounts represent.
• Go to the Wasem Projects worksheet. Hania Wasem manages more projects than any other consultant. Bao wants you to summarize the Wasem project data using subtotals to show the value of the projects in each industry. Modify the Wasem project data as follows:
  • Sort the Wasem table in ascending order by the Industry field.
  • Convert the table to a normal range.
  • Insert subtotals into the range B3:G18, with the subtotals appearing at each change in the Industry column value.
  • Add subtotals using the SUM function to the Contract Total and Billed to Date columns.
• Go to the Yonas Projects worksheet. The Yonas table on this worksheet lists consulting projects for Ezra Yonas. Format the table to match others in the workbook as follows:
  • Apply the Gold, Table Style Medium 5 table style to the Yonas table.
  • Display the filter buttons.
• Bao knows that the Yonas table includes a duplicate record and asks you to remove it.
  Use a table tool to remove the duplicate record based on the values in all the columns in the table.
• Go to the All Consultants worksheet, which contains a table named Consultants listing data for all Five Points Group consultants.
  Freeze the first three rows of the worksheet to display the worksheet title and column headings when the worksheet is scrolled.
• Bao asks you to calculate the difference between the contract totals and billed to date amounts. Modify the Consultants table as follows:
  • Add a table column to the end of the Consultants table. (Hint: Ensure that a blank column remains between the table and the content to the right of the table.)
  • Use Amount Due as the column heading.
  • Apply a middle vertical alignment to the column heading and use AutoFit to adjust the width of the column.
  • In cell H4, enter a formula using structured references and no function to subtract the value in the [Billed to Date] column from the value in the [Contract Total] column.
  • Fill the range H5:H40 with the formula in cell H4 if Excel does not automatically do so.
• She also wants you to make the following calculations in the range K4:K6:
  • In cell K4, use the COUNTA function with a structured reference to count the values in the [Project ID] column of the Consultants table.
  • In cell K5, use the SUM function with a structured reference to total the values in the [Contract Total] column of the Consultants table.
  • In cell K6, use the SUM function with a structured reference to total the values in the [Billed to Date] column of the Consultants table.
• Bao wants you to make it easy to filter the Consultants table based on the project type and location. Add slicers to the table as follows:
  • Insert two slicers, one based on the Project Type field and the other based on the Location field in the Consultants table.
  • Apply the slicer style Light Yellow, Slicer Style Light 4 to the two slicers.
  • Resize and reposition the Project Type slicer so it covers the range J8:K14 (Hint: Gridlines can be applied to help with positioning by selecting the View tab in the ribbon, clicking Show Grouping, and checking the gridlines).
  • Resize and reposition the Location slicer so it covers the range J16:K28.
• Remove the filter buttons from the Consultants table because you can now use the slicers to filter the data.

PROJECT STEPS

• Bao Sang is a project assistant for the Five Points Group, a management consulting company in Chicago, Illinois. She tracks the consultants’ projects and billings in an Excel workbook and asks for your help in updating and analyzing the data.
  Go to the Kang Projects worksheet, which lists project data for Kendra Kang. Bao asks you to format the data as follows to match the data for the other consultants:
  • Format the range B3:G13 as an Excel table with headers using the Gold, Table Style Medium 5 table style.
  • Use Kang as the name of the table.
• Bao wants you to add a new project for Kendra Kang. Add the record shown in Table 1 to the end of the Kang table.
• She also wants you to focus on Kendra Kang’s projects with contract totals over $600,000.
  Use a custom Number filter to display only projects with contract totals greater than 600,000.
• Go to the Valles Projects worksheet, which contains a table named Valles for Gabriel Valles’ consulting projects. Unfreeze the panes in the worksheet because the worksheet is not long or wide enough to scroll.
• Bao asks you to organize the projects by project type and contract amount and to display the average amount of contracts and billings. Modify the Valles table as follows:
  • Remove the filter from the Valles table to display all of the data.
  • Sort the data in ascending order first by the Project Type field and then in descending order by the Contract Total field.
  • Insert a Total row in the Valles table.
  • Use the Total row to calculate the average contract total and average billed to date amounts.
  • In cell B12, change the text to Average to indicate what the amounts represent.
• Go to the Wasem Projects worksheet. Hania Wasem manages more projects than any other consultant. Bao wants you to summarize the Wasem project data using subtotals to show the value of the projects in each industry. Modify the Wasem project data as follows:
  • Sort the Wasem table in ascending order by the Industry field.
  • Convert the table to a normal range.
  • Insert subtotals into the range B3:G18, with the subtotals appearing at each change in the Industry column value.
  • Add subtotals using the SUM function to the Contract Total and Billed to Date columns.
• Go to the Yonas Projects worksheet. The Yonas table on this worksheet lists consulting projects for Ezra Yonas. Format the table to match others in the workbook as follows:
  • Apply the Gold, Table Style Medium 5 table style to the Yonas table.
  • Display the filter buttons.
• Bao knows that the Yonas table includes a duplicate record and asks you to remove it.
  Use a table tool to remove the duplicate record based on the values in all the columns in the table.
• Go to the All Consultants worksheet, which contains a table named Consultants listing data for all Five Points Group consultants.
  Freeze the first three rows of the worksheet to display the worksheet title and column headings when the worksheet is scrolled.
• Bao asks you to calculate the difference between the contract totals and billed to date amounts. Modify the Consultants table as follows:
  • Add a table column to the end of the Consultants table. (Hint: Ensure that a blank column remains between the table and the content to the right of the table.)
  • Use Amount Due as the column heading.
  • Apply a middle vertical alignment to the column heading and use AutoFit to adjust the width of the column.
  • In cell H4, enter a formula using structured references and no function to subtract the value in the [Billed to Date] column from the value in the [Contract Total] column.
  • Fill the range H5:H40 with the formula in cell H4 if Excel does not automatically do so.
• She also wants you to make the following calculations in the range K4:K6:
  • In cell K4, use the COUNTA function with a structured reference to count the values in the [Project ID] column of the Consultants table.
  • In cell K5, use the SUM function with a structured reference to total the values in the [Contract Total] column of the Consultants table.
  • In cell K6, use the SUM function with a structured reference to total the values in the [Billed to Date] column of the Consultants table.
• Bao wants you to make it easy to filter the Consultants table based on the project type and location. Add slicers to the table as follows:
  • Insert two slicers, one based on the Project Type field and the other based on the Location field in the Consultants table.
  • Apply the slicer style Light Yellow, Slicer Style Light 4 to the two slicers.
  • Resize and reposition the Project Type slicer so it covers the range J8:K14 (Hint: Gridlines can be applied to help with positioning by selecting the View tab in the ribbon, clicking Show Grouping, and checking the gridlines).
  • Resize and reposition the Location slicer so it covers the range J16:K28.
• Remove the filter buttons from the Consultants table because you can now use the slicers to filter the data.

Table 1: New Record for the Kang Table

All necessary details will be given.