Archive for month: January, 2024

Overview

This project involves a case study based on a fictional company, Healthy Home Care, Inc. You’ll assume the role of office manager, who is responsible for creating the literature for a Welcome Package. The promotional documents will be printed and placed in a folder to be mailed to the director of a senior center. For this project, you’ll complete four documents for the package:

1. A cover letter discussing your partnership with a senior center
2. A fact sheet highlighting the services and amenities you offer
3. A flier promoting the next wellness clinic
4. A pre-registration form

Instructions

Create the following documents. Your score will be based on the rubric found in the scoring guidelines.

Create and Edit a Cover Letter

The Welcome Package includes a letter to the director of a senior center.

1. Start Word and create a new document.
2. Change the style of the blank paragraph to No Spacing.
3. Type the text shown below, pressing Enter to place one blank line where indicated, four blank lines in the closing, and four more blank lines before the word Enclosures, and beginning new paragraphs where indicated.

Your document should now look similar to that shown below.

1. Replace the word Date with a time stamp displaying a date that updates automatically in the format “Month, Date, Year.”
2. Edit the first paragraph to display the Healthy Home Care, Inc., services as a bulleted list with each item starting with an uppercase letter, similar to Figure below.

1. Bold the three occurrences of Healthy Home Care, Inc., within the body of the letter.
2. Save the document, naming it “Healthy Home Care letter.”

Create a Fact Sheet

The Fact Sheet needs to display the Healthy Home Care, Inc., services in large print.

1. Start Word and create a new document.
2. Change the style of the blank paragraph to No Spacing.
3. Type the text shown below, pressing Enter to start new paragraphs where shown.

Your document should now look similar to that shown below.

1. Change the margins to 2 inches on the left and right and 1 inch on top and bottom.
2. Replace the first two lines of text with appropriately formatted WordArt that has a Wrap Text format of Square and is centered above the rest of the text, similar to that shown below.

1. Format the remaining text as Georgia 16 point.
2. Bold the three questions only.
3. Press Enter after the last question and then format the answer paragraphs as a bulleted list, using a character other than the • symbol.
4. Format the bulleted list with 12-point spacing after each paragraph.
5. Save the document, naming it “Healthy Home Care fact sheet.”

Create a Flier

The flier will promote the Wellness Clinic at Palms Senior Center.

1. Start Word and create a new document.
2. Change the document orientation to Landscape.
3. Type the text shown below.

1. Change the margins to 0.3 inch on all sides.
2. Your document should look similar to that shown below.

1. Format the title in Comic Sans MS 72 point, bold, dark green.
2. Select the next three lines of text and apply the Heading 1 style.
3. Modify the Heading 1 style with the following formats:
   1. Arial 26 point bold
   2. Dark gray color
   3. Center alignment
4. Format the last line of text with 2-inch right and left indents and then change the font to Arial 9 point.
5. In the blank paragraph after Complimentary, insert an appropriate clip art image of fruit.
6. Size the clip art so all the text is on one page, and then center the image.
7. Save the document, naming it “Healthy Home Care flier.”

Create a Pre-Registration Form

The pre-registration form will be used to compile names of prospective clients.

1. Start Word and create a new document.
2. Insert a 5 by 11 table.
3. Merge the cells in the top row.
4. Select only Header Row in Table Style Options and then select a table style with blue shading in the first row.
5. Type the form title as shown below and format the first line of text as Arial 20 point, bold and the second line as Arial 16 point, regular.

1. Type text and merge cells so your form looks similar to the figure below. After merging cells in the last row, change the row height to 4.5 inches. Change the row heights of the cells containing text to 0.3 inch.

1. Save the document, naming it “Healthy Home Care pre-registration form.”

Scoring Guidelines

Rubric

Skill/Grading CriteriaExemplary
(4)Proficient
(3)Fair
(2)Poor
(1)Apply a Word StyleAppropriate paragraphs are in the indicated Word style.Most paragraphs are in the| indicated Word style.Some paragraphs are in the indicated Word style.Few paragraphs are in the indicated Word style.Insert a time stampA time stamp set to update automatically is displayed in the format Month, Date, Year.A time stamp set to update automatically is displayed in any format.A time stamp not set to update is displayed in any format.A date has been typed.Edit Text and format as a bulleted listAll of the indicated paragraphs have been edited and formatted as a bulleted list.Most of the indicated paragraphs have been edited and formatted as a bulleted list.Some of the indicated paragraphs have been edited and formatted as a bulleted list.An attempt has been made to format the indicated text in a list style.Apply the bold character formatAll of the indicated text has been formatted as bold.Most of the indicated text has been formatted as bold.Some of the indicated text has been formatted as bold.The wrong text has been formatted as bold.Change marginsAll margins have been changed to the measurements indicated.Most of the margins have been changed to the measurements indicated.Some of the margins have been changed to the measurements indicated.The margins have been changed to the wrong measurements.Create WordArt and change wrapAppropriate WordArt has been created, sized, formatted, and given the appropriate wrap.Appropriate WordArt has been created, sized, and formatted.Appropriate WordArt has been created and sized.WordArt has been created but is neither appropriate nor formatted.Format text in a different fontAll of the indicated text has been formatted with the correct typeface, size, and style where indicated.Most of the indicated text has been formatted with the correct typeface, size, and style where indicated.Some of the indicated text has been formatted with the correct typeface, size, and style where indicated.None of the indicated text has been formatted with the correct combination of typeface, size, and style where indicated.Change the bullet style of a listAll of the indicated paragraphs have been edited and formatted as a bulleted list with an appropriate bullet character.Most of the indicated paragraphs have been edited and formatted as a bulleted list with an appropriate bullet character.Some the indicated paragraphs have been edited and formatted as a bulleted list with an appropriate bullet character.An attempt has been made to format the indicated text in a list style.Change paragraph spacingAll of the indicated paragraphs have the appropriate paragraph style.Most of the indicated paragraphs have the appropriate paragraph style.Some of the indicated paragraphs have the appropriate paragraph style.An attempt has been made to add spacing between paragraphs without changing the paragraph style.Change page orientationThe document orientation is Landscape.The document orientation is Landscape.The document orientation is Landscape.The document orientation is Landscape.Create indentsThe indicated paragraph has right and left indents of the appropriate measurements.The indicated paragraph has either a right or left indent of the appropriate measurement.The indicated paragraph has right and left indents, but of the wrong measurements.Spaces, tabs, or some other character was used in an attempt to change indents.Apply color to textAll of the indicated text has been formatted in the appropriate color.Most of the indicated text has been formatted in the appropriate color.Some of the indicated text has been formatted in the appropriate color.The wrong text has been formatted in a color.Modify a built-in styleAll of the indicated changes have been made to the Word style.Most of the indicated changes have been made to the Word style.Some of the indicated changes have been made to the Word style.Few of the indicated changes have been made to the Word style.Insert clip artAn appropriate clip art image has been inserted, sized, and formatted.An appropriate clip art image has been inserted and sized.An appropriate clip art image has been inserted.A clip art image has been inserted but is neither related to the content nor has it been formatted.Insert a tableA table of the specified size has been inserted.A table of the wrong size has been inserted.Tabs have been used to create rows and columns of data.Text has been typed with no attempt to organize it.Merge table cellsAll of the indicated table cells have been merged.Most of the indicated table cells have been merged.Some of the indicated table cells have been merged.Few of the indicated table cells have been merged.Apply a table styleA Word table style with the appropriate options has been applied.A Word table style with the appropriate options has been applied without top row shading.A Word table style with the wrong options has been applied.An attempt has been made to format the table by applying separate cell formats.Format table row heightAll of the indicated rows have the appropriate height.Most of the indicated rows have the appropriate height.Some of the indicated rows have the appropriate height.The indicated rows have been changed to the wrong height.

Submission Checklist

Before submitting your project, make sure you’ve correctly completed the following:

• Create, save, and name a file
• Type text
• Edit text
• Change page orientation
• Character formats, including typeface, point size, bold, and color
• Apply Word styles
• Modify a Word style
• Insert an automatically updating time stamp
• Paragraph formats, including alignment, spacing, and indents
• Create WordArt
• Insert clip art
• Change the wrap and size of an image
• Use bulleted lists, including changing the default bullet
• Insert a table
• Change table formats using Table Styles
• Change cell formats using Text and Paragraph Styles
• Change table structure, including merging cells and row heights
• Type data into a table

Be sure to keep a backup copy of any files you submit to the school!

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) 

Independent Project 6-5 (Mac 2016 Version)

Classic Gardens and Landscapes counts responses to mail promotions to determine effectiveness. You use SUMIFS and a nested IF formula to complete the summary. You also calculate insurance statistics and convert birth dates from text to dates. 

Skills Covered in This Project 

• Nest MATCH and INDEX functions.
• Create DSUM formulas.
• Build an IFS function.

• Build SUMIFS formulas.
• Use DATEVALUE to convert text to

dates.

Step 1 

Download start file

1. Open the ClassicGardens-06 start file. Click the Enable Editing button. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Create a nested INDEX and MATCH function to display the number of responses from a city.
   1. Click the Mailings sheet tab and select and name cells A3:D28 as Responses
   2. Click the Mailing Stats sheet tab.
   3. Click cell B21 and type Carthage.
   4. Click cell C21, start an INDEX function, and select the first argument list option.
   5. Choose the Responses range for the Array argument.
   6. Click the Row_num box and nest a MATCH function. Select cell B21 for the Lookup_value and
      cells A3:A28 on the Mailings sheet for the Lookup_array. Click the Match_type argument box
      and type 0.
   7. Click INDEX in the Formula bar. Click the Column_num box and nest a second MATCH function to
      look up cell D3 on the Mailings sheet in the lookup array A3:D3.
   8. Click the Match_type box and type 0 (Figure 6-105).
      Important: There is a known bug in Excel for Mac that places plus signs ( + ) instead of commas ( , ) between the arguments when using the Formula Builder. If this is the case in your Excel for Mac version, replace the plus signs with commas.

Excel 2016 Chapter 6 Exploring the Function Library Last Updated: 3/27/19 Page 1 

USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) 

1. Format the results to show zero decimal places.
2. Type Smyrna in cell B21.
3. Use DSUM to summarize mailing data.
   1. On the Mailings sheet, note that number sent is located in the third column and response data is in
      the fourth column.
   2. Click the Criteria sheet tab. Select cell B2 and type lan* to select data for the Landscape Design
      department.
   3. Click the Mailing Stats sheet tab and select cell B7.
   4. Use DSUM with the range name Responses as the Database argument. Type 3 for the Field
      argument, and use an absolute reference to cells B1:B2 on the Criteria sheet as the Criteria
      argument.
   5. Copy the formula to cell C7 and edit the Field argument to use the fourth column.
   6. Complete criteria for the two remaining departments on the Criteria sheet.
   7. Click the Mailing Stats sheet tab and select cell B8.
   8. Use DSUM in cells B8:C9 to calculate results for the two departments.
4. Use SUM in cells B10:C10.
5. Format all values as Comma Style with no decimal places.
6. Create an IFS function to display a response rating.
   IMPORTANT: If you are using a version of Excel that does not include the IFS function, create a formula using nested IF functions instead where each Value_if_false argument is the next IF statement. The innermost nested IF statement should have a Logical_test argument of C7/B7<10%, Value_if_true argument of $C$18, and Value_if_false argument of 0.
   1. Click cell D7. The response rate and ratings are shown in rows 14:18.
   2. Start an IFS function and select C7 for the Logical_test1 argument. Type / for division and select
      cell B7. Type >= 20% to complete the test.
   3. Click the Value_if_true1 box, select C15, and press F4 (FN+F4) (Figure 6-106).

Excel 2016 Chapter 6 Exploring the Function Library Last Updated: 3/27/19 Page 2 

USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) 

1. Click the Logical_test2 box, select C7, type /, select cell B7, and type >=15%
2. Click the Value_if_true2 box, click cell C16, and press F4 (FN+F4).
3. Complete the third and fourth logical tests and value_if_true arguments (Figure 6-107).
4. Copy the formula in cell D7 to
   cells D8:D10.
5. Use SUMIFS to total insurance
   claims and dependents by city and department.
   1. Click the Employee Insurance
      sheet tab and select cell E25.
   2. Use SUMIFS with an absolute
      reference to cells F4:F23 as the
      Sum_range argument.
   3. The Criteria_range1 argument
      is an absolute reference to cells E4:E23 with Criteria1 that will select the city of Brentwood.
   4. The Criteria_range2 argument
      is an absolute reference to the department column with criteria that will select the Landscape Design department.
   5. Complete SUMIFS formulas for cells E26:E28.
   6. Format borders to remove inconsistencies, if any, and adjust column widths to display data.
6. Use DATEVALUE to convert text data to dates.
   1. Click the Birth Dates sheet tab and select cell D4. The dates were imported as text and cannot be used in date arithmetic.
   2. Select cells D4:D23 and cut/paste them to cells G4:G23.
   3. Select cell H4 and use DATEVALUE to convert the date in cell G4 to a serial number.
   4. Copy the formula to cells H5:H23.
   5. Select cells H4:H23 and copy them to the Clipboard.
   6. Select cell D4, click the arrow with the Paste button [Home tab, Clipboard group], and choose
      Values (Figure 6-108).
   7. Format the values in column D
      to use the Short Date format.
   8. Hide columns G:H.
   9. Apply All Borders to the data
      and make columns B:D each 13.57 wide. NOTE: Some versions of Excel 2016 for Mac use inches for row height and column width. When viewing the column width, if double quotes appear when displaying the value, enter 1.17” instead of 13.57.

Excel 2016 Chapter 6 Exploring the Function Library

Last Updated: 3/27/19 Page 3 

USING MICROSOFT EXCEL 2016 

Independent Project 6-5 (Mac 2016) 

Step 2 

Upload & Save

Step 3 

Grade my Project

9. Save and close the workbook (Figure 6-109). 10. Upload and save your project file.
11. Submit project for grading.

Excel 2016 Chapter 6 Exploring the Function Library

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

The Wear-Ever Shoes company maintains inventory data and customer survey results in your workbook. You use Lookup & Reference, Database, and Logical functions to complete the data. You also use a Financial function to calculate depreciation and a Text function to enter email addresses.

[Student Learning Outcomes 6.1, 6.2, 6.3, 6.5, 6.6, 6.7]

File Needed: WearEverShoes-06.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-WearEverShoes-06.xlsx

Skills Covered in This Project

• Nest INDEX and MATCH functions.
• Use SUMIFS from the Math & Trig category.
• Use DAVERAGE.
• Create an IFS formula.
• Use a Text function to concatenate text strings.
• Calculate depreciation with the DB function.

1. Open the WearEverShoes-06 start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Click the Inventory sheet tab.
3. Select cells A3:I39, click the Name box, type Inventory as the range name, and press Enter.
4. Select cell L5 and type WE006.
5. Create a nested function with INDEX and MATCH to display inventory for a product.
   1. Select cell L6.
   2. Click the Lookup & Reference button [Formulas tab, Function Library group] and choose INDEX. Select the first argument list array, row_num, column_num and click OK.
   3. For the Array argument, press F3 (FN+F3) and select Inventory.
   4. Click the Row_num box and click the Name box arrow. Choose MATCH in the list or choose More Functions to find and select MATCH. The INDEX function uses this MATCH statement to find the row.
   5. Click cell L5 for the Lookup_value argument.
   6. Click the Lookup_array box and select cells A3:A39. This MATCH function finds the row that matches cell L5 in column A.
   7. Click the Match_type argument and type 0.
   8. Click INDEX in the Formula bar. (Click OK if the argument list opens.)
   9. Click the Column_num argument, click the Name box arrow, and choose MATCH (Figure 6-92).Figure 6-92 MATCH is nested twice
   10. Type quantity in the Lookup_value box.
   11. Click the Lookup_array box and select cells A3:I3. This MATCH function finds the cell in the “Quantity” column after the row is located by the first MATCH function.
   12. Click the Match_type box and type 0. The formula is =INDEX(Inventory,MATCH(L5,A3:A39,0),MATCH(“quantity”,A3:I3,0)).
   13. Click OK. The result is 2.
   14. Click cell L5, type WE015, and press Enter. The quantity is updated.
6. Use SUMIFS to calculate total pairs in stock by specific criteria.
   1. Select cell M13.
   2. Click the Math & Trig button [Formulas tab, Function Library group] and choose SUMIFS.
   3. Select cells E4:E39 for the Sum_range argument and press F4 (FN+F4) to make the references absolute.
   4. Click the Criteria_range1 box, select cells C4:C39, the “Color” field, and press F4 (FN+F4).
   5. Click the Criteria1 box and select cell K13. Leave this as a relative reference.
   6. Click the Criteria_range2 box, select cells D4:D39, and make the references absolute.
   7. Click the Criteria2 box and select cell L13. The criteria specifies the number of black pairs, size 8 (Figure 6-93).Figure 6-93 SUMIFS to calculate number by color and size
   8. Click OK. The result is 7.
   9. Copy the formula in cell M13 to cells M14:M21.
7. Click the Satisfaction Survey worksheet tab and review the data.
8. Select cells A4:H40 and name the range as Survey. Note that the “Comfort” field is the fifth column and that the other attributes follow in the sixth, seventh, and eighth columns.
9. Use DAVERAGE to summarize customer survey data.
   1. Click the Criteria sheet tab.
   2. Select cell B2 and type rug*, criteria for the Rugged Hiking Boots.
   3. Click the Average Ratings worksheet tab and select cell C5.
   4. Click the Insert Function button [Formulas tab, Function Library group].
   5. Choose Database in the Or select a category list.
   6. Select DAVERAGE and click OK to calculate an average comfort rating for the boots.
   7. Press F3 (FN+F3), choose Survey for the Database argument, and click OK.
   8. Click the Field box and select cell C4.
   9. Click the Criteria box, select the Criteria sheet tab, select cells B1:B2, and make the references absolute (Figure 6-94).Figure 6-94 DAVERAGE for comfort rating
   10. Click OK. The result is 7.75.
   11. Copy the formula in cell C5 to cells D5:F5.
10. Use DAVERAGE to summarize survey data.
    1. Select the Criteria sheet tab and select cell B5. Type the criteria as shown here for the shoe styles.The table lists the criteria to be entered on the Criteria sheet.CellCriteriaB5com*B8laz*B11ser*B14gli*
    2. Click the Average Ratings sheet tab and select cell C6.
    3. Click the Recently Used button [Formulas tab, Function Library group] and select DAVERAGE.
    4. Press F3 (FN+F3) and choose Survey for the Database argument.
    5. Click the Field argument box and select cell C4.
    6. Click the Criteria box, select cells B4:B5 on the Criteria sheet, and press F4 (FN+F4).
    7. Click OK. The result is 7.5.
    8. Copy the formula in cell C6 to cells D6:F6.
11. Build DAVERAGE functions for the remaining shoe styles on the Average Ratings sheet.
12. Select cells G5:G9 on the Average Ratings sheet, click the AutoSum arrow [Home tab, Editing group], and choose Average.
13. Create an IFS function.Note: If your version of Excel does not include the IFS function, build the following nested IF function =IF(G5>=9,$J$5,IF(G5>=8,$J$6,IF(G5>=5,$J$7,$J$8))) to show the ratings.
    1. Select cell H5, click the Logical button [Formulas tab, Function Library group], and choose IFS.
    2. Click the Logical_test1 argument, select cell G5, and type >=9.
    3. Click the Value_if_true1 box, click cell J5, and press F4 (FN+F4) to make the reference absolute.
    4. Click the Logical_test2 box, click cell G5, and type >=8.
    5. Click the Value_if_true2 box, click cell J6, and press F4 (FN+F4).
    6. Click the Logical_test3 box, click cell G5, and type >=5.
    7. Click the down scroll arrow to reveal the Value_if_true3 box, click cell J7, and press F4 (FN+F4).
    8. Click the down scroll arrow to reveal the Logical_test4 box, click cell G5, and type <5.
    9. Click the down scroll arrow to reveal the Value_if_true4 box, click cell J8, and press F4 (FN+F4) (Figure 6-95). The complete formula is:=IFS(G5>=9,$J$5,G5>=8,$J$6,G5>=5,$J$7,G5<5,$J$8)Figure 6-95 IFS function with multiple logical tests
    10. Click OK and copy the formula to cells H6:H9.
    11. Format column H to be 13.57 (100 pixels) wide.
14. Calculate depreciation for an asset using a Financial function.
    1. Click the Depreciation sheet tab and select cell C11. Depreciation is the decrease in the value of an asset as it ages. The DB function calculates the loss in value over a specified period of time at a fixed rate.
    2. Click the Financial button [Formulas tab, Function Library group] and choose DB.
    3. Select cell C6 for the Cost argument, and press F4 (FN+F4) to make the reference absolute. This is the initial cost of the equipment.
    4. Click the Salvage box, select cell C7, and press F4 (FN+F4). This is the expected value of the equipment at the end of its life.
    5. Click the Life box, select cell C8, and press F4 (FN+F4). This is how long the equipment is expected to last.
    6. Click the Period box and select cell B11. The first formula calculates depreciation for the first year (Figure 6-96).Figure 6-96 DB function to calculate asset depreciation
    7. Click OK. The first year depreciation is $39,900.00.
    8. Copy the formula in cell C11 to cells C12:C18. Each year’s depreciation is less than the previous year’s.
    9. Select cell C19 and use AutoSum. The total depreciation plus the salvage value is approximately equal to the original cost. It is not exact due to rounding.
15. Use CONCAT to build an email address. (If your version of Excel does not include CONCAT, use CONCATENATE.)
    1. Right-click any worksheet tab, choose Unhide, select E-Mail, and click OK.
    2. Select cell C5, type =con, and press Tab. The text1 argument is first.
    3. Select cell A5 and type a comma (,) to move to the text2 argument.
    4. Select cell B5 and type a comma (,) to move to the text3 argument.
    5. Type “@weshoes.org” including the quotation marks (Figure 6-97).Figure 6-97 CONCAT references and typed data
    6. Type the closing parenthesis ()) and press Enter.
    7. Copy the formula in cell C5 to cells C6:C8.
16. Save and close the workbook (Figure 6-98).

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

You are the office manager for a real estate company in northern Utah County. You tracked real estate listings, including city, agent, listing price, sold price, etc. Agents can represent a seller, a buyer, or both (known as dual agents). Your assistant prepared the spreadsheet structure with agent names, agent types, the listing and sold prices, and the listing and sold dates. You want to complete the spreadsheet by calculating the number of days each house was on the market before being sold, agent commissions, and bonuses. In addition, you will use conditional functions to calculate summary statistics. For further analysis, you will insert a map chart to indicate the average house selling price by city. Finally, you will create a partial loan amortization table and calculate cumulative interest and principal to show a potential buyer to help the buyer make decisions.

The   spreadsheet contains codes (BA, DA, SA) to represent agent roles (Buyer’s   Agent, Dual Agent,   Seller’s Agent). You want to switch the codes for the actual descriptions.
In cell E12 of the Details sheet, insert the SWITCH function to evaluate the   agent code in cell D12. Include mixed cell references to the codes and roles   in the range J2:K4 for the values
and results arguments. use all cell references in the function. Copy the   function to the range E13:E39.

Now you want to calculate the   number of days between the list date and sale date.
In cell J12, insert the DAYS function to calculate the number of days between   the Listing Date and the Sale Date. Copy the function to the range J13:J39.

You want to calculate agent   commissions based on their role.
In cell K12, insert the IFS function to calculate the agent’s commission   based on the agent code and the applicable rates in the range L2:L4. Use   relative and mixed references correctly. Copy the function to the range   K13:K39.

You want to calculate a bonus if   the sold price was at least equal to the listing price, and if the house sold   within 30 days after being listed.
In cell L12, insert an IF function with a nested AND function to calculate a   bonus. The AND function should ensure both conditions are met: Sold Price   divided by the Listing Price is greater than or equal to 100% (cell L7) and   the Days on Market are less than or equal to 30 (cell L8). If both conditions   are met, the bonus is $1,000 (cell L9). Otherwise, the bonus is $0. Use mixed   cell references to the input values in the range L7:L9. Copy the function to   the range L12:L39.

The top-left section of the   spreadsheet is designed for summary statistics for one condition. You will   calculate average selling prices and the number of houses sold in each city   (the condition).
In cell B2, insert the AVERAGEIF function to calculate the average Sold Price   for houses in the city of Alpine. Use mixed references for the range; use a   relative reference to cell A2. Copy the function and use the Paste Formulas   option to paste the function in the range B3:B5 so that the bottom border in   cell B5 is preserved.

You want to count the number of   houses in one city.
In cell C2, insert the COUNTIF function to count the number of houses in the   city of Alpine. Use mixed references for the range; and use a relative   reference to cell A2. Copy the function and use the Paste Formulas option to   paste the function in the range C3:C5 so that the border in cell C5 is   preserved.

You want to calculate the total commissions   for each agent (the condition).
In cell B7, insert the SUMIF function to total the commissions by agent. Use   mixed references for the ranges; and use a relative reference to cell A7.   Copy the function and use the Paste Formulas option to paste the function in   the range B8:B9 so that the borders are preserved.

The top-middle section of the   spreadsheet is designed for summary statistics for multiple conditions. You   will calculate the number of houses sold for each agent when he or she served   as a Dual Agent (DA). Use mixed references for ranges and the agent code   condition in cell J3. Use relative cell references to the agent condition in   cell E2. When you copy the formulas, use the paste Formulas options to   preserve border formatting.
In cell F2, insert the COUNTIFS function in cell F2 to count the number of   houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for   that house. Use all cell references in the function. Copy the function to the   range F3:F4 and preserve the bottom border for cell F4.

You are ready to calculate the   total value of those houses for each agent when he or she served as a Dual   Agent (DA). Use mixed references for ranges and the agent code condition in   cell J3. Use relative cell references to the agent condition in cell E2. When   you copy the formulas, use the paste Formulas options to preserve border   formatting.
In cell G2, insert the SUMIFS function to sum the selling prices of the   houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for   that house. Copy the function to the range G3:G4 and preserve the bottom   border for cell G4.

Now, you will calculate the   highest-price house highest-price house sold for each agent when he or she   served as a Dual Agent (DA). Use mixed references for ranges and the agent   code condition in cell J3. Use relative cell references to the agent   condition in cell E2. When you copy the formulas, use the paste Formulas   options to preserve border formatting.
In cell H2, insert the MAXIFS function in cell H2 to display the highest-price   house sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for   that house. Copy the function to the range H3:H4 and preserve the borders in   the range H3:H4.

The Map worksheet contains a   list of cities, postal codes, and average house sales. You will insert a map   chart to depict the averages visually using the default gradient fill colors.
Display the Map worksheet, select the range B1:C5 and insert a map chart.

Cut the map chart and paste it   in cell A7. Set a 2.31″ height and 3.62″ width.

You want to enter a meaningful   title for the map.
Change the map title to Average Selling Price by Zip Code.

Display the Format Data Series   task pane, select the option to display only regions with data, and show all   labels. Close the task pane.

You are ready to start   completing the loan amortization table.
Display the Loan worksheet. In cell B8, type a reference formula to cell B1.   The balance before the first payment is identical to the loan amount. Do not   type the value; use the cell reference instead. In cell B9, subtract the   principal from the beginning balance on the previous row. Copy the formula to   the range B10:B19.

Now, you will calculate the   interest for the first payment.
In cell C8, calculate the interest for the first payment using the IPMT   function. Copy the function to the range C9:C19.

Next, you will calculate the   principal paid.
In cell D8, calculate the principal paid for the first payment using the PPMT   function. Copy the
function to the range D9:D19.

Rows 21-23 contain a summary   section for cumulative totals after the first year.
In cell B22, insert the CUMIPMT function that calculates the cumulative   interest after the first year. Use references to cells A8 and A19 for the   period arguments.

The next summary statistic will   calculate the principal paid after the first year.
In cell B23, insert the CUMPRINC function that calculates the cumulative   principal paid after the first year. Use references to cells A8 and A19 for   the period arguments.

Rows 25-28 contain a section for   what-if analysis.
In cell B27, use the RATE financial function to calculate the periodic rate   using $1,400 as the
monthly payment (cell B26), the NPER, and loan amount in the original input   section.

In cell B28, calculate the APR   by multiplying the monthly rate (cell B27) by 12.

Create a footer with your name   on the left side, the sheet name code in the center, and the file name code   on the right side of each worksheet.

Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!