ICA26-I
pdf
keyboard_arrow_up
School
University of Minnesota-Twin Cities *
*We aren’t endorsed by this school
Course
10
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
Pages
3
Uploaded by AdmiralTree7176
In-Class Assignment #26 (continuation of ICA #25) – Go to Part 3
You are an operations analyst intern for wind energy construction company and have been
asked to examine the utilization of equipment for a particular construction site. Various pieces
of machinery are on site, and you need to figure out which pieces of machinery are soaking up
the most cash and need to be moved off site. You know your boss likes charts and isn’t familiar
with Excel so it would be best to create a model that he can easily understand and use.
Part 1 – Amount Billed - Approximate and Exact Match VLOOKUP
1.
Download the datafile. It contains various pieces of equipment and their corresponding
activity for that week. Each line of data represents one piece of equipment over the course
of one week. Try hitting CTL A (CMD A) then hitting CTL . (CMD .). Notice how this data set
has over 3,000 lines of data.
2.
Look through the data in column E. This information is somewhat overwhelming. The
picture will be clearer if we consolidate the pieces of equipment into broad types. You have
been given a classification system for that consolidation in the tab entitled “Equipment
Dictionary.”
3.
In the Weekly Equipment Report tab, insert a Column F and label it “Equipment Type”.
Using VLOOKUP and the data in the Equipment Dictionary tab, classify each piece of
equipment in Column D by its equipment type. Be sure to use Named Ranges to facilitate
your work.
4.
The owners of the equipment charge by the hour. They also want their equipment fully
used, but not overused. Therefore, the Amount Billed (Column K) equals:
a.
The Operating Hours times the Hourly Rate PLUS
b.
A surcharge based on usage above or below a target of 40 hours per week. The dollar
amount of the surcharge is calculated as the number of hours above or below the target
times the hourly rate times the surcharge rate according to the following schedule:
Utilization
Surcharge
0%
50%
25%
40%
50%
30%
75%
20%
100%
0%
103%
10%
110%
25%
150%
50%
200%
100%
c.
Pause and think about the logic required before you jam a formula in Column J.
Part 2 – Equipment Usage - Interactive Chart
1.
You’ve been requested to create an interactive chart that shows utilization by week for
each type of equipment. In preparation, you’ve already created a worksheet titled
“Equipment Usage Chart” and placed the needed column headers. Take all the Week End
dates (Column B) from the first worksheet and copy them into the new worksheet starting
in cell A2.
2.
Make sure to eliminate the duplicates. If done correctly, when finished, you should have 58
unique Week End dates.
3.
You want to create a chart that allows the boss to change the equipment type interactively
and see the usage of that equipment type over time. Use Data Validation to create a drop-
down list in cell F2. From the Equipment Dictionary worksheet, in Column B you have all the
various equipment types. Copy them elsewhere in the worksheet so as not to disturb the
original list. Again, remove the duplicates. How many unique equipment types are there?
Does that make sense scanning the data? These unique equipment types will represent the
items that will compose your drop-down list in F2.
4.
You are going to calculate the average usage in each of the weeks for any type of
equipment that you select from the drop-down list in cell F2. Hang on. This could get a little
confusing. Go slow. Make sure that you have created Named Ranges for the necessary data
in the Weekly Equipment Reports tab. Specifically, column H (Utilization), Column B,
(Weeks) and Column F (Equip_Type).
5.
Back to the Equipment Usage Chart tab, in cell B2 (the Average Utilization column),
calculate the Average Utilization under the conditions that the equipment type is what
has been selected in cell F2 and the Week End date is the value in column A.
What type of
Conditional Function will this require? What are the arguments in this function?
a.
If you have done everything correctly, and you choose the equipment type Motor
Grader, you should see an 83% usage in the week ending 6/24/18. In the week ending
7/1/18, the average utilization will be 85%.
b.
Note that the #DIV/0 may occur because there is no report of that equipment being on
site during that week and as a result you are dividing by zero, an error. If you don’t like
the way this looks, use the formula IFERROR to program around the problem.
6.
In cell C2 (the Equipment Count column),
calculate the number of pieces of equipment
used under the conditions that the equipment type is what has been selected in cell F2
and the Week End is the value in column A
. What type of Conditional Function will this
require? What are the arguments in this function?
a.
If you have done everything correctly, if you choose Motor Grader as the equipment
type, then in the week ended 6/24/18, that type of equipment was on site 1 time. In the
week ended 8/19/18, it was on site 4 times.
7.
Insert a Combo Chart and select the Clustered Column and Line chart options with a
secondary axis. Title the chart with the name of the equipment type in cell F2 and adjust the
Chart Elements to make the chart attractive. Notice how altering the equipment type in the
drop-down menu adjusts your chart automatically.
a.
Note:
You can make the Chart Title Interactive as well.
Click into the chart title,
move the cursor to the formula bar and type in =F2 (the cell location where the
equipment type is selected).
This is the point where we left off from the previous assignment (ICA #25). The datafile for
this assignment has the problem completed up to step 7. While your own solution from
the previous assignment may work, please use the datafile for this assignment (ICA#26) to
continue.
Part 3 – Equipment Usage - Pivot Charts
1.
Return to the Weekly Equipment Reports tab. Create a Pivot Table and label the tab Pivot Chart.
Now recreate the interactive chart from Part 2 of this problem.
a.
Specifically, you’ll want to be able to select a particular equipment type (e.g., Motor Grader) and
produce a Pivot Chart that displays, as of each Week End, on one vertical axis the Average
Utilization and on the other axis the Count of the Equipment used.
b.
Think about what type of chart will work best. Also consider how you are going to have to
handle the dates to reproduce the chart from Part 2.
Notice that Excel has grouped the dates by
year.
What will you need to do to change this to the weekly values?
c.
How can you make this effectively as interactive as the drop-down menu from Part 2?
Part 4 – Dashboard – Pivot Tables
1.
Now you want to produce a visual review of equipment usage, looking at several different metrics.
This is called a Dashboard.
On a tab labeled Dashboard, create four pivot charts – one for each item
below.
This time as you create the second chart and when asked where you want the chart to be
placed, you will select “existing sheet” rather than a new sheet.
Arrange these pivot tables / charts
in a pleasing manner that will fit on one page.
a.
Equipment inventory pie chart showing equipment counts by equipment types.
b.
Column chart with average utilization by equipment type.
c.
Bar chart with the amount billed by equipment type.
d.
Combo chart displaying average hourly rate and average surcharge billed by equipment type.
2.
Include the data along with the charts in your dashboard.
a.
This dashboard is meant to be an attractive visual. Don’t just create the charts and walk away.
Make your presentation interesting and engaging to the eye.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help