Select data label options to   display percentage and category name.

Exp22_Excel_Ch03_ML2_Grades
Exp22 Excel Ch03 ML2 Grades
Excel Chapter 3 Mid-Level 2 – Grade Analysis 
  
Project Description:
As Dr. Dae Jeong’s teaching assistant, you maintain their 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.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch03_ML2_Grades.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

 
 
A pie chart is an effective way   to visually illustrate the percentage of the class that earned A, B, C, D,   and F grades.
 
  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.
 
You should enter a chart title   to describe the purpose of the chart. You will customize the pie chart to   focus on particular slices.
 
  •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.
 
A best practice is to add Alt   Text for accessibility compliance.
 
  Add Alt Text: Most students earned B and C grades. (including the period).
 
You want to add data labels to   indicate the category and percentage of the class that earned each letter   grade.
 
  Add data labels in the Inside End position. Select data label options to   display Percentage and Category Name. Remove the Values data labels.

 
 
Apply 18-pt size and apply   Black, Text 1 font color to the data labels.
 
You want to create a bar chart   to depict grades for a sample of the students in the class.
 
  Create a clustered bar chart using the ranges A5:D5 and A18:D23 in the Grades   worksheet. Move the bar chart to its own sheet named Sample Scores.
 
Customize the bar chart with   these specifications:
  · Style 5 chart style
  · Legend on the right side in 11 pt font size
  · Light Gradient – Accent 2 fill color for the plot area

 
 
The chart needs a descriptive   title.
 
  Type Sample   Student Test Scores for   the chart title.
 
Displaying the exact scores   would help clarify the data in the chart.
 
  Add data labels in the Outside End position for all data series. Format the   Final Exam data series with Blue-Gray, Text 2 fill color.
 
 
The student names display in   reverse alphabetical order on the category axis. You want to display the   names in alphabetical order, format the axis, and add Alt Text.
 
  Select the category axis and display the categories in reverse order 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.
 
  Select the category axis and change the font size to 12.
 
  Add Alt Text: Quinn earned the highest scores on all tests. (including the period).
 
You want to create a scatter   chart to see if the combination of attendance and final averages are related.
 
  Display the Grades worksheet. Select the range E5:F31 and create a scatter   chart. Cut the chart and paste it in cell A42. Set a height of 5.96″ and a width of 5.5″.
 
Add Alt Text: The scatter   chart shows the relationship between final grades and attendance. (including the period).
 
Titles will help people   understand what is being plotted in the horizontal and vertical axes, as well   as the overall chart purpose.
 
  Make sure the scatter chart is selected. Type Final Average-Attendance Relationship as the chart title and bold the   title. Type Percentage of Attendance as the primary horizontal axis   title, and type Student Final Averages as the primary vertical axis   title.
 
To distinguish the points   better, you can start the plotting at 40 rather than 0. Make sure the scatter   chart is selected.
 
  Apply these settings to the vertical axis of the scatter chart:
40 minimum bound, 100 maximum bound, 10 major units, and Number format   with 0 decimal places.
 
Make sure the scatter chart is   selected. Apply these settings to the horizontal axis: 40 minimum bound, 100 maximum bound, automatic units.   

 
 
Adding a fill to the plot area   will add a touch of color to the chart.
 
  Make sure the scatter chart is selected. Add the Parchment texture fill to   the plot area.

 
 
You want to insert a trendline   to determine trends.
 
  Make sure the scatter chart is selected and insert a linear trendline chart   element.

 
 
You want to add sparklines to   detect trends for each student.
 
  Insert Column sparklines in the range H5:H31 using data in the range B6:D31   on the Grades sheet. Display the Low Point. Set the Vertical Axis Minimum and   Maximum Values to be the same for all sparklines.
 
To make the Sparklines more   effective and easier to read, you will increase the row height.
 
  Change the row height to 22 for rows 6 through 31.

 
 
Save and close Exp22_Excel_Ch03_ML2_Grades.xlsx. Exit   Excel. Submit the file as directed.

Select the dates and select text   axis to remove the extra dates from the line chart.

Exp22_Excel_Ch03_Cumulative_Patient
Exp22 Excel Ch03 Cumulative Patient
Excel Chapter 3 Cumulative – Blood Pressure Monitoring 
  
Project Description:
You work as a nurse in a health clinic. You record each patient’s systolic and diastolic blood pressures each week. A number of patients have requested blood pressure data and charts. This information helps patients know if they have hypertension, caused by high blood pressure. You prepared a spreadsheet that can be used as a template to track a patient’s blood pressure and heart rate. You are ready to create charts that you can use when discussing a patient’s blood pressure.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch03_Cumulative_Patient.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

 
 
Weekly blood pressure readings   have been entered for a sample patient. You want to create a clustered column   chart to compare the readings for the month. After you create the chart, you   will move it to a Charts sheet that will be used to contain several charts   for easy reference.
 
  Create a clustered column chart using the range A2:C7 on the April worksheet.   Move the chart to cell A1 in the Charts sheet.
 
The chart needs a descriptive,   easy-to-read title.
 
  Change the chart title to Blood Pressure Readings in April and bold the title.
 
You realize the chart might be   easier to read as a bar chart.
 
  Change the chart type to Clustered Bar.
 
You will increase the chart size   to make it easier to read on a display screen.
 
  Set 4″ height. 
 
You want to remove the   additional dates displayed on the category axis.
 
  Select the dates on the axis and display the Format Axis task pane. In the   Axis Options category, select Text axis to remove the extra dates from the   chart.
 
You want to arrange the dates   with the oldest date at the top.
 
  Display the dates in reverse order on the category axis so that 4/1/2024 is   at the top of the vertical axis.
 
Next, you will create a line   chart depicting the heart rate readings for the month. This chart will help   the patient see trends over time.
 
  Create a line chart using the ranges A3:A7 and D3:D7 on the April worksheet.   Move the line chart to start in cell I1 in the Charts sheet.
 
You decide to adjust the size of   the line chart.
 
  Set 3.5″ height and 5″ width.
 
Change the line chart title to Heart Rates   in April and   bold the title.
 
Select the dates and select Text   axis to remove the extra dates from the line chart.
 
You will change the font color   for the axes to make the chart easier to read.
 
  Apply Black, Text 1 font color to the value axis and to the category axis on   the line chart.
 
Now you want to apply a gradient   fill color to the plot area to soften the image.
 
  Apply Light Gradient – Accent 1 gradient fill color to the plot area on the   line chart.
 
Data labels provide exact   details. In this case, adding data labels will provide exact heart rates.
 
  Add data labels in the above position on the line chart.
 
The April worksheet contains a   range that identifies how many times in the month the patient had normal and   high blood pressures. You decide a pie chart will help the patient visualize   the data better.
 
  Create a 2-D pie chart using the ranges F3:F4 and H3:H4 in the April   worksheet. Move the chart to start in cell A22 in the Charts sheet. 
 
Change the chart title to Percentage of   Normal and High Blood Pressure and apply Black, Text 1 font color to the title.
 
Removing the legend can reduce   extraneous elements in the chart. Adding data labels keep descriptions within   each slice.
 
  Remove the legend. Add Category Name and Percentage data labels in the Best   Fit position. Remove the Value data labels.
 
Changing the color of the high   blood pressure slice and exploding it away from the other slice will   emphasize it.
 
  Apply Red solid fill color to the High (Hypertension) slice and explode the   slice by 6%.
 
The last chart you want to   create is a combo chart to depict the blood pressure readings with the   overlay of heart rates.
 
  Create a Clustered Column – Line on Secondary Axis combo chart using the   range A2:D7 in the April worksheet. Move the chart to a chart sheet named Combo Chart.
 
After creating the chart, you   will apply a chart style.
 
  Apply Style 4 chart style to the combo chart.
 
Change the chart title to Blood   Pressure/Heart Rates.
 
Select the dates and select Text   axis to remove the extra dates from the chart
 
Apply Black, Text 1 font color   and 11 pt font size to the two value axes and the category axis.
 
You decide to add a title to   each axis to describe the data being plotted.
 
  Add a primary vertical axis title Blood Pressure and a secondary vertical axis title Heart Rate.
 
Move the legend to the top   position.
 
You decide to remove one of the   data series.
 
  Edit the data source to remove the 4/1/2024 data series.
 
Now you are ready to add Alt   Text to the chart.
 
  Display the combo chart and add the Alt Text Shows blood pressure and heart rate for   the last four weeks.
 
Finally, you add sparklines to   the blood pressure readings and heart rates below the respective columns.
 
  Select range B8:D8 on the April worksheet. Insert Column Sparklines using   data from the range B3:D7.
 
You want to customize the   sparklines.
 
  Apply Blue sparkline color and display the high points for the sparklines.
 
Save and close Exp22_Excel_Ch03_Cumulative_Patient.xlsx.   Exit Excel. Submit the file as directed.

Exp22_excel_ch03_hoe_jobs exp22 excel ch03 hoe jobs excel chapter 3 hands-on exe

Exp22_Excel_Ch03_HOE_Jobs
Exp22 Excel Ch03 HOE Jobs
Excel Chapter 3 Hands-On Exercise – Computer Job Outlook 
  
Project Description:
You are an academic advisor for the School of Computing at a university. You researched the job outlook that indicated the number of jobs in 2019 and the estimated number of jobs in 2029. You will prepare several charts to help you identify trends.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch03_HOE_Jobs.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

 
 
You want to start by creating a   clustered column chart. You will focus on the first four jobs included in the   list.
 
  In the Outlook sheet, use the Insert tab to create a clustered column chart   for the range A5:D9. Cut the chart and paste it in cell H1. Set 3.5″ height and 5.3″ width for the chart.
 
Next, you want to create a   recommended chart to depict the number of people for each job and the   estimated number of new people by 2029.
 
  In the Outlook sheet, use the Recommended Charts feature to create a   recommended chart for the ranges A5:B12 and D5:D12. Change it to a stacked   bar chart and move it to a chart sheet named Bar Chart. 
 
For the third chart, you want to   see what percentage each job contributes to the total job pool for this   group.
 
  Display the New Jobs worksheet, select the range A4:B9 and use Quick Analysis   to create a pie chart. Move the pie chart to a chart sheet called Pie Chart. Note, Mac users use the ribbon   to create the pie chart.
 
The last chart will depict the   number of new jobs and the related percentage increase. Because the two data   types are different, you will create a combo chart.
 
  In the Outlook sheet, select the ranges A5:A12 and D5:E12, and create a combo   chart with Clustered Column – Line on Secondary Axis. Move the combo chart to   a chart sheet called Combo Chart. 
 
It is important to replace the   default chart titles with actual descriptive chart titles.
 
  With the combo chart selected, type Number of New Jobs by 2029 for the chart title. Apply bold   and Black, Text 1 font color to the chart title.
  Select the pie chart, type New Computer-Related Jobs by 2029 for the chart title. Apply   bold, 18 pt font size, and Black, Text 1 font color to the chart title.
  Select the bar chart, type Projected Number of Jobs by 2029 as the chart title.
  Select the column chart, type Number of Jobs 2019 and 2029 for the chart title. Apply bold   and Dark Blue font color to the chart title.
 
You want to add a value axis   title to clarify the column chart.
 
  With the column chart selected, add a primary horizontal axis title and type Job Titles as the axis title. Apply Dark   Blue font color to the axis title.
 
Adjusting the value axis   settings will make the axis labels easier to read on the bar chart.
 
  Select the bar chart, change the maximum bound for the value axis to 1800000 and display the units in   Thousands. Apply Dark Blue font color to the axis title, Thousands.
 
Showing percentages for pie   slices is important to knowing which job contributes what percentage of the   overall new jobs.
 
  Select the pie chart, add data labels in the Inside End position. Display   Percentage labels. Remove the Value data labels. Apply bold and 18 pt font   size to the data labels.
 
You want to apply a texture fill   to the chart area to create a softer appearance around the pie chart.
 
  Apply the Blue tissue paper texture fill to the chart area on the pie chart.
 
It is a best practice to add Alt   Text for a chart for accessibility compliance.
 
  With the pie chart selected, add Alt Text: Displays the percentage of new computer   jobs by job title. (including   the period).
 
You want to emphasize one job   category with a different fill color and by setting it apart from the rest of   the pie.
 
  Select the Computer Systems Analysts pie slice, apply Red solid fill color,   and explode the slice by 10%.
 
You want to give more contrast   to the bar chart. Therefore, you will apply a different chart style to the   bar chart.
 
  Select the bar chart and apply Style 10.
 
You decide to exclude the number   of new jobs to simplify the column chart.
 
  Select the column chart. Change data source by changing the chart data range   from D to C. Then switch the data series and categories so that the years are   on the category axis.
 
You want to insert a new column   so that you can insert sparklines later.
 
  In the Outlook sheet, insert a new column D. Type Trends in cell D5. 
 
You want to insert sparklines to   show the trends between 2019 and 2029.
 
  Select the range B6:C12 and insert Line sparklines in the range D6:D12.   Display markers on the sparklines and select Dark Blue, Sparkline Style Dark   #4. Change the marker color to Dark Red.
 
Each sparkline has its own start   and stop value. To depict the data more accurately, you will adjust the   minimum and maximum values on the axes.
 
  Customize sparklines by setting the vertical axis minimum value to be the   same for all sparklines and setting the vertical axis maximum value to be the   same for all sparklines. 
 
Save and close Exp22_Excel_Ch03_HOE_Jobs.xlsx. Exit   Excel. Submit the file as directed.

Please go through the below links and write the summary and bullets points for each lecture.

Please go through the below links and write the summary and bullets points for each lecture. Furthermore some of the example from your own has to added in the reading assignment as well.
1. https://www.youtube.com/embed/PySo_6S4ZAg
2. https://www.youtube.com/embed/AwQHqWyHRpU
3. https://www.youtube.com/embed/JUJNGv_sb4Y
4. https://www.youtube.com/embed/ANszao6YQuM
5. https://www.youtube.com/embed/IM9ANAbufYM
6. https://www.youtube.com/embed/G5FNYxbW_Qw
7. https://www.youtube.com/embed/gCJCgQW_LKc
8. https://www.youtube.com/embed/733m6qBH-jI
9. https://www.youtube.com/embed/NP2XqpgTJyo
10. https://www.youtube.com/embed/IFLstgCNOA4

Exp22_excel_ch03_ml1_airports exp22 excel ch03 ml1 airports excel chapter 3 mid-

Exp22_Excel_Ch03_ML1_Airports
Exp22 Excel Ch03 ML1 Airports
Excel Chapter 3 Mid-Level 1 – Airport Passenger Counts  
  
Project Description:
As an analyst for the airline industry, you track the number of passengers at the top six major U.S. airports: Atlanta (ATL), Chicago (ORD), Los Angeles (LAX), Dallas/Fort Worth (DFW), Denver (DEN), and New York (JFK). You researched passenger data and created a worksheet that lists the number of total yearly passengers. You will create a clustered column chart to compare the number of passengers at each airport. Then, you will create a line chart that compares trends over time. Next, you will create a bar chart to compare the passenger count for the latest year of data available and emphasize the airport with the largest number of passenger traffic. Finally, you will insert sparklines to visually represent trends in passengers at each airport over the ten-year period.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch03_ML1_Airports.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

 
 
You want to create a clustered   column chart to depict the passenger counts at the six airports over several   years.
 
  Create a clustered column chart for the range A4:L10. Cut the chart and paste   it in cell A15.

 
 
You want to customize the column   chart with a chart title, display the years as a data series, enlarge the   chart to be easier to read, and apply a chart style.
 
  •Type Passengers   Counts at Top U.S. Airports as the chart title.
  •Swap the data on the category axis and in the legend.
  •Set a 3.5″ height and 11.4″ width.
  •Apply the Style 7 chart style.
 
The value axis takes up a lot of   space for the numbers. You will adjust the value axis to simplify it.
 
  •Change the display units to Millions for the value axis.
  •Edit the axis title to display Millions of Passengers.

 
 
You want to focus on the 2019 data   series by adding data labels.
 
  Display data labels above the columns (Outside End position) for the 2019   data series only.
 
Applying a fill color to the   chart area will make the chart visually appealing.
 
  Apply the Light Gradient – Accent 1 gradient fill to the chart area.

 
 
A best practice is to add Alt   Text to a chart for accessibility compliance.
 
  Add Alt Text: The column chart displays the number of passengers. (including the period).
 
You want to create a bar chart   to display passenger counts for only one year.
 
  Create a clustered bar chart for the range A5:A10 and L5:L10 and move the   chart to a chart sheet named Bar Chart.
 
You want to customize the bar   chart.
 
  •Change the chart color to Monochromatic Palette 5.
  •Enter Passengers   at Top 6 U.S. Airports in 2019 as the chart title.
  •Apply the Style 5 chart style.
  •Add Alt Text: Atlanta had the most passengers. (including the period).
 
Modifying the axes will improve   readability of the bar chart.
 
  •Change the font size to 10 for the category axis and value axis.
  •Change the value axis Maximum Bound to 1.1E8.
 
 
You will format a data point so   that it stands out and then add gridlines to enhance readability in the bar   chart.
 
  •Format the Atlanta data point with Dark Blue fill color.
  •Add Primary Minor Vertical gridlines.

 
 
A line chart effectively shows   trends over time for the passenger counts at the different airports.
 
  Create a line chart using the range A4:L10 in the Passenger worksheet and   move the chart to a chart sheet named Line Chart. Add a chart title Passengers   at U.S. Airports   and bold the title.

 
 
You want to customize the line   chart.
 
  •Set the Minimum Bound to 4.0E7 for the value axis and set the Maximum Bound to 1.1E8.
  •Set the Vertical (Value) Axis Display units to Millions. Delete the Vertical   (Value) Axis Display Units Label from the upper-left corner of the chart. Add   a value axis title In Millions.
  •Change the font size to 10 for the value axis and category axis.
  •Move the legend to the top.
  •Open the Select Data source dialog box and deselect the check boxes for the   even-numbered years. (On a Mac, change the Chart data range by selecting   these ranges: A4:B10,D4:D10,F4:F10,H4:H10,J4:J10,L4:L10 since the dialog box   does not contain check boxes to deselect the years.)
  •Add Alt Text: The line chart displays trends for top six U.S.   airports.   (including the period).
 
Sparklines provide a simple   visualization to represent data in a worksheet.
 
  Display the Passenger worksheet and insert Line sparklines in the range   M5:M10 to illustrate the data in the range B5:L10.
 
 
You want to customize the   sparklines to point out high and low points.
 
  •Show the high and low points in each sparkline.
  •Apply Black, Text 1 color to the high point marker in each sparkline.
 
 
Set page formats for the   Passenger worksheet.
 
  Select Legal paper size, select Landscape orientation, set 0.3″ left and right margins, and   scale to fit 1 page.
 
Save and close Exp22_Excel_Ch03_ML1_Airports.xlsx.   Exit Excel. Submit the file as directed.

 exp22_excel_ch02_cumulative – garten furniture   exp22 excel ch02 cumulative –

 Exp22_Excel_Ch02_Cumulative – Garten Furniture 
 Exp22 Excel Ch02 Cumulative – Garten Furniture 
 Excel Chapter 2 Cumulative – Furniture Sales  
  
Project Description:
You are a finance manager for Garten furniture, a local furniture store that also provides financing, delivery, and hands free setup. As part of your daily tasks, you create an Excel workbook that reports sales, payment plan information, and summary statistics.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch02_Cumulative_Furniture_Sales.xlsx.   Grader has automatically added your last name to the beginning of the   filename.
 
Insert a function in cell C3 to   display the current date and ensure that the format is set as a Long Date.
 
Set column C’s width to Autofit.
 
Insert a function in cell D11 to   display the item name based on the provided inventory lookup information in   the range B24:D29. Be sure to use the appropriate absolute cell referencing   and the function returns an exact match. Copy the function from cell D11 down   through D19 to complete column D.
 
Insert a function in cell F11 to   display the item price based on the provided inventory lookup information. Be   sure to use appropriate absolute cell referencing and the function returns an   exact match. Copy the function from cell F11 down through F19 to complete   column F.
 
Apply Currency Format to column   F.
 
Insert an IF function in cell   G11 to calculate the total due. If the customer has chosen delivery (column E   = Yes), there is an additional   delivery charge located in cell L11. If the item is not delivered, the total   due is price. Be sure to use appropriate relative and absolute cell   references. Copy the function from cell G11 down through G19 to complete   column G.
 
Apply Currency format to column   G.
 
Insert the function in cell I11   to calculate the first customer’s monthly payment, using appropriate relative   and absolute cell references. Place a minus sign to the left of the present   value argument so that the function returns a positive result. Copy the   formula down the column.
 
Use Quick Analysis to insert a   function in cell I20 that calculates the total of all monthly payments in   column I.
 
Apply Currency Format to column   I.
 
Type the customer name McGowan in cell B6. 
 
Use the XLOOKUP function in cell   C6 to lookup the customer name in the worksheet and return the corresponding   SKU, item name, delivery option, price, total due, years financed, and   monthly payment.
 
Use Format Painter to copy the   format from the range C11:I11 to C6:I6.
 
Insert a function in cell H24 to   calculate the total number of orders in the SKU column.
 
Insert a function in cell H25 to   calculate the lowest monthly payment in column I.
 
Insert a function in cell H26 to   calculate the average monthly payment in column I.
 
Insert a function in cell H27 to   calculate the highest monthly payment in column I.
 
Insert a function in cell H28 to   calculate the median monthly payment in column I.
 
Apply Currency format to the   range H25:H28.
 
Save and close Exp22_Excel_Ch02_Cumulative_Furniture_Sales.xlsx.   Exit Excel. Submit the file as directed

Exp22_excel_ch02_ml1 – katherine’s coffee shop weekly payroll exp22 excel ch02 m

Exp22_Excel_Ch02_ML1 – Katherine’s Coffee Shop Weekly Payroll
Exp22 Excel Ch02 ML1 – Katherine’s Coffee Shop Weekly Payroll
Excel Chapter 2 Mid-Level 1 – Payroll 
  
Project Description:
You have been hired as the new manager of Katherine’s Coffee shop. As part of your responsibilities, you will be calculating the weekly payroll. The previous manager developed a partial worksheet, but you will enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to include total pay columns, calculate some basic statistics, and create an employee lookup section. As you construct formulas, make sure you use absolute, relative, and mixed cell references correctly in formulas.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch02_ML1_Payroll.xlsx.   Grader has automatically added your last name to the beginning of the   filename. 
 
Use IF functions to calculate   the regular pay and overtime pay in columns E and F based on a regular   40-hour workweek. Be sure to use the appropriate absolute or mixed cell   references. Pay overtime only for overtime hours. Note employees receive 1.5   their hourly wage for overtime hours worked. Calculate the gross pay in cell   G5 based on the regular and overtime pay. Spencer’s regular pay is $440. With   five overtime hours, Spencer’s overtime pay is $82.50.
 
Create a formula in cell H5 to   calculate the taxable pay. Multiply the number of dependents by the deduction   per dependent and subtract that from the gross pay. With two dependents,   Spencer’s taxable pay is $422.50.
 
Insert a VLOOKUP function in   cell I5 to identify and calculate the federal withholding tax. With a taxable   pay of $422.50, Spencer’s tax rate is 25% and the withholding tax is $105.63.   The VLOOKUP function returns the applicable tax rate, which you must then   multiply by the taxable pay.
 
Calculate FICA in cell J5 based   on gross pay and the FICA rate, and calculate the net pay in cell K5. Copy   all formulas down their respective columns. Be sure to preserve the existing   formatting in the document. Based on the hours Spencer worked he paid $39.97   to FICA and had a weekly net pay of $376.90
 
Use Quick Analysis tools to   calculate the total regular pay, overtime pay, gross pay, taxable pay,   withholding tax, FICA, and net pay on row 17. (On a Mac, this step must be   completed using the AutoSum feature on the ribbon.)
 
Apply Accounting Number Format   to the range C5:C16. Apply Accounting Number Format to the first row of   monetary data and to the total row. Apply the Comma style to the monetary   values for the other employees. 
 
Insert appropriate functions to   calculate the average, highest, and lowest values in the Summary Statistics   area (the range I24:K26) of the worksheet. Format the # of hours calculations   as Number format with one decimal and the remaining calculations with   Accounting Number Format.
 
Use the XLOOKUP function to look   up the employee name in cell A20 (Wagner) in the payroll data and return the   specified information in row 20. Ensure the return array includes overtime   pay, gross pay, taxable pay, federal tax, FICA and net pay. 
 
Save and close the workbook.   Submit the file as directed. 

Write an if function in cell d9   that calculates the down payment for the mortgage.

Exp22_Excel_Ch02_ML2 – Vacation Property 1.1
Exp22 Excel Ch02 ML2 – Vacation Property 1.1
Excel Chapter 2 Mid-Level 2 – Vacation Property 
  
Project Description:
You have been hired as a finance manager at Granger vacation homes, a regional construction company that finances and builds vacation properties. As part of your responsibilities you manage mortgage applications. Your predecessor left a partially created document you will use to calculate various details about each property being financed. You will use the VLOOKUP function to help determine mortgage interest rates, IF statements to calculate down payment, various statistical calculations, and XLOOKUP to create a quick search within the document.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch02_ML2_Vacation   Property.xlsx. Grader has automatically added your last name to the   beginning of the filename. 
 
Write an IF function in cell D9   that calculates the down payment for the mortgage. The down payment is 15% of   the purchase price if the customer is considered high risk or 5% of the   purchase price if considered low risk. The criteria to assess the risk of the   loan is based on the credit score value in cell B6. Be sure to use the appropriate   absolute or mixed cell references when referencing the input values in the   range B4:B6. Then use the fill handle to copy the formula down, stopping in   cell D28.
 
Enter a formula to calculate the   percent financed in cell E9. The percent financed is the amount   financed/purchase price. Next use the fill handle to copy the formula down,   stopping cell E28.
 
Enter a formula in cell F9 to   calculate the amount financed. The amount financed is the purchase price-down   payment. After entering the formula, use the fill handle to copy the formula   down, stopping in cell F28.
 
Use the VLOOKUP function in cell   G9 to lookup the mortgage rate based on the customer credit score and the   data in the range D4:E6. Be sure to use the appropriate absolute or mixed   cell references. Use the fill handle to copy the formula down, stopping in   cell G28.
 
Calculate the rate per period in   cell H9. All mortgages being financed will be paid monthly. After completing   the formula, use the fill handle to copy the formula down, stopping in cell   H28.
 
Calculate the total number of   payment periods in cell J9 based on the years financed in cell I9. After   completing the formula, use the fill handle to copy the formula down,   stopping in cell J28.
 
Use the PMT function to   calculate the total monthly payment in cell K10 based on the periodic   interest rate, number of periods, and amount financed. Ensure the results are   a positive value then use the fill handle to copy the function, down stopping   in cell K28.
 
Use Quick Analysis to calculate   the total of all payments in cell K29. (Quick Analysis is not available for   Mac. If using a Mac, use AutoSum to calculate the SUM of the range K9:K28).
 
Use the appropriate statistical   functions in the range B32:B36 to calculate descriptive statistics based on   the row headings in the range A32:B36. In cell B32, calculate the total count   of loans based on column A, lowest payment in cell B33 based on the data in   column K, highest payment in B34, average payment in B35, and median payment   in B36. 
 
Use the XLOOKUP function in cell   E32 to lookup the employee number in cell D32 and return the corresponding   down payment, % finances, and amount financed. 
 
Use the appropriate function to   insert the current date and time in cell B2.
 
Save and close the workbook.   Submit the file as directed.

Select the ranges a5:d13 and   f5:g13 and clear cell contents but keep the formatting in cell f5, type 1.

Excel Chapter 1 Mid-Level 2 – Real Estate Sales Report 
Exp22_Excel_Ch01_ML2_Sales
Exp22 Excel Ch01 ML2 Sales
  
Project Description:
You own a small real estate company in Indianapolis. You track the real estate properties you list for clients. You want to analyze sales for selected properties. Yesterday, you prepared a workbook with a worksheet for recent sales data and another worksheet listing several properties you listed. You want to calculate the number of days that the houses were on the market and their sales percentage of the list price. In one situation, the house was involved in a bidding war between two families that really wanted the house. Therefore, the sale price exceeded the list price.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch01_ML2_Sales.xlsx.   Grader has automatically added your last name to the beginning of the   filename.
 
The owners of the house on 640 North Oak Drive took their house   off the market. You want to delete that row since it did not sell.
 
  In the April worksheet, delete row 10, which has incomplete sales data.
 
The listings are organized by   date listed. The 973 East Mulberry Lane   listing needs to be moved up in the list.
 
  Select and cut row 13 and insert cut cells on row 10.
 
You want to assign a property ID   to each listing. The code will have the year 2024 with a sequential number.
 
  Type 2024-001 in cell A5 and use Auto Fill to   complete the series to assign a property ID to each property in the range   A6:A13.
 
Real estate agents study the   number of days houses are on the market. In some cases, the longer a house is   on the market, it might indicate the asking price is too high.
 
  Enter a formula in cell C5 that calculates the number of days the first house   was on the market by subtracting the date listed from the date sold. Copy the   formula to the range C6:C13.
 
Monetary values look better when   they are formatted to display the dollar signs. Because all monetary values   are whole numbers, you will decrease the decimal points to avoid columns   where all the data ends in .00.
 
  Format the list prices and sold prices in the range F5:G13 with Accounting   Number Format with zero decimal places.
 
To analyze the real estate   market, it is helpful to calculate the selling prices as a percentage of the   list price. For example, the second house was listed for $500,250, but it   sold for only $400,125. Therefore, the sale percentage of the list price is   .7999 or 79.99%.
 
  Enter a formula in cell H5 that calculates the sales price percentage of the   list price for the first house by dividing the sold price by the list price.   Copy the formula to the range H6:H13.
 
The calculated percentages need   to be formatted with the percent symbol rather than displaying the values as   raw numbers.
 
  Format the values in the range H5:H13 with Percent Style with two decimal   places.
 
Currently, the labels on row 4   are hard to read. You will wrap the headings within each cell to improve   readability.
 
  Center horizontally and wrap the headings in the range A4:H4.
 
For better sequencing of   columns, you want to display the Days on Market column after the two columns   containing the dates.
 
  Insert a new column between the Date   Sold and List Price columns.   Move the Days on Market data to the   new blank column F by moving the range C4:C13 to F4:F13. Delete column C.
 
You notice the list contains a   wrong date and wrong list price that need to be corrected.
 
  Edit the date sold of the 41 Chestnut   Circle house to be 4/22/2024.
 
Increasing the height of the   rows of data and centering the data vertically between the top and bottom   cell margins will make it easier for the other agents to review the data.
 
  Select the property rows (rows 5:13), set a 25 row height, and apply Middle Align.
 
Displaying borders helps separate   the property listings.
 
  Apply the All Borders border style to the range A4:H13.

 
 
You want to improve the   alignment of number of days on market below the column label.
 
  Apply Align Right and increase the indent two times on the days on market formula   results in the range E5:E13.
 
You noticed some misspelled   words in the worksheet.
 
  Use Excel to check the spelling and correct misspelled words.
 
You don’t need the data on the   Properties sheet but you do want to copy the April worksheet to create a   template for May.
 
  Delete the Properties sheet. Insert a new sheet named May. Make sure the May sheet is the   second sheet tab.
 
You want to copy the original   data to the new sheet so that you can modify the duplicated data.
 
  Use the Select All feature to select all data on the April worksheet and copy   it to the May worksheet. Use the paste option to Keep Source Column Widths.

 
 
After copying data from the   April worksheet to the May worksheet, you want to prepare the May worksheet   for data.
 
  In cell A2, change April to May. Select the ranges A5:D13 and   F5:G13 and clear cell contents but keep the formatting In cell F5, type 1. In cell G5, type 1. Copy the values to the range   F6:G13.
 
The property listings dataset is   small. To improve readability on the printed copy for the other real estate   agents, you will increase the scaling.
 
  Select both worksheets and apply 120% scaling.
 
You want to apply page setup   options to both sheets, so you need to group the sheets first.
 
  Select both worksheets. Select Landscape orientation. Center the page   horizontally and vertically between the margins.
 
With both worksheets still   selected, insert a footer with the text Exploring Series on the left side, the sheet   name code in the center, and the file name code on the right side.
 
Save and close Exp22_Excel_Ch01_ML2_Sales.xlsx. Exit   Excel. Submit the file as directed.

Excel chapter 2 hands-on exercise – townsend mortgage company  exp22_excel_ch02_

Excel Chapter 2 Hands-On Exercise – Townsend Mortgage Company 
Exp22_Excel_Ch02_HOE – Townsend Mortgage Company
Exp22 Excel Ch02 HOE – Townsend Mortgage Company
  
Project Description:
You are an assistant to Yui Matheson, a mortgage broker at the Townsend Mortgage Company. Yui spends days reviewing mortgage rates and trends, meeting with clients, and preparing paperwork. Yui relies on your expertise in using Excel to help analyze mortgage data. Today, Yui provided you with sample mortgage data: loan number, house cost, down payment, mortgage rate, and the length of the loan in years. She asked you to perform some basic calculations so that the output provided by her system can be checked to verify that it is calculating results correctly. You have also been tasked with calculating the amount financed, the periodic interest rate, the total number of payment periods, the percentage of the house cost financed, and the payoff year for each loan. In addition, you will calculate totals, averages, and other basic statistics. Furthermore, Yui asked you to complete another worksheet that uses functions to look up interest rates from a separate table, calculate the monthly payments, and determine how much (if any) the borrower will have to pay for private mortgage insurance (PMI).
     
Start Excel. Download and open   the file named Exp22_Excel_Ch02_HOE_Loans.xlsx.   Grader has automatically added your last name to the beginning of the   filename.
 
You would like to create a   formula to calculate the amount financed for each loan.
 
  Enter a formula in cell D8 to calculate the amount financed for the first   mortgage. Once completed, copy the formula down completing column D.

 
 
You would like to create a   formula to calculate the monthly interest rate per period for each loan.
 
  Enter in formula in cell F8 to calculate the monthly interest rate for the   first loan. Add the appropriate absolute cell reference for cell B5 and then   use the fill handle to copy the formula down completing column F.
 
You would like to calculate the   total number of payment periods for each loan. To complete the task, you will   use a mixed cell reference as an alternative to an absolute cell reference.
 
  Enter a formula in cell H8 to calculate the total number of payment periods   per loan. Use an appropriate mixed cell reference for cell B5 and then use   the fill handle to copy the formula down completing column H.
 
You would like to calculate the   total value of the homes being financed.
 
  Use AutoSum in cell B16 to calculate the total value of all homes sold.
 
You would like to calculate the   average value of all homes being purchased.
 
  Use AutoSum in cell B17 to calculate the average value of all homes being   purchased.
 
You would like to calculate the   median value of all homes being purchased.
 
  Enter a function in cell B18 to calculate the median value of all homes being   financed.
 
You would like to display the   cost of the least expensive home.
 
  Enter a function in cell B19 to display the value of the least expensive home   financed.
 
You would like to display the   value of the most expensive home financed.
 
  Enter a function in cell B20 to display the most expensive home financed.
 
You would like to determine the   total number of homes financed.
 
  Enter a function in cell B21 to count the total number of homes being   financed based on the values column B.
 
To complete your calculations   you will use the fill handle to copy the previously created functions.
 
  Use the fill handle to copy the functions in the range B16:B21 through the   range C16:D21.
 
You would like to test the   functions you created by editing one of the values in the worksheet.
 
  Edit the value in cell B9 to 425000. Note the results of the formulas are now updated.
 
It is a good practice to insert   the date in a worksheet containing financial information. You will use the   TODAY function to insert the current date in cell B4.
 
  Use the TODAY function to insert the current date in cell B4.
 
You would like to use a lookup   function on the Payment Info worksheet to determine the annual percentage   rate (APR) of homes financed based on the lookup table in the range D4:E6.
 
  Use the XLOOKUP function in cell G9 to determine the APR based on the years   financed in cell F9, lookup array in the range D4:D6, and return array in the   range E4:E6. Be sure to use the appropriate absolute cell referencing. Then   use the fill handle to copy the function down completing column G. 
 
Next you will calculate the   total payment for each mortgage financed.
 
  Use the PMT function in cell H9 to calculate the monthly payment based on the   APR in cell G9, years financed in cell F9, and amount financed in cell D9.   Ensure the final value is positive. Use the appropriate absolute cell   references and then copy the function down completing the column. 
 
For your last step you will   determine the monthly personal mortgage insurance (PMI) if applicable. Since   not all mortgages require PMI, you will use the IF function.
 
  Enter an IF function in cell I9 to determine the monthly PMI if applicable.   The mortgage requires PMI if the percent of down payment is less than 20%   (cell B7). If the mortgage requires PMI it is .38% of the amount financed. Be   sure to use the appropriate absolute cell references for the input values and   then use the fill handle to copy the function down completing the column.
 
Save and close Exp22_Excel_Ch02_HOE_Loans.xlsx. Exit   Excel. Submit the file as directed.