Microsoft Excel Office Help<\/p>\n
Step 1: Insert Functions
\nA.\tSelect the Search worksheet and then the Pet Deposit column. Create a formula to determine the required pet deposit for each unit. If the unit has two or more<\/p>\n
bedrooms and was remodeled after 2006, the deposit is $150; if not, it is $100.
\nB.\tThe Recommendation column needs a nested function to indicate the remodeling status. If the apartment is unoccupied and has not been remodeled before 2006,<\/p>\n
then display “Please remodel” in the Recommendation column. Display ” ~ No Change” for apartments that do not meet the former criteria.
\nC.\tMake sure each field has the appropriate professional formatting for titles, headers, currency, percent, and so forth. Your worksheets need to be readable,<\/p>\n
clean, and professional. Please let spell-check work for you; use this feature to check for spelling errors.
\nClick Image to Expand<\/p>\n
Step 2: Quick Search
\nNow that all of the rental properties are listed and organized, the owners would like to be able to search through the apartment numbers and return the price of the<\/p>\n
apartment number listed.
\nA.\tInsert number 1301 in cell B3. B3 is the cell that will be used to research apartment unit prices.
\nB.\tCreate a nested lookup function in cell E3. Look up the rental price in column D using the apartment unit number in cell B3. (Use the INDEX function.)
\nC.\tMake sure each field has the appropriate professional formatting for titles, headers, currency, percent, and so forth. Your worksheets need to be readable,<\/p>\n
clean, and professional. Please let spell-check work for you; use this feature to check for spelling errors.
\nClick Image to Expand<\/p>\n
Step 3: Database Functions
\nThe Database tab of the workbook contains a duplicate listing of all of the apartments listed as rental properties. The owners are looking for you to help them filter<\/p>\n
through all of this data to come up with specific pieces of information. They would like to have answers to the following questions.
\n\ufffd\ufffd\ufffd\tHow many two- and three-bedroom apartments need to be remodeled?
\n\ufffd\ufffd\ufffd\tWhat is the value of lost rent income?
\n\ufffd\ufffd\ufffd\tWhat is the year of the oldest remodel?
\nYou know you will need to perform an advanced filter and use some database functions to address these interests. Select the Database worksheet to begin.
\nA.\tEnter conditions in the Specify Criteria area for unoccupied two- and three-bedroom apartments that need to be remodeled. Be very careful with data entry and<\/p>\n
spelling.
\nB.\tSelect cells A2:H4 and give it the Range Name Criteria.
\nC.\tInsert database functions to determine the database statistics in the range D8:D10.
\nD.\tComplete an advanced filter based on the specify criteria range; filter the database in place.
\nE.\tPut the three Rs into practice. Adjust all titles and columns for proper spacing. Double check data formats and consider grid lines.
\n Database Criteria<\/p>\n
Step 4: New Apartment Loan Amortization
\nSummit Ridge Ski owners want to purchase a sixth apartment complex. This decision is under review. Here are the details of their offer. The loan amount is $950,000<\/p>\n
with a down payment of $400,000 for 30 years at 5.325%, with the first payment due on January 20, 2017. Please consider the loan calculations and build a loan<\/p>\n
amortization table on the Loan worksheet. Click on the Loan worksheet to begin.
\nA.\tEnter the loan details provided above in the Input Area and place formulas to create all calculations in the Summary Calculations. The loan payment is at the<\/p>\n
end of the period.
\nB.\tCreate a loan amortization table. The Payment Date column needs a date function. The Interest Paid and Principal Payment columns require financial functions.
\nC.\tCreate a custom footer with your name on the left side, the page and page number in the center, and your professor’s name on the right side of each worksheet.<\/p>\n
Make sure you put the page back to normal view after you insert the footer at the bottom.
\n Loan Details
\nStep 5: Conditional Functions
\nIn addition to adding the new apartments to their financial portfolios, the owners of Summit Ridge Ski Resort would like to ensure that they are paying their employees<\/p>\n
at market value and want to continue to encourage employees to stay with the company. Another phase of your project is to research all employee salaries to see if they<\/p>\n
have any effect on job satisfaction. Employee satisfaction surveys allow the company to get a pulse for how content employees are. A voluntary survey was administered<\/p>\n
to a cross-section sample of all employees in the company. This next bit of work will be on the Employee Satisfaction Worksheet.
\nA.\tCalculate the average job satisfaction for Administrative Assistant in cell I5. Format the results with the number format and two decimal positions.
\nB.\tUse the fill handle from cell I5 to copy the function down through the range I6:I11. Make certain to consider the appropriate mixed and\/or absolute cell<\/p>\n
referencing.
\nC.\tCalculate the average salary of all Administrative Assistants and place the result in cell J5.
\nD.\tUse the fill handle from cell J5 to copy the function down through the range J6:J11. Make certain to consider the appropriate mixed and\/or absolute cell<\/p>\n
referencing.
\nE.\tCalculate the number of Sales and Marketing Directors in cell I14 that have a job satisfaction level of 4 or above.
\nF.\tCalculate the average salary of Sales and Marketing Directors in cell I15 that have a job satisfaction level of 4 or above.
\nG.\tUse a process like that demonstrated in steps E and F to calculate the total number and the average salary of Managers that have a job satisfaction of 4 or<\/p>\n
greater.
\n Employee Satisfaction<\/p>\n
Step 6: Calculate Relative Standing and Central Tendency
\nPlease continue your analysis and calculate both the salary rankings and the salary quartile thresholds.
\nA.\tInsert a function in cell F4 to determine the rank of the salary in cell D4 against the range of all salaries.
\nB.\tCopy the Salary Rank function down column F. Make certain to consider the appropriate mixed and\/or absolute cell referencing.
\nC.\tCalculate the minimum quartile value in the list of salaries. Place your quartile function in cell I22.
\nD.\tCopy the remaining quartile values in range I23:I26. Make certain to consider the appropriate mixed and\/or absolute cell referencing.
\nE.\tInsert a function in cell H29 to determine the strength of the relationship between satisfaction and salary. Format the correlation of columns D and E as<\/p>\n
number format with two decimal positions.
\n Statistical Functions
\nStep 7: Using the Analysis ToolPak
\nYou are almost finished with your analysis. Please create a summary of descriptive statistics using the Analysis ToolPak. The Analysis ToolPak add-in must be active<\/p>\n
before completing the following.
\nA.\tGo to the Data tab and select Data Analysis. Select Descriptive Statistics, and click OK.
\nB.\tInsert the input range using the salary data in column D.
\nC.\tSelect the new worksheet output option to display on a new worksheet named Descriptive Statistics. Put a check in the box to the left of Summary statistics.<\/p>\n
Click OK.
\nD.\tCreate a histogram with the Analysis ToolPak. The salary column D will be the input range and the quartile range I22:I25 will be the bin range. Position the<\/p>\n
Output range in cell H31. Make sure the Chart output is checked. Click OK.
\nE.\tFormat the worksheet professionally.
\n Descriptive Statistics
\nStep 8: Create a Documentation Sheet
\nClean up the formatting of your Excel workbook, taking into account professional appearance.
\nThe Minimum Requirement (per the Grading Rubric)
\nA.\tInsert a new spreadsheet into the workbook. The Documentation sheet should be the first sheet in the workbook.
\nB.\tMake certain each tab has a descriptive name and color for each tab (sheet) in the workbook.
\nC.\tCreate the professional documentation worksheet. Be sure to include a description of each worksheet. An image is provided below.
\n Week 5 Documentation Sheet<\/p>\n
Stay a While Longer – Apartments Search<\/p>\n
Unit #\t\t\tRental Price<\/p>\n
Unit #\tApartment Complex\t# Bed\tRental Price\tOccupied\tLast Remodel\tPet Deposit\tRecommendation
\n1101\tTurning Leaf Circle\t1\t $765.00 \tYes\t2005
\n1102\tTurning Leaf Circle\t1\t $765.00 \tYes\t2003
\n1103\tTurning Leaf Circle\t2\t $895.00 \tNo\t1999
\n1104\tTurning Leaf Circle\t2\t $895.00 \tYes\t2005
\n1105\tTurning Leaf Circle\t3\t $1,225.00 \tNo\t2007
\n1106\tTurning Leaf Circle\t3\t $1,225.00 \tYes\t2005
\n1107\tTurning Leaf Circle\t2\t $895.00 \tNo\t2005
\n1108\tTurning Leaf Circle\t2\t $895.00 \tYes\t2009
\n1109\tTurning Leaf Circle\t3\t $1,225.00 \tNo\t2008
\n1110\tTurning Leaf Circle\t3\t $1,225.00 \tYes\t2009
\n1201\tTurning Leaf Circle\t1\t $775.00 \tYes\t2011
\n1202\tTurning Leaf Circle\t1\t $775.00 \tYes\t2010
\n1203\tTurning Leaf Circle\t2\t $875.00 \tYes\t2011
\n1204\tTurning Leaf Circle\t2\t $875.00 \tNo\t2011
\n1205\tTurning Leaf Circle\t3\t $1,050.00 \tYes\t2005
\n1206\tTurning Leaf Circle\t3\t $1,050.00 \tNo\t2005
\n1207\tTurning Leaf Circle\t2\t $875.00 \tYes\t2000
\n1208\tTurning Leaf Circle\t2\t $875.00 \tYes\t2000
\n1209\tTurning Leaf Circle\t3\t $1,050.00 \tNo\t2005
\n1210\tTurning Leaf Circle\t3\t $1,050.00 \tYes\t2005
\n1301\tPiedra Springs \t1\t $875.00 \tNo\t2008
\n1302\tPiedra Springs \t1\t $875.00 \tNo\t2001
\n1303\tPiedra Springs \t1\t $900.00 \tNo\t2001
\n1304\tPiedra Springs \t1\t $900.00 \tYes\t2001
\n1305\tPiedra Springs \t2\t $1,355.00 \tNo\t2005
\n1306\tPiedra Springs \t2\t $1,355.00 \tYes\t2005
\n1307\tPiedra Springs \t2\t $1,355.00 \tYes\t2011
\n1308\tPiedra Springs \t2\t $1,355.00 \tNo\t2011
\n1401\tEagles Domain Road\t1\t $875.00 \tNo\t2005
\n1402\tEagles Domain Road\t2\t $950.00 \tNo\t2011
\n1403\tEagles Domain Road\t3\t $1,355.00 \tYes\t2011
\n1404\tEagles Domain Road\t1\t $875.00 \tNo\t2011
\n1405\tEagles Domain Road\t2\t $950.00 \tYes\t2011
\n1406\tEagles Domain Road\t3\t $1,355.00 \tNo\t2002
\n1407\tEagles Domain Road\t2\t $975.00 \tYes\t2002
\n1408\tEagles Domain Road\t2\t $975.00 \tYes\t2002
\n1501\tHickory Ridge Villas\t1\t $550.00 \tYes\t2011
\n1502\tHickory Ridge Villas\t1\t $550.00 \tNo\t2011
\n1503\tHickory Ridge Villas\t1\t $550.00 \tNo\t2003
\n1504\tHickory Ridge Villas\t1\t $550.00 \tYes\t2001
\n1505\tHickory Ridge Villas\t2\t $850.00 \tNo\t2003
\n1506\tHickory Ridge Villas\t2\t $850.00 \tYes\t2011
\n1507\tHickory Ridge Villas\t2\t $850.00 \tNo\t2011
\n1601\tCypress Trail\t1\t $1,225.00 \tYes\t2005
\n1602\tCypress Trail\t1\t $1,225.00 \tYes\t2005
\n1603\tCypress Trail\t2\t $1,355.00 \tNo\t2005
\n1604\tCypress Trail\t2\t $1,355.00 \tYes\t2011
\n1605\tCypress Trail\t3\t $1,675.00 \tNo\t2011
\n1606\tCypress Trail\t3\t $1,695.00 \tYes\t2005
\n1607\tCypress Trail\t3\t $1,710.00 \tNo\t2005<\/p>\n","protected":false},"excerpt":{"rendered":"
Microsoft Excel Office Help Step 1: Insert Functions A. Select the Search worksheet and then the Pet Deposit column. Create a formula to determine the required pet deposit for each unit. If the unit has two or more bedrooms and Read More …<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-5424","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/5424","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=5424"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/5424\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=5424"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=5424"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=5424"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}