Administrative Assistant Test

Your company sells products to customers in two cities, New York and San Francisco. You have been asked to calculate each product’s retail prices inclusive of sales taxes in these cities.

Specifically, you need to:

• Write formulas to calculate the retail prices, inclusive of sales tax, using the base price of each product.
• Make sure you reference cells A5 and B5 in your formulas.

Note: Do not use array formulas in your work.

Your task is to download the file ‘TaxInclusive-RawData.xlsx’ file and execute the request. When you have completed your work, save it in .xlsx format, then upload that file for evaluation.

An elderly customer calls with a problem finding some product information on your company’s website. He is nice enough, just frustrated and hopeless because he can’t find what he needs. The information he’s looking for is too long and complicated to share over the phone. What are the best next steps?

Your assistant attended several meetings on your behalf while you were out on sick leave. You need to include a summary of each meeting in your next team newsletter. For each meeting below, choose the clearest and most concise way to restate the key points your assistant compiled.

The company's sick leave policy says:

This company’s sick leave policy applies to all our employees who have been with our company for more than six months. Our employees can take sick leave only when they want to recover from a sudden illness, accident, or injury. They can use up to 10 days of sick leave for these purposes per calendar year.

Upon completion of each 12 month period of employment, employees will receive 3 additional days of sick leave for every completed 12 month period of working for the company. Unspent additional sick leave days cannot be transferred to the next 12 month period. For example, an employee who has worked for the company for 10 years would receive 30 additional days of sick leave, or 40 in total.

Keep in mind that employees who become sick should either use their sick days or work from home to avoid spreading illnesses.

Fill in the blanks, with numbers, for the cases below. Enter the number 0 for cases when an employee doesn't have the right to sick leave, according to the policy.

• Emily, who has worked for us for almost half a year and still hasn't used any sick leave days, has the right to use up to __ sick leave days this year.
• Faith, who just started her 3rd year of working for us and has not taken sick leave until now, had a car accident. She has the right to use up to __ days of sick leave to recover from her injuries.
• Vanessa, who started to work for us a little over a year ago, took 2 days of sick leave last year. She was Faith's passenger in the car accident. Vanessa has the right to use up to __ days of sick leave to recover from her injuries.
• Edith, who began working for us 8 months ago, has the right to use up to __ sick leave days for a volunteer adult care program at a local hospital that lasts the whole day.
• If Gabrielle, who has worked for us for 20 months, and who took a 10-day sick leave immediately after her 6-month trial period had finished, gets ill tomorrow, she will have the right to use up to __  days of sick leave.

You are working as a social media manager for a company that has specific expectations of how you will perform your work.

Compare those expectations with how you actually spent your time last week.

Five types of tires were tested in three different driving conditions.

Consider the following table of their test scores:

If a tire type scored 4 or less in any category, it failed the test.

What is the highest average score of the tire types that passed the test?

As a customer success specialist, you can only approve in-app purchase refund requests that are made within time limits determined by whether the customer is an adult or a child and whether the request came through the app store or by email.

NOTE: US customers use the date format MONTH/DAY/YEAR while Europeans use DAY/MONTH/YEAR. According to your company, a child is anyone born after 1/2/2010 for US customers and after the same date of 2/1/2010 for Europeans.

The table below gives the details you need to decide which customers' refund requests to approve.

Whose refund request should be approved?

Dan put a large bet on a horse. However, a day before the race, the horse was injured.

Select which of the following statements are true:

You need to schedule a critical meeting. You're available from 9:30am to 2:00pm.

Angela is available from 10:30am to 2:30pm.

Chris is available from 12pm to 4pm.

Everyone hates meeting during lunch but will if it's important.

When are all three of you available to start a one-hour meeting?

Your company’s accounting department has asked you to give them a list of orders filtered by two periods of time:

• On 'Sheet1', they need orders spanning a two-month period starting from the 1st of July, 2018, and stretching through the 31st of August, 2018.
• On 'Sheet2', they need all orders for the period before the 1st of July, 2018, and all orders for the period after the 31st of August, 2018.

Your task is to download the file 'TimeFilters-RawData.xlsx’ file and execute this request. When you have completed your work, save it in .xlsx format, then upload that file for evaluation.

Your company sells tea and coffee to a variety of customers through its sales representatives. The sales manager is preparing for the annual performance reviews in which he reviews the achievements of all the sales representatives as a basis for discussing bonus compensation and raises.

He asks you to prepare some pivot tables to help him prepare for these reviews:

1. For Pivot Table 1, you’ll show Total Sales by Employee per each Category.

2. For Pivot Table 2, you’ll show Total Sales by CompanyID and Category per each Employee.

3. Ensure that both pivot tables match the layout shown below.

(Open full-size image in a new tab.)

Your task is to download the ‘PerformanceReviews-RawData.xlsx’ file and execute all parts of this request. When you have completed your work, save it in .xlsx format, then upload that file for evaluation.

Your company just introduced a new pricing policy. In order to further motivate customers to buy your products, your company will give discounts on each order based on the total price of the order.

Your boss needs you to:

• Use the data in the Discounts worksheet to build a lookup formula that calculates the applicable discount percentage according to each order amount. Add that formula to column D of the Orders worksheet.
• Write a formula that calculates the final order amount including the discount. Add that formula to column E of the Orders worksheet.

Note: Do not use any array formulas in your work.

Your task is to download the file ‘OrderDiscounts-RawData.xlsx’ file and execute all parts of this request. When you have completed your work, save it in .xlsx format, then upload that file for evaluation.

You have been asked to create a column chart and need to make it look the same as the chart provided in the image below.

Your task is to download the ‘SalesComparison-RawData.xlsx’ file and use your spreadsheet knowledge to create the chart shown here. You should attempt to recreate this chart using any columns colors you want, but be sure to:

• Create a clustered column chart.
• Set up the horizontal axis categories and remove horizontal gridlines to match the image.
• Adjust the vertical axis scale and remove vertical gridlines to match the image.
• Add a chart title, vertical axis title, and legend that match those displayed in the image above.
• Ensure your data series are in the correct order.

When you have completed the chart, save it in .xlsx format, then upload that file for evaluation.

Germany's economy avoided falling into recession during the final three months of last year. This means that it avoided two consecutive quarters of negative GDP growth, which is the definition of a recession. This was a small, but positive, surprise for all analysts who, after the July-to-September period that featured a 0.3% decline, predicted the continuation of this negative trend.

Reasons for slower growth last year include a slowdown in the global economy and a weaker car sector, with some German consumers less willing to buy new cars amid confusion over new emission standards.

Joe Johnson, senior financial analyst, told the BBC that US tariffs on EU car exports, which US President Donald Trump has threatened, could have a major impact on Germany. He thinks that, if this happens, Germany might fall into recession.

What can be concluded from the text above?

A customer phones to complain about a printer she purchased last week that stopped working. The customer tells you the brand of printer and it's clear she has called the wrong number because your company has never sold that brand.

What responses show appropriate customer service in this situation?

Carefully read the following excerpt from an article on paper production:

With the recovery rate of used paper for recycling approaching 70 percent in the United States and Europe, and approaching 80 percent in Japan, to keep up with our demand for paper we need to continue using fresh fiber as well as recycled, according to the premise set forward in a new report by the World Business Council for Sustainable Development (WBCSD).

Detailing the reasons why fresh fiber combined with recycled is important for a single integrated wood fiber system, the report examines the “complementarity” of using both and discusses the functions of different types of fibers and the issues related to both recycling old fiber and sourcing fresh fiber. In 2012, 400 million tons of paper and paperboard were produced and consumed globally, which is double that in 1985, notes the report. As the population continues to expand and standards of living increase, this number is expected to climb by another 40 percent by 2028. While many would advocate for cutting down on paper use in the first place, in the face of global demand the most sustainable fiber may have to be the next best thing. But therein lies the rub: finding adequate quantities of said fiber will be the challenge of the global pulp and paper industry.

Which statement most effectively summarizes the text?

In an upcoming referendum, voters will be asked whether the minimum wage should be increased. Telephone polls of likely voters were conducted to predict what the result might be. All respondents were put into at least one of six categories based on profession and age: scientist, lawyer, hourly wage earner, small business owner, younger voter, older voter.

The opinion polls showed:

• A majority of voters are in favor of keeping the current minimum wage.
• A majority of scientists and lawyers supported a rise in the minimum wage.
• Younger voters are more inclined to support a rise in the minimum wage.
• Older voters tend to support no change.
• Hourly wage earners overwhelmingly supported a rise in the minimum wage.
• Small business owners were evenly split on the subject.

Select all the statements that can be concluded from the above text:

An author writes an argumentative essay to persuade readers to agree with a claim about a topic. When writing an argumentative essay, it's important to establish credibility with readers to convince them that the author is trustworthy. True statements, accurate evidence, and clear logic increase an author's credibility. However, false statements, inaccurate evidence, and unclear logic make an author less credible. With lower credibility, an author is less likely to persuade readers to agree with a claim--even when it is trustworthy.

Select the statement that most effectively summarizes the above text:

In which year did the company make the largest profit per employee?

The charts below show the number of cars John sold last year and the profit he made per car.

Considering all car types, in which quarter did John make the largest average profit per car?

Which product line saw the largest absolute increase in income (dollar amount, not percentage) in the second half of the year compared to the first half of the year?

The charts below show the number of cars John sold last year and the profit per car he made.

How much did John earn last year?

While training to be a workplace investigator, you are taught to read mirrored writing so you can read a suspect's monitor that is facing away from you when there is a window or mirror behind it. To test your skill, you are asked to compare a list of passwords to a list of mirrored images.

_{(Open full-size image in a new tab.)}

Which of the passwords are the same as the mirrored images?

To improve communications, your company's CEO gives you a list of meetings she wants you to schedule between all possible combinations of herself, the CFO, the COO, and the CCO. You come up with your own list to compare with hers.

Based on your list, which meetings are missing, mistaken, or misspelled on the CEO's list?

The chart below shows how many properties an employee sold last month:

What was the total number of properties sold last month?

Your company has been working to add old paper invoices to their new digital system. You have been tasked with checking the implementation to ensure that the details from the paper copy have been entered into the new system accurately.

Compare each item in the list below to see if the version displayed on your screen matches the paper copy.

In the Match? column, choose Y if the items match or choose N if they do not match.

Your staff has submitted the two reports below, which cover the sales and bonus data for seven employees.

Which employees have different information listed between the reports?

No matter how tired Billy is, he never slows down or stops painting. In fact, it would only take Billy 4 hours to paint a fence by himself. It would take Suzy 6 hours to paint the same fence by herself.

On Friday, Billy and Suzy worked together to paint the fence, but Billy got tired after only 2 hours.

How much time did it take to paint the whole fence?

For three days, Jake went to an auction where he spent a total of $150 on auction bidding for 5 porcelain teacups. On the way to the auction's first day, he stopped and spent $5 for a coffee before it started. On the second day he spent $100 more bidding for 3 porcelain teacups than he spent bidding for 2 porcelain teacups on the last day.

How much did Jake spend on his second day?

Cats are common pets throughout the world, and their worldwide population exceeds 500 million.

Cats are the second most popular pet in the U.S. by number of pets owned, behind freshwater fish. Although cat ownership has commonly been associated with women, research has shown that men and women in the U.S. are equally likely to own a cat.

They are ranked as the third most popular pet in the U.K. by number of pets owned, after fish and dogs, with 8 million being owned.

What can be concluded from the text above?

In the summer, Agathe wore an ill-fitting sun hat and oversized sunglasses to the beach in Greece. Both slipped off when she fell asleep in her swimsuit on the beach at 2 p.m.

What will most likely happen to Agathe?

As part of your customer service work for QuikPizza, you monitor the restaurant's social media accounts to make sure that customers have positive experiences with the restaurant. When you come into work one morning, you see the following public post from the night before, which already has 138 likes.

_{(Open full-size image in a new tab.)}

Given that this message is on social media, which individual messages would be appropriate as a response?

David found a vintage watch his father lost 38 years ago. The watch's date showed that it had worked for over two years after his father lost it. When David found the watch, it was 3 times as old as when his father lost it.

How old was the watch when David's father lost it?

You are covering for a coworker who is out on leave. Your coworker has left you a voicemail message specifying the tasks you will need to do for them.

Hi there! Thanks so much for covering for me. So, the main thing you need to do is complete the monthly revenues spreadsheet. You can do that by looking up the latest ad revenue in the reporting from marketing. Ok, so after you do all of that, go ahead and check my email and then my voicemail to see if there are any requests from the executives. If there are any such requests, do those first unless you have an emergency request from another department. Once you’re done with fulfilling requests, please water my desk plant after you turn off the computer. Oh, wait, I should have mentioned a couple of things: you have to log in with manager access in order to see the marketing reports, and you'll need to send me an update email right after you finish handling the requests. OK, thanks again. This is a big help! I owe you lunch when I’m back.

Place the tasks in the correct order.

1st: _____________

2nd: _____________

3rd: _____________

4th: _____________

5th: _____________

6th: _____________

7th: _____________

8th: _____________

9th: _____________

10th: _____________

You have so many small tasks that you don't have enough time for your large project.

Which of the following tasks are good candidates for automation to save you time?

You have partially planned your tasks for tomorrow, as shown in the table below.

Given 8 hours of work and the tasks you already have planned, how many responses to clients can you plan for tomorrow?

(Round your answer to the nearest integer.)

You drafted an email on your phone in poor lighting while in transit. Now back in the office, you need to proofread what you wrote before sending it out. Read the email draft below.

The agenda for next week’s meeting includes presentations from both the marketing and sales teams. Marisol, you will need to has completed the analysis of the most recent campaign by then. You guys need to present slides of those outcomes at the meeting, OK? Meanwhile, Lucas should do something similar for the sales team. Rather than presenting individual case studies, tries to analyze the quarter as a whole this time. Let's nail these presentations and show the executives how much improvement we have made?

Which sentences have errors in grammar or punctuation?

John has some apples, and two brothers, Jack and Jim.

John eats 3 of his apples and splits the remainder equally between himself and each one of his brothers. Jack and Jim both eat half the apples they have been given. Together the three brothers have 4 apples left.

How many apples did John start with?

A brother and sister own equal parts in a company. The minority shareholders have the remaining 12,000 shares or 30% of the company.

What is the number of shares that the sister owns?

It has rained continuously for 15 days. Tomorrow, an important football match is being held in an outdoor stadium.

Select which of the following statements are true:

An eagerly awaited new album has been leaked several hours before its official release. Listeners are now able to download the album for free.

How will this affect album sales?

A client requests a phone call with you at 1:30pm tomorrow their time. The client’s time zone is six hours behind your time zone.

What time would you use to add the phone call to your schedule?    __________

You have been assigned the following list of tasks, and your boss needs you to complete as many as you can in the next two weeks (10 work days) before you go on vacation.

Which tasks will you complete before going on vacation?