Scenario<\/p>\n
You have been hired to work for a construction company that builds homes. Your company is currently building homes for 47 customers. You notice that your secretary is constantly receiving phone calls from customers asking when their house is scheduled to be completed.<\/p>\n
You volunteer to create an Excel workbook to help! Specifically, the workbook should be able to look up a customer and display that customer’s address, house plan, build time, when the contract was signed, and different possible expected dates of completion.<\/p>\n
In the attached workbook, Sheet1 shows each client’s name, address, house plan, and contract date. Sheet2 shows how many workdays each house plan takes to complete, and a list of holidays.<\/p>\n
Requirements<\/p>\n
Sheet1 and Sheet2<\/p>\n
The columns labeled\u00a0Contract Date\u00a0(on Sheet1) and\u00a0Holiday Date\u00a0(on Sheet2) need to be formatted to properly show up as dates.<\/p>\n
The company does not work on the owner’s birthday. Since you and the owner share the same birthday, please add your birthday to the holiday table on Sheet2. The year you put with your birthday needs to correspond to the years in which these projects are being completed. Put at least two years worth of birthdays on the list.<\/p>\n
On Sheet1, create a column that looks up the number of days each house plan takes to build from the table on Sheet2.<\/p>\n
Create formulas (in three additional columns) to calculate the estimated completion date based on three different work schedules:<\/p>\n
Calculate the completion dates for all homes using a workday function if we work normal workdays (Monday to Friday) during construction.<\/p>\n
Calculate the completion dates for all homes if we also work on each Saturday during construction.<\/p>\n
Calculate the completion dates for all homes if we also work on each Saturday and holiday during construction.<\/p>\n
Sort the data by contract date with oldest contracts on top.<\/p>\n
Lookup Sheet<\/p>\n
Create a separate sheet that the office assistant can use. Use data validation to create a drop-down list of customer names (it is most efficient to use a named range for the client list). Create an input message for the drop down list.<\/p>\n
Use lookup functions to display the address, house plan, contract date, build time, and estimated completion dates for:<\/p>\n
Working Normal workdays (Monday-Friday and taking holidays off)<\/p>\n
Working Monday-Saturday and taking holidays off<\/p>\n
Working Monday-Saturday and working the holidays<\/p>\n
Finally, please label and the worksheet tabs appropriately. Ensure all columns are wide enough for the data contained in them. Format column headings to make the pages easier to read.<\/p>\n","protected":false},"excerpt":{"rendered":"
Scenario You have been hired to work for a construction company that builds homes. Your company is currently building homes for 47 customers. You notice that your secretary is constantly receiving phone calls from customers asking when their house is scheduled to be completed. You volunteer to create an Excel workbook to help! Specifically, the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[10],"class_list":["post-107536","post","type-post","status-publish","format-standard","hentry","category-research-paper-writing","tag-writing"],"_links":{"self":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts\/107536","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/comments?post=107536"}],"version-history":[{"count":0,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts\/107536\/revisions"}],"wp:attachment":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/media?parent=107536"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/categories?post=107536"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/tags?post=107536"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}