Shelly Cashman Excel 365 | Module 9: End of Module Project 1 скачать в хорошем качестве

Shelly Cashman Excel 365 | Module 9: End of Module Project 1

Please contact us if you need this project or any other project in any subject to be completed. Whatsapp: +1-343-321-3276 Email: essayserviceusa@gmail.com WhatsApp Link: https://wa.me/13433213276 #shellycashmanexcel365 #Module9EndofModuleProject1 #comedyhouse Shelly Cashman Excel 365 | Module 9: End of Module Project 1 Comedy House FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING PROJECT STEPS 1. Lionel Briones is the financial adviser for the Comedy House, a chain of comedy clubs in the Midwest. He is developing an Excel workbook to send to club owners and managers to help them with payroll, budget, and employee scheduling. He asks for your help in correcting errors and solving problems with the data. Go to the Employees worksheet, which lists employees for a typical Comedy House location and includes a sample paystub. Lionel asks you to correct the errors in the worksheet. Use any error-checking method to determine the source of the #NAME? error in cell G15, which should calculate the average daily wage. Correct the error by editing the formula in cell G15. 2. In cell G23, use the Trace Dependents arrows to identify cells that depend on cell G23. Edit the formula to correct the #NAME? error in cell G23 and its dependent cell. 3. Use the Trace Precedents arrows to find the source of the #DIV/0! error in cell G25. Correct the formula in cell G25, which should divide the net pay (cell G23) by the number of hours worked (cell G19). Remove the trace arrows. 4. Lionel wants you to make sure that the hourly wages entered in the range F5:F13 are between $15 and $35 per hour. Set a data validation rule for the range F5:F13 that allows only decimal number values from 15 to 35. Add an Input Message using Hourly Wage as the Input Message title and the following text as the Input message: Enter a number from 15 to 35. Add an Error Alert using the Stop style, Invalid Wage as the Error Alert title, and the following text as the Error message: Confirm the hourly wage. 5. Circle the invalid data in the worksheet. Type 35 as the hourly wage in the cell containing invalid data. 6. Go to the Budget worksheet, which contains a typical weekly budget for a Comedy House club. Lionel has already created a scenario named Specials that calculates the weekly profit when the club offers specials on food and beverages to increase business. He wants you to create another scenario that compares the weekly profit if the club raises prices. Add a new scenario named Raise Prices that uses the revenue in the range C5:C6, the variable costs in the range C10:C11, and the fixed costs in the range C15:C19 as the changing cells. Enter cell values for the Raise Prices scenario as shown in bold in Table 1, which are the same as the current values except for a change to the Average order per guest. Table 1: Cell Values for the Raise Prices Scenario Cell Value Guests_per_week (cell C5) 2400 Average_order (cell C6) 52 Food_and_beverage (cell C10) 14,000 Supplies (cell C11) 9300 Payroll (cell C15) 78,080 Rent (cell C16) 3800 Utilities (cell C17) 1400 Insurance (cell C18) 550 Miscellaneous (cell C19) 400 7. Lionel is ready to compare the weekly profit for the current values with those in the Specials and Raise Prices scenarios. Create a Scenario Summary report using the weekly profit (cell C27) as the result cell to show how the weekly profit changes depending on the revenue and cost changes. Use Budget Scenario Report as the name of the worksheet containing the report. 8. Return to the Budget worksheet. Lionel also wants to compare the changes to the total costs and the weekly profit with the two scenarios. Create a Scenario PivotTable report using the total costs and weekly profit (range C26:C27) as the result cells. Use Budget PivotTable as the name of the worksheet containing the PivotTable. Format cells B4:C5 in the Budget PivotTable worksheet using the Currency number format with 0 decimal places and $ as the symbol. 9. Return to the Budget worksheet. Comedy House usually hosts a welcome night for each club, inviting local media and others to introduce the club to a new location. Each club has a budget of $1200 for the welcome night. Lionel wants to know how many guests can attend with the costs in the range F5:F8. Use Goal Seek to set the budget (cell F8) to 1200 by changing the number of guests (cell F7). 10. Go to the Schedule worksheet, which provides a sample schedule for employees working five consecutive days with two days off. Lionel wants to find the best schedule for these employees that provides enough staff while minimizing payroll costs. In Solver, use the weekly payroll as the objective cell in the Solver model, with the goal of determining the minimum value for that cell. Use the number of staff on each schedule as the changing variable cells. Enter the constraints based on the information provided in Table 2. Use Simplex LP as the solving method to find a global optimal solution. Save the Solver