Select Page

Step 1. Place your order

Fill in the order form and provide all details of your assignment.

Step 2. Make Payment

Choose the payment system that suits you most.

Step 3. Receive your paper

Once your paper is ready, we will email it to you.

Please complete below: ***You have 1.5 HOURS to complete this 2-part assessment.

by | Sep 1, 2022 | Finance | 0 comments

Get Help With Your Essay

"Place your order now for a similar assignment and have exceptional work written by our team of experts, guaranteeing you A results."

For This or a Similar Paper Click To Order Now

 

Please complete below:
***You have 1.5 HOURS to complete this 2-part assessment.
Part 1 – Data Cleansing and Visualization
This component will measure a candidate’s ability to do the following:
A. Leverage the most effective/efficient Excel functions to complete the work in a timely manner.
B. Utilize analysis skills & creative freedom to visually convey pertinent financial information for your Manager to share with the Executive Team
Steps 1 & 2 below must be completed using excel formula functions.
1. The “Vendor Information” tab contains an extract of data for the Information Technology division. On this tab, complete the following:
a. The column “Cost Centre” consists of the “Cost Centre Number” and “Cost Centre Alias”. Split the cost centre number and alias into two columns as follows:
b. Using IF statements, populate the “SVP Grouping” column as seen below, according to the cost centres listed below:
c. Capture the 6-digit GL Account Number in the “GL Account Number” column by splitting the GL Account Number from its Alias located in the “GL Account” column
d. Group the Project Code with the GL Account Number as per below:
e. Using Conditional Formatting, highlight all USD invoices (Invoice Currency Column) in green.
2. Populate the missing data (yellow highlighted cells) on the tables in the tab “PART 1A: EXPENSE SUMMARY”.
3. Create a pivot table summarizing YTD actuals by ‘Vendor Name’ and paste it below the two IT Section tables on the same tab. Sort the YTD actuals in descending order.
4. Display data that you deem relevant from the “Vendor Information” tab in a visual format for an individual at the executive level. Feel free to be as visually creative as possible, summarizing the data and using whichever Excel functionality (Visual Aids, graphs, etc.) that you like. Save your work in the “PART 1B – DASHBOARD” tab.
Part 2 – Business Communication & Critical Thinking
This component will measure a candidate’s ability to communicate effectively in writing and ask pertinent questions to stakeholders.
1. The Finance department leads have just submitted their budget request for 2022. The consolidated results are presented below. Prepare a brief email (on the tab “PART 2A – EMAIL”) updating the Chief Financial Officer (CFO) on the Finance division’s publish budget for 2022. Include information you deem relevant.
publish 2022 budget as of September 2021
2022 Budget by Category
in $000’s 2022
Budget 2021
Forecast Change from Forecast
Increasep / (Decrease)q
Compensation 50,400 44,900 5,500 12%
Technology 8,900 7,200 1,700 24%
Consulting & Professional Fees 4,200 5,100 (900) (18%)
Investment Data Subscriptions 8,500 8,100 400 5%
Investment Custodial Fees 2,700 2,800 (100) (4%)
Travel & Training 890 400 490 123%
Other 1,970 980 990 101%
Total Operating Expenses $77,560 $69,480 $8,080 12%
2022 Headcount by Department 2022 Budget 2021 Forecast Change from
2021 Forecast
Over (Under)q
Financial Planning & Analysis 6 5 1 20%
Corporate Accounting 14 12 2 17%
Accounting Policy & Tax 7 5 2 40%
Investment Reporting 50 45 5 11%
Investment Operations 40 37 3 8%
Total Finance Division 117 104 13 13%
Other details:
• The 2022 compensation budget includes: permanent staff costs, contract/temporary support costs, and other staffing-related costs such as bonuses, severance, and overtime. A merit increase of 2% has been applied to all permanent staff salaries.
• The Investment Reporting and Investment Operations teams directly support HOOPP’s Investment Management (IM) division. The IM division is expecting to add 25 new positions in 2022.
• Of the 13 new hires planned for 2022, 10 are budgeted to join on January 1st.
• The Corporate Accounting department is planning to work on 4 new major initiatives in 2022.
• The Consulting & Professional fees line includes a $400K provision to support exploratory research for a new budgeting system.
• HOOPP’s total investment assets are expected to grow by 8% in 2022.
• HOOPP’s custodian bank is responsible for safeguarding its assets, settling transactions, and collecting dividends/interest payments among other services
• The government announced plans to lift all travel restrictions in Q2 2022.
2. As the CFO of HOOPP, what questions would you ask the Finance department leads to help you assess the reasonability of your division’s funding request for 2022. Record your questions on the tab “PART 2B – CRITICAL THINKING”.
HINT: Use the information presented in the additional details section to determine whether any of the line items appear too high, too low or require further information.
See attached.

For This or a Similar Paper Click To Order Now

Affordable

Only pay once! Clear pricing system with no hidden fees, direct line to your writer, 24/7 customer support.

Quality

Our reputation is built on delivering top-quality papers. No matter how small the task, we give customers our best.

Timely

Missed due dates can result in a serious loss of marks. We strive to finish everything with time to spare.