Beyond the Book
Part 1: What-If Analysis with Data Tables
All-Around Vision Care sets a goal for each office to expand its services each year. It establishes a percentage growth rate but uses an adjustment factor for each city to recognize differences in its markets. You have been asked to build a one-variable data table that tests several overall growth percentages to see the effect on the revenues forecast. You also will build a two-variable data table for Boston where you’ll illustrate revenues if both the growth percentage and its adjustment factor are changed.
1. Open the worksheet Revenues
2. Trace dependent cells for cell H4 to see how the overall growth rate affects the 4-year revenues forecast in cell H18. Hint: click “Trace Dependents” in the Formula Tab | Formula Auditing group 3 times. Click “Remove Arrows” after you have completed your evaluation.
3. One-Variable Data Table
a) You would like to evaluate growth rates between 1.00% and 3.00%. Interpolate the data to fill in the cells A26:A32. These percentages represent possible overall growth rates to replace the current value in cell H4. Your goal is to explore various growth rates using a constant amount of increase from 1% to 3%, rather than a constant percentage increase. Interpolating from a Starting Value to an Ending Value EX9-6b
b) Set up your one-variable data table by creating a reference to the four-year revenue forecast in cell B24. Make the number bold. EX 8-3 Working with Data Tables. Watch this video: https://www.excel-easy.com/examples/data-tables.html
c) Create a data table that shows revenues if the overall growth rate is set to the percentages shown in cells A25:A33. Format the results to improve readability.
d) Use Conditional Formatting to highlight the Overall Growth Rate AND the corresponding Revenue Forecast on your one-variable data table, dependent on what is entered into cell H4. For instance, if you enter 3.00% in cell H4, cells A33 and B33 should be highlighted.
4. Two-Variable Data Table
a) Next, create a data table with the result in cells F24:I31 that shows Boston Four-Year Total revenues when the overall growth rate and Boston’s adjusted growth factor are varied.
b) Use Conditional Formatting to highlight the Overall Growth Rate, the Boston Adjustment Factor AND the corresponding Revenue Forecast on your two-variable data table, dependent on what is entered into cells H4 and H6. For instance, if you enter 2.25% in cell H4 and 1.75 in H6, cells E29, H23 and H29 should be highlighted.
5. Save your file now and often!
PART 2: What-If Analyses with Solver
Michael Abbas is a manager at the Daybreak movie theater. One of his jobs is to create the weekly work schedule for the movie ushers. The theater employs 15 ushers—13 are fulltime and two are parttime. The theater needs eight ushers to work each weekday, 11 on Saturday, and 10 on Sunday. Michael is currently working on the usher schedule for the second week in August. Michael tries to accommodate all timeoff requests while maintaining the required staff. He has been developing a workbook to automatically generate the work schedule. The workbook already contains the names of the ushers and their timeoff requests. He asks you to determine which ushers will be working which days. Complete the following:
1. Use the numbers 0 and 1 to indicate which shifts each employee is working. For example, 0 indicates an employee is not working that day, whereas 1 indicates that employee is scheduled to work.
In the Schedule worksheet, in the range D5:J19, enter 0 in each cell to indicate that you have not yet scheduled any shift for any employee.
2. In the range K5:K19, enter formulas to calculate the total number of shifts for each employee.
3. In the range L5:L19, calculate the total number of hours worked by each employee. Each shift is eight hours.
4. In the range D21:J21, enter the number of shifts required per day. The theater requires eight shifts on weekdays, 11 shifts on Saturday, and 10 shifts on Sunday.
5. In the range D22:J22, enter a formula that calculates the total number of shifts actually scheduled for the employees on each day.
6. In the range D23:J23, enter a formula to subtract the attendants required value from the attendants actually scheduled value. A negative number indicates that not enough employees have been scheduled to cover the day’s shifts.
7. In cell D25, calculate the total shortfall in shifts by entering a formula to total the values in the range D23:J23.
8. Create a Solver model that sets the value of cell D25 to 0 (indicating that all shifts are covered) by changing the values in the range D5:J19 under the following constraints:
a. Add a binary constraint to force every value in the range D5:J19 to be either a 0 or a 1.
b. Add a constraint to limit the total hours worked by each fulltime employee to less than or equal to 40.
c. Add a constraint to limit the total hours worked by each parttime employee to less than or equal to 24.
d. Add a constraint to require that the difference values in the range D23:J23 all equal 0.
e. Based on the entries in the range C5:C19, add constraints so that employees are not scheduled to work shifts on days when they are unavailable to work.
9. Run the Solver model using the Evolutionary method. (Note that Solver might take a minute to arrive at a solution.) Confirm that the schedule generated by Solver fulfills all of the requirements—all shifts are covered each day, no employee works more hours than allowed by his or her full or parttime status, and no employee works on a requested day off.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
