Doug Gold is an accountant for Ward Consulting, a firm that provides research services for various corporate and government agencies. Each month, Doug provides the controller with an analysis of the outstanding accounts. Doug uses Excel to track these accounts. He asks you to enter formulas to allow him to analyze the data. Complete the following:
1. Open the Receivables workbook, then save the workbook as Receivables Overdue.
2. In the Documentation worksheet, enter your name and the date.
3. In the Invoices worksheet, in cell B1, enter 7/1/2016 as the current date. Note the defined name CurrentDate has been assigned to cell B1.
(Troubleshoot) 4. The sales rep commission rate varies for each sales rep. In column D, Doug used a VLOOKUP function to look up the commission rate for each sales rep, and then multiplied the commission rate by the invoice amount to calculate the commission. Although the first two rows in column D of the Excel table named Aging display the correct commission, all the other cells display #N/A. Find the problem with the formulas in the Commission column and fix it.
5. In column G, calculate the days past due. If the number of days since the invoice was sent (CurrentDate – Invoice Date) is greater than 30, calculate the days past due (Current Date – Invoice Date – 30); otherwise, enter 0.
6. Create the following formulas to assign the value in the Invoice Amount column to one of five columns— Current, 1-30 days, 31-60 days, 61-90 days, and Over 90 days:
a. In the Current column, create a formula to display the invoice amount (column F) in the Current column if the number of days past due is 0.
b. In the 1-30 days column, create a formula to display the invoice amount if the number of days past due is greater than or equal to 1 and less than or equal to 30.
c. In the 31-60 days column, create a formula to display the invoice amount if the number of days past due is greater than or equal to 31 and less than or equal to 60.
d. In the 61-90 days column, create a formula to display the invoice amount if the number of days past due is greater than or equal to 61 and less than or equal to 90.
e. In the Over 90 days column, create a formula to display the invoice amount if the number of days past due is greater than or equal to 91 days.
f. Format columns H through L in the Accounting format with two decimal places.
7. The invoice amount (column F) for each invoice can only appear once in columns H through L. In column N, do the following to create a formula to verify this rule.
a. In cell N3, enter the label Error Check.
b. In the range N4:N105, enter a formula using the IF and COUNT functions. The logical test of the IF function counts the number of the cells that have an entry in columns H through L for each invoice. If the count is greater than one, the formula displays Error; otherwise, it leaves the cell blank. (Hint: click to select one cell, not label)
8. Copy the Invoices worksheet to a new sheet and name is Overdue Accts. (Press Ctrl + L-button and drag and drop to copy a sheet.) In the Overdue Accts worksheet, do the following:
a. Filter the records so only invoices whose balance is past due are displayed.
b. Sort the filtered data by invoice date (oldest first).
c. Include a Total row in this table and display sums for columns I through L. (Quick Analysis)
d. Hide columns C, D, F, H, N (Home – Format – Hide and Unhide)
e. Remove the filter buttons and gridlines from the table. (Hint: Use options on VIEW tab and the TABLE TOOLS DESIGN tab.)
(Troubleshoot) 9. In the Invoice Reports worksheet, Doug used the COUNTIF function to count the number of invoices for each sales rep. The formulas he created display only zeros. Fix the formulas in the range B3:B7 so that they display the number of invoices processed by each sales rep.
10. In the Invoice Reports worksheet, complete the Sales Rep Analysis report. In the Commission and Total Amount columns (columns C and D), use the SUMIF function to summarize commissions (column D in the Aging table in the Invoice worksheet) and the invoice amount (column F in the Aging table) for each sales rep. In row 7 of the report, calculate the totals. Format these columns appropriately.
11. In the Invoice Reports worksheet, complete the Accounts Receivable Aging report in the range F1:H8 by creating formulas that count the number of invoices for each group in the invoices worksheet and sum the total amounts for those invoices.
12. In the Invoice Reports worksheet, in the range A12:B17, use the COUNTIF, SUMIF, and AVERAGEIF functions to complete the report. (Hint: The formulas will reference the Invoice Amount (column F) in the Invoices worksheet. Review Figure 8-39 to see various ways to enter criteria in the COUNTIF, SUMIF, and AVERAGEIF functions.)
a. In cell B15, use the COUNTIF function to count the number of invoices greater than the amount in cell B13.
b. In cell B16, use the SUMIF function to add the total value of invoices greater than the amount in cell B13.
c. In cell B17, use the AVERAGEIF function to calculate the average value of these invoices.
13. In cell B13, enter 1000 as the invoice amount above which invoices are included in the report.
14. Save the workbook, and then close it.
Work Laws in India Distributed: third October, 2016 Last Edited: second October, 2017 Disclaimer: This exposition has been put together by an understudy. This isn't a case of the work composed by our expert article scholars. You can see tests of our expert work here. Any sentiments, discoveries, conclusions or proposals communicated in this material are those of the writers and don't really mirror the perspectives of UK Essays. Catchphrases: work laws india, indian work laws Presentation Work law is otherwise called the Employment Law. The Indian Constitution gives premise of laws to control the work in India. These laws are by and large known as the Labor Laws or the Industrial Laws. As it were it is the "assemblage of laws, regulatory decisions, and points of reference which address the lawful privileges of, and confinements on, working individuals and their associations". These arrangement of laws give the premise to any kind of communication or intervention between the businesses and the exchange associations. It characterizes the legitimate limit of the privileges of both the businesses and the representatives at the work environment. The work laws broadly cover the accompanying: a. Mechanical Relations b. Wellbeing and Safety at Work Place c. The benchmarks which the businesses should hold fast to which incorporates yet isn't restricted to occasions, leaves, rejections, the lowest pay permitted by law and such. This investigation is basically in light of the Industrial Relation part of the Labor Laws. In the event that we investigate the historical backdrop of work laws we see that the need to figure work laws emerged due to the representatives requesting better working conditions and the concurrent requests by the businesses to confine the forces of the workers in the association. The businesses expected that the exchange associations may rise above the mechanical debate and increase spontaneous political forces. The ILO or the International Labor Organization was the principal official body to manage these issues. It was set up as an office of the League of Nations following the Treaty of Versailles, which meant that the apocalypse War I. Presently we center around the Industrial Relation part of the Labor Laws. Modern Relations, as the name proposes fundamentally manages the representatives and the administration which result straightforwardly or in a roundabout way from the business worker relationship. It especially manages the connection between the association and a gathering of laborers by and large known as the Trade Unions. It relates to the investigation of free aggregate dealing, exchange unionism, and the work administration connections. According to the Industrial Disputes Act of 1947 Industrial Relations can be characterized as the connection amongst business and workers, representatives and representatives and representatives and exchange associations. Work relations can be acknowledged at any level of the work put like the shop floor, territorial level or at the national level. Hardly any qualities of Industrial Relations: a. It is non-existent if the gatherings included i.e. exchange association and the business are truant. b. It might incorporate both clash and co-tasks. c. It might cover imperative ecological issues like a nation's innovation ability, socio-political condition, monetary condition and so on. d. It consolidates the laws made by the legislature to represent the business worker relationship, honors of courts, and the impedance examples of the legal on the work administration relations directions. As a general rule the ideas associated with Industrial Relations originate from a wide assortment of fields extending from disciplines like humanities, sociologies, conduct sciences, laws and so forth. Mechanical Relations in India: In the pre-1991 time the contentions and question in the association were tended to through exorbitant work enactments. The laws were defensive in nature and secured every one of the parts of the work environment like cutbacks, wage issues, motivators and so forth. At the appointed time of time it was understood that these laws were defensive in nature and that they prompted authoritative wastefulness. With the coming of globalization the defensive idea of these laws demonstrated lacking for the Indian business to stay focused as it was extremely inflexible and did not enable the Indian makers to contend in the worldwide market. Post progression the laws supported the industrialists and they started plans like representative decrease and the willful retirement plans. Along these lines we can particularly see that globalization got some emblematic changes the Industrial Relations in India. They might be summed up quickly as takes after: a. Decentralization the aggregate dealing in every one of the segments. b. Amid an expansionary period of the economy there is a deficiency of capable administrators and talented work. c. The quantity of associations has expanded essentially yet their impact has reduced. d. In the saving money segment, for instance, a few associations are setting up an assembled front. e. Inner Unions are being pushed by managers which basically suggest that no outer alliance is being looked for. f. Preparing and ability advancement are viewed as a critical piece of work put. Point of Industrial Relations: The real point of Industrial Relations is to protect the enthusiasm of the works and the representatives all in all by a feeling of common assention and comprehension between the two gatherings. It decreases clashes and difficulty between the gatherings for an amicable presence of the Indian business. It likewise guarantees that an abnormal state of efficiency is guaranteed. They additionally help to diminish the strikes, bolt outs and different types of barricades by encouraging common comprehension and assent. The Industrial Relations arrangements additionally guarantee that a situation of Industrial Democracy is kept up for the advantage of the business and the nation in general. Greater government control with a specific end goal to manage the segments which are at misfortune so open enthusiasm everywhere can be shielded. Socializations or justification of businesses by making the express a noteworthy boss in itself. Sick Effects of Poor Industrial Relations: Poor modern relations strategies impede the ordinary working of the business in this manner influencing the whole economy all in all. It might be noticed that the immediate misfortune to a specific plant may not be colossal due a contention but rather the aggregate misfortune to the economy is huge. On the off chance that we don't have a vigorous approach set up then there would be inflexibility to change in this unique circumstance. Indeed every worker works to gain as well as to fulfill his proud and social needs. Poor modern relations approaches take a substantial toll on human conduct regarding strain. In this manner appropriate arrangements should be set up to keep up friendliness and stay away from social pressure. Contract Labor in India: A representative is known as an agreement worker when he is enlisted through a temporary worker. They can be named as aberrant workers who are basically procured, administered and paid by a contractual worker. The temporary worker is paid thus by the foundation who needs these agreement workers. Be that as it may, contract workers must not be utilized when the work is enduring and will proceed every day, when the work is sufficient to utilize all day specialists. The Contract Labor (Regulation and Abolition) Act appeared in 1970 and the Contract Labor Central Rules became effective in the year 1971. The fundamental destinations of these laws were to manage the work of Contract Laborers and to accommodate its nullification in specific situations. The law lays certain principles and directions as to procuring of agreement work, their wages, working conditions and so forth. According to the rules the law is material to any industry which connects in excess of 20 specialists on an agreement premise. The means associated with enlisting the agreement work are: Enquiry Offers Near Statement Transaction Honor the Contract The Act likewise has arrangements that characterize certain comforts that should be given to the agreement workers. These might be the foundation of flasks, rest room game plans, adequate drinking water offices, and medical aid. It is to be noticed that these courtesies are made mandatory by the Act. On the off chance that the temporary worker neglects to pay the wages to the laborers the Principal Employer is at risk to make the installment. The arrangement of agreement work has permitted firms huge adaptability to pick what is to their greatest advantage. In the event that it were absent organizations would need to go for innovative rebuilding which thusly would mean less number of perpetual representatives. The Trade Unions anyway restricted this go about as this implied decrease in changeless business. Additionally, as the agreement workers have a place with the weaker areas of the general public they might be denied of the advantages. The measure of time devoured in planning with the contractual workers and the sub-temporary workers would likewise be significantly more when contrasted with the standard business.>GET ANSWER