BUS104 Microsoft Excel Assignment

This BUS104 – Microsoft Excel Assignment is an excel-based management assignment. It is quite similar to a case study as well. It’s first priority is to develop a pivot table according to given dataset (excel file). If you need similar help, register & post your assignment to get instant help from our Excel experts!

Assignment Type: Excel Assignment

Assignment Category: Business Management Assignment Help

Platform: The assignment is on Microsoft Excel

University: Unknown

Template: Unknown

Assignment Size: 1 hour

Attached File(s); None.

Full Introduction for BUS104 Microsoft Excel Assessment

You are part of a new top management team at a long-haul trucking company. This company started by moving agriculture products across the country. The new team decided to expand to other types of cargo such as electronics, and industrial products such as chemical. The change had been good for the company as the profit has been improving steadily in the past few years and the team has decided extend their success. The team has decided on the following options:

• Decide if another service center is necessary to maintain the fleet of trucks. Currently, you have a service center in Denver. Should your team decide to have another service center, where do you want to build the center?
• There are three difference cargo type, Agriculture-Food, Commercial-Electronic, and Industrial Chemical. If the company has enough resource to push for expansion in one type of cargo, should the company push for more Agriculture-Food, Commercial-Electronic, or Industrial Chemical business?

Alternatively, should the team seek a specific route to expand or are there routes that should be dropped? To arrive at the best answer to this important decision, the top management have asked the information resource department to collect aggregate operations from the past three years showing year and quarter, trucking start and destination, cargo type, and number of trailers moved (see Excel Assessment.xlsx for the details).

The rate for each city, distance between city, and rate for each cargo type are also included in the worksheet.
The rate per mile that you charge to the customer would depend on the rate for each city, and the type of cargo. Hence, for an agriculture-food cargo from Chicago to New York City, the company would charge 2.0529 because rate for Chicago is 0.9029, rate for New York is 1.0000, and rate for agriculturefood is 0.1500.

Detailed instructions

Due to insurance, license fee, and various other assessment from the government, it is estimated that each trip requires about $3,000 of standard fixed cost, and paying the truck driver, highway toll fees, and cost of fuels is estimated to be about $0.75 per mile.
Complete the following steps to analyze the worksheet before answering the questions.
In cell;

• H2 enter Revenue
• I2 enter Profit
• J2 enter Destination City Code
• K2 enter Distance
• L2 enter Original City Rate
• M2 enter Destination City Rate
• N2 enter Cargo Rate
• O2 enter Rate to Charge
• P2 enter Fix Cost
• Q2 enter Cost (@ 0.75 per Mile)
• In row 3 (below the titles that you just entered)
• Use VLOOKUP to determine the Destination City Code

Short explanation of VLOOKUP:
VLOOKUP uses at least three inputs: value to check (or thing that you want to search)
where to check it (or the table that contains the data) which answer is needed (or the column where the answer is located)
Hence, in this case, what you need to put into cell J3 is:
=VLOOKUP(C3,CityData,2)
You need the “=” to tell Excel that you are starting a formula. C3 is where the column where destination city on the row. CityData is a range-name of where the code is located. The table CityData is between T2 to V16 2 is the second column of the CityData table.

Hlookup

Use HLOOKUP to determine the Distance between the Origin City and Destination City.
Short explanation of HLOOKUP:
HLOOKUP also uses at least three inputs: value to check (or thing that you want to search) where to check it (or the table that contains the data) which answer is needed (or the ROW where the answer is located) Hence, in this case, what you need to put into cell I3 is =HLOOKUP(B3,CityDistance,J3) Again, you need the “=” to tell Excel that you are starting a formula. B3 is where the column where origin city (where the trip started) on the row.

CityDistance is a range-name of where the code is located. The table CityDistance is between X2 to AK16 J2 has the row where the answer can be retrieved from the CityDistance table. Use VLOOKUP to determine the Origin City Rate Remember Origin City is in column B and the rate is the third column of CityData Use VLOOKUP to determine the Destination City Rate Use VLOOKUP to determine the Cargo Rate Cargo is in column F, and the table for cargo is named as CargoData Now that you located the three rates, origin city, destination city, and cargo rate, add them up and put in Rate to Charge column.

You can use =SUM(…) or add up the individual cells. Highly recommend using =SUM() The Fix Cost is $3,000, so enter that number in column P. A better option is to enter $3,000 in some other cell (like S2) and make a reference to the cell in column P. You may need to turn on ABSOLUTE referencing when entering this formula because if you copy the formula to any other place, you would want the reference to $3,000 to remain in place. The Cost Per Mile is $0.75, hence multiple 0.75 with the distance that you found in column K.

Following…

Again, you may want to put 0.75 in some cell (like S3) and make a reference to that cell in column Q. If you do, you will need to turn on absolute referencing when entering the formula.
Now go back to the Revenue column. Revenue is just the total rate (Rate to Charge) column O multiply by the Distance column K multiply by the (Number of Trailers) column G.

The Profit is the difference between Revenue and the sum of Fix Cost and Cost Per Mile. Excel follows order of operations so the following will NOT work. =H3-P3+Q3 If you are interested, compare your formula with =H3–sum(P3,Q3)

Assuming you only enter the formula in row 3. You will now need to duplicate (copy) all of the formula you entered to the rest of the rows (to row 1764). You can search YouTube to find a faster/quicker way to duplicate/copy the formula.

Now that you populated the table with a set of analysis, create couple of Pivot Tables by selecting Insert (top menu bar) and located PivotTable (the first icon on the left) If you did not do something unusual, selecting OK will create the PivotTable for you.

Once you created your FIRST PivotTable, check the box for Origin City and Destination City (Upper Right Corner), then drag the Destination City from the Rows (Lower Right Corner) to Columns (again, Lower Right Corner). Depending on the question, you will need to check Revenue, Profit, or Number of Trailers.

Go Back to the Data sheet (lower left corner), create a second PivotTable, check the box for Cargo Type and Year. Now drag “Year” from the Values (lower right corner) to Columns. Again, depending on the question, you will need to check Revenue, Profit of Number of Trailers to answer the question.

Legal Note for BUS104 – Excel Assignment

The assignment file and details given on this page is just an example file for BUS 104 Microsoft Excel Homework. If any individual/institution believe publication of this assignment is offensive; please contact with us using Contact Form and we will get back to you in shortest possible notice.

Pure Assignment has established in Turkey. Pure Assignment is only charged of Turkish law and all matters discussed in Terms of Use and the other subtitles must be considered according to Turkish Law. According to related law (law number 5651) published in official paper;

MADDE 5 (Article number: 5)–

(1) Yer sağlayıcı, yer sağladığı içeriği kontrol etmek veya hukuka aykırı bir faaliyetin söz konusu olup olmadığını araştırmakla yükümlü değildir.

(1) The site provider is not obliged to check the content it provides or to investigate whether there is an illegal activity. (Translated by Google Translate)

(2) Yer sağlayıcı, yer sağladığı hukuka aykırı içerikten, ceza sorumluluğu ile ilgili hükümler saklı kalmak kaydıyla, bu Kanunun 8 inci ve 9 uncu maddelerine göre haberdar edilmesi halinde ve teknik olarak imkân bulunduğu ölçüde hukuka aykırı içeriği yayından kaldırmakla yükümlüdür.

(2) The site provider is obliged to remove the illegal content from the publication; if it is informed in accordance with the 8th and 9th articles of this Law. And to the extent technically possible, provided that the provisions regarding the criminal liability are reserved for the illegal content it provides. (Translated by Google Translate)

Pure assignment offers the Site and Site Services for clients to be able to share any freelance job to let them find the help from the right expert they need with their business or assignments; papers and projects; either for business purpose or educational purpose. Pure assignment aims to get clients and freelancers together to be able to help them each other. In this case, Pure assignment works as a service provider as mentioned in law (law number 5651).