I have homework due tomorrow about Business Analytics Modelling. All in 1 excel file includes macros needed to be created
Documentation BIT 3424 Raji Raman Student Name:Gokcen Matthews Unique File ID:16798 First Open:done Honor Policy: I swear to uphold the Virginia Tech Honor Policy (as specified in the course syllabus). I will not copy another student's file. I will not share my file with another student. Student Initial:GM P5 Practice 5 Create a dynamic interface to evaluate employee travel costs. User should be able to sort, format, and calculate report. 4 questions #1: Make two buttons to sort the table by Employee Name or by Client Location. In Excel, name the first cell of the employee table. In VBA, dynamically name the entire table (with labels). Sort in ABC order. #2: Make two buttons to highlight table rows with Number of Night values Smaller or Larger than a given value. Dynamically name the table (no labels). Clear previous formatting rules. Select the table and add new formatting rule. In Excel, name the given number of nights value cell. Highlight rows with smaller costs green and larger costs yellow. #3: Make a button to calculate a report of the Min and Max Costs in the table. Dynamically name the table (no labels). In Excel, name the Costs Report column cells. Use Application.WorksheetFunction to calculate min/max table costs. #4: Make a button to calculate the total nights and total costs for a given Employee. Dynamically name the table (no labels). In Excel, name the given Employee name cell as well as the Total Nights and Total Cost report row cells. Use Application.WorksheetFunction to calculate sum of nights and sum of costs for given employee. EmployeeClient LocationNumber of NightsTotal Cost F. GarciaArlington1$562.50 S. TiagoArlington4$462.50 A. BilakbarAtlanta5$650.00Highlight Number Nights F. GarciaAtlanta5$750.00Nights D. EdwardsBaltimore1$675.00 D. EdwardsBaltimore1$475.00Costs Report P. QuiBaltimore2$575.00Min P. QuiBaltimore3$475.00Max A. BilakbarCharlotte3$250.00 C. DenversCharlotte4$250.00EmployeeF. Garcia F. GarciaCharlotte2$350.00Total NightsTotal Cost G. HoiCharlotte1$450.00 C. DenversDC1$600.00 F. GarciaDC4$500.00 G. HoiDC5$400.00 P. QuiDC1$500.00 S. TiagoDC5$600.00 E. FernandezRichmond2$287.50 E. FernandezRichmond3$487.50 P. QuiRichmond3$487.50 Sort by Client Location Highlight Smaller Highlight Larger Calculate Report Sort by Employee Calculate Employee Report P6 Practice 6 Create a dynamic interface to evaluate upcoming employee travel based on client location and number of nights. Travel costs to client locations are given. This application should be able to filter for given location to find which employee will be there maximum number of nights. The application should also be able to calculate total costs for a given employee based on travel costs and number of nights. 2 Questions #1: Create a button that filters table by client location and finds name of employee who will stay the maximum number of nights. Ask user for a city name. In Excel, name first cell of employee table, then in VBA use a range variable to record current employee table (with labels). Filter the table for the given city name. Copy/paste the filtered table and record the pasted table in a range variable. Calcualte the maximum number of nights from the filtered table. Find the name of the employee who will stay the maximum number of nights. Hint: Use Application.WorksheetFunction.Match and Range().Cells. Delete the pasted range and select the first cell of the employee table. Display a message: "Employee name will be in City Name the max number of nights: max number" with title "Max Nights in City Name". Remove filter from employee table. #2: Create a button that calculates the total travel cost for a given employee. Ask the user for an employee name. Use a range variable to record current employee table (with labels). Look up the client location and number of nights for the given employee name. Then look up the travel cost for the client location in the travel cost table. Hint: You can name this table in Excel. Calculate the total travel cost based on the location cost and number of nights (assume $100/night hotel cost). Calculate the return date based on today's date and the number of nights. Highlight the current row of the table for the given employee. Display a message: "Trip Name: " first initial of first and last name "-" first three letters of location in upper case. Next line total cost. Next line return date. Hint: Ucase() function for upper case; Format(x, "Currency") for total cost; InStr(text to look in, text to find) to find first initial of last name. Upcoming Travel EmployeeClient LocationNumber of Nights A. BilakbarAtlanta5 C. DenversCharlotte3 D. EdwardsBaltimore1 E. FernandezRichmond2 F. GarciaArlington1 G. HoiCharlotte1 P. QuiBaltimore2 S. TiagoArlington4 U. VanceAtlanta4 V. WilsonRichmond4 W. XieArlington2 X. YenBaltimore5 Y. ZeniyaCharlotte4 Given Travel Costs (round-trip) Client LocationTravel Cost Arlington$262.50 Atlanta$450.00 Baltimore$375.00 Charlotte$150.00 DC$300.00 Richmond$187.50 Filter Location and Find Max Nights Calculate Employee Travel Cost and Return Date P7 Practice 7 Create a dynamic interface to allow users to either search current scheduled travel or add new trips. Application should be able to look up information or add new information. Should include error checking. 1 Question #1: Allow user to either lookup trip information by employee name or add new trip information to table. Ask user for menu option: 1 to lookup, 2 to add new. Make sure menu option is valid. In Excel, name starting cell for travel table and name entire location/cost table. In VBA, record range of entire scheduled travel table (with labels). Ask user for employee name. If user wants to lookup info: check if employee name is in first column of travel table. If not, give error message. Otherwise, lookup location, nights, and cost info and give user report message. If user wants to add new trip: check if employee name is already in first column of travel table. Hint: check If Not Range().Find() Is Nothing. If already scheduled, give error message. Otherwise, ask for new location and check that it is in first column of location/cost table. Ask for number of nights and make sure positive number. Calculate total cost = travel cost + nights * $100/night. Output new trip information to new row in table. Hint: Use With start range .End(xlDown) and Offset to new row. Scheduled Travel EmployeeClient LocationNumber of NightsTotal CostClient LocationTravel Cost F. GarciaArlington1$562.50Arlington$262.50 S. TiagoArlington4$462.50Atlanta$450.00 D. EdwardsBaltimore1$675.00Baltimore$375.00 J. JonesArlington2$462.50Charlotte$150.00 M. JaneDC4$700.00DC$300.00 Richmond$187.50 Employee Travel: Lookup or Add New Trip P8 Practice 8 Create dynamic interface to allow user to modify number of nights in scheduled trip. User can lookup trip info and update nights which should also update cost. 1 Question #1: Allow user to update number of nights for scheduled trip (assume only one trip scheduled per employee). In Excel, name starting cell of travel table and entire location/cost table. In VBA, record range for entire travel table. Ask user for employee name. Make sure name is in first column of travel table. Highlight table row for given name. Lookup current number of nights from table for given name. Tell user current value and ask for new number of nights. Make sure number is positive value, otherwise ask them to input again. Make sure nights is a numeric value, otherwise use error handling to return to input prompt. Lookup current location from travel table and note travel cost from location table. Calculate new travel cost with location cost and updated number of nights (for $100/night). Modify the employee row with new number of nights and updated cost. Format new values to be bold and larger font. Give user confirmation message. Make sure to clear previous formats at beginning of procedure. Scheduled Travel EmployeeClient LocationNumber of NightsTotal CostClient LocationTravel Cost F. GarciaArlington3$562.50Arlington$262.50 S. TiagoArlington3$562.50Atlanta$450.00 D. EdwardsBaltimore1$675.00Baltimore$375.00 J. JonesArlington2$462.50Charlotte$150.00 M. JaneDC4$700.00DC$300.00 Richmond$187.50 Update Travel