Find number of days each invoice has been outstanding

Assignment Help Business Management
Reference no: EM131029579

Assignment

Tim, the owner of Ace Tile Company, would like you to manage his accounts receivable file, making it automated and organized so that he can easily locate late payers. Open the file Ace_Tile_Company_Accounts_Receivable_Database_and_Aging_Report.xls to see the outline of the accounts receivable file. It contains a sample of accounts receivable list. You need to type in your student ID and Today's Date as prompted when you access the file. The days outstanding for each account should be automatically calculated. You also need to produce an aging report that automatically classifies the records according to four categories of lateness: current (30 days or less), between 31 days and 60 days, between 61 days and 90 days, and over 90 days.

Tim would also like some reporting mechanism to identify late payers so that he can phone them and expedite payment. The accounts receivable file should be sorted first by greatest number of days outstanding to smallest and then by highest invoice balance to lowest. A listing of customers more than 60 days late with outstanding balances over $1200 would also be useful.

Tim's company will upgrade all computers to Windows 10 soon. Therefore, the Excel application (included in MS-Office XP) that has been used since 2002 must be replaced as it is incompatible with the new operating system. Tim has heard of open source software and cloud computing in a small business forum. You are asked to investigate possible replacement options and make a recommendation. The recommended spreadsheet application should be able to process Accounts Receivable largely the same way as it has been done.

Specific requirements:

1. Enter your student ID at cell address C10.

2. Enter today's date at cell address J10 (do not use today function, but just enter date in the form of mm/dd/yyyy like 02/25/2016).

3. Calculate the number of days each invoice has been outstanding in the cell range F15:F35. This is the difference between the transaction date and today's date at J10. Use absolute address if necessary.

4. Classify the invoices using formulas into the four categories using IF statements in the cell range G15:J35. That is, you need to compare the outstanding day of each account with each category (less than 30 days, between 31 and 60 days, between 61 and 90 days, or more than 90 days). Put the transaction amount in the right category, and put 0 in the other categories. For example, if the outstanding day at F15 is 35 days, then put the transaction amount ($63.41) at H15, and put 0 at G15, I15, and H15. Use the AND operator or embedded if statements to express "between .. and.." condition.

5. Enter formulas to calculate sums at cell addresses E38 (total transaction amount), G38 (total current amount), H38 (total 31-60 days amount), I38 (total 61-90 days amount), and J38 (total over 90 days amount)

6. Sort the database in descending order using the number of days outstanding as the primary sort key and the amount as the secondary sort key.

7. At E39, use Count function to compute the number of transactions. At G39 to J39, use CountIF to compute the number of transactions for each aging group.

8. In "late payers" worksheet, use filter to display a list of customers more than 60 days late with outstanding balances over $1200.

9. Return to "Accounts Receivable" worksheet, and in the designated areas, input the recommendation about replacing the current spreadsheet software. At G44, enter recommended spreadsheet application or office suite. At G55, enter cost information. At G46:G50, enter reasons why you recommended this option. At G52:G55, enter sources used for research and recommendation.

10. Format the spreadsheet in an attractive and readable manner.

Attachment:- Ace Tile Company Accounts Receivable Database and Aging Report.xls

Reference no: EM131029579

Questions Cloud

Why has diversity in workplace been given so much attention : Why has diversity in the workplace been given so much attention lately? What are the workforce diversity challenges and opportunities confronting new managers in the 21st century
An investigation on the elements : At the point when directing an investigation on the elements, which add to a percentage of the issues shoppers experienced with the IPhone 6/6 or more, it was apparent that one of the memory chips was defective. This memory chip, fabricated by Samsun..
Flow rate and the change in temperature : What type of relationship exists between the flow rate and the change in temperature?
What is the optimal cost of inventory : Shopkeeper has a uniform demand of an item at the rate of 50 units per month. He buys from supplier at the cost of Rs. 6/- per item and the cost of ordering is Rs. 10/- each time. If the stock holding costs are 20% per year of stock value, how freque..
Find number of days each invoice has been outstanding : Calculate the number of days each invoice has been outstanding in the cell range F15:F35. This is the difference between the transaction date and today's date at J10. Use absolute address if necessary.
Explain why marketing strategy and planning is important : Based on your reading and understanding of the assigned chapters, explain why marketing strategy and planning is important in the competitive business environment. State whether marketing strategy is relevant only to profit-making organizations. C..
The inventory charges per year are 20 percent? : A stockiest purchases an item at the rate of Rs. 40/- per piece from a manufacturer. 2000 units are required per year. What should be the order quantity per order if the cost per order is Rs. 15/ and the inventory charges per year are 20 percent?
Transformational leadership reclassifies : Transformational leadership reclassifies individuals' missions and dreams, restores their dedication, and rebuilds their frameworks for objective achievement through a relationship of common incitement and height that changes over adherents into pion..
Find the x-intercepts and y-intercepts : The number of tickets sold each day for an upcoming performance of Handel's Messiah is given by , where x is the number of days since the concert was first announced. When will daily ticket sales peak and how many tickets will be sold that day?

Reviews

Write a Review

 

Business Management Questions & Answers

  Explain how can flextronic leverage this information

Illustrate what information does Flextronic have that its clients do not? Explain why? Explain how can Flextronic leverage this information. Explain how does IBM manage its suppliers in order to make its pull strategy more effective?

  What is the origins of the scandal of volkswagen

1.What is the origins of the scandal of Volkswagen?2. Each Kind of standards US use to control the quality of Volkswagen vehicles?3.  Provide a brief overview on the standards used by US in the control of the quality of cars from the Volkswagen Compa..

  Political coalition in the decision-making process

The Carnegie model emphasize the need for a political coalition in the decision-making process. When and why are coalitions necessary?

  Case study of john noble

John Noble speculated that Howie really didn't know much about his employees, including the types of rewards that they found meaningful and whether they personally felt that if they worked hard

  What are the differences in leadership at both facilities

What are the differences in Leadership at both facilities and How will changes at Green River need to be implemented to match Leadership skills?

  The irac method of case study analysis

The IRAC method of case study analysis

  Determine upper and lower control limits

For a variable control a circuit voltage will be examined on a sample of only 5-circuits. The last average voltage for sample size five has been 3.1 volt, and the range has ben 1.2 volts.

  Taining and career development1 show list three benefits of

taining and career development1. show list three benefits of distance learning programs. explain how they are

  Using the most important concepts

Write and submit a 500 word paper using the most important concepts you have learned in your business program, Tie the concepts of management, accounting, or finance to your chosen career choice. This paper must be submitted in APA format, or MLA for..

  Propose and discuss at least three strategies leaders

propose and discuss at least three strategies leaders should adopt to enhance their relationships with superiors

  Motivation concepts table

Explain how a theory would or would not be applicable if applied to two or more workplace situations drawn from a personal experience

  Explain the level of significance

Explain At the 0.05 level of significance is there evidence of a difference in the daily customer count based on the price of a small coffee

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd