it is a project with excel. I have uploaded the instruction. I have chosenGUESS INC as my client and KATE SPADE &C0 as Benchmark 1 and MICHAEL KORS HOLDING LTD as Benchmark 2
Client Analytical Procedures Project ACT 622 Class Project Preliminary Analytics Analytical Procedures performed at the Beginning of the Audit, in the Planning Stage All the firms on the following list are publicly traded retail firm. Choose 4 firms from the following list. One will be your client firm, and the other three firms will be used to calculate an average benchmark for comparison. Make a note of the GVKEY and Company Name of the four companies so you can report them on the template. Important: Before you start, check the Blackboard Discussion Board to see if anyone else has claimed the same client firm and three benchmark firms. If no one has claimed your client firm as their client firm, and no one else has claimed the *same combination* of three benchmark firms, then post on the discussion board that you will be using these four firms. List the client name and GVKEY, and the three benchmark names and GVKEYS. The GVKEY is the firm’s assigned ID number, and you can find it in the data file next to the firm name. Note that it is ok if someone else is using one or two of your benchmark firms, as long as no one is using all three of the same benchmark firms. For example, below you can see that Student 1 signed up first, so no firms were claimed. Student 2 can select Firm A for a client, because no one has chosen Firm A as a client before. Student 2 can also use Firms X, A, and B as benchmark firms, because they have not been used together as a benchmark by anyone else. Notice that Student 3 has made a mistake! Student 3 can claim Firm C as a client, but cannot use Firms X, A, and B for his/her benchmark because Student 2 has already claimed this combination. Student 3 should change one of his/her benchmark firms so that he/she is not using the same exact combination of 3 firms. Firms X, A, and C would be ok for a benchmark for Student 3. If you have questions, please send me an email. Don’t forget to post on the discussion board and claim your firm combinations before you start! Student 1: Client – Firm X Benchmark – Firm A, Firm B, Firm C Student 2: Client – Firm A Benchmark – Firm X, Firm A, Firm B Student 3: Client – Firm C Benchmark – Firm X, Firm A, Firm B You will need to save 12 lines of data in the spreadsheet: 3 years of data for your client firm, and 3 years of data for each of your 4 comparison firms. FINAL SUBMISSION OF YOUR WORK When you are finished, you should upload two files, 1) the dataset you ultimately worked with and 2) the completed template with ratios and analysis. Make sure to put your name somewhere inside each document because when I download them for grading, Bb does not attach names. To upload the file into Bb, look for the “assignment” in Bb with the official title, “Client Analytical Procedures Project.” Scroll down to find the uploading links for your two files. FILES FOR COMPLETING THE PROJECT Data for all the available firms is in File #1. Notice that the data covers three years as follows: Firm 1 Year 1 Firm 1 Year 2 Firm 1 Year 3 Firm 2 Year 1 Firm 2 Year 2 Firm 2 Year 3 Firm 3 Year 1 …etc. … etc. File #2 is an Excel spreadsheet containing a template where you will enter the values you calculate for your client firm and for the benchmark average. Calculate each of the data points and ratios for 3 years. In the left three columns will be your client firm, in the next three columns will be the annual average of your three benchmark firms. On the right side of the template, there is a space to make comments. Start the comments by stating if the three years of data for your client firm is favorable, unfavorable, or mixed relative to the three years of data for the benchmark firms. It is not enough to say there is an increase or decrease, because this does not identify whether it is a favorable or unfavorable change. The most recent years’ data represents your client’s unaudited financial statements. This is the year you will be auditing, and your job is to evaluate whether, after comparing to the benchmark firms, there is an increase, decrease, or no change in risk for your audit of this client, relative to each line item. Explain your reasoning. Consider the audit risk model (control risk, inherent risk, and acceptable audit risk), identification of significant accounts, and consider materiality. Using course terminology will demonstrate your comprehension of course material and improve your grade. Table 1 below is a legend to let you know what the data abbreviations stand for. You may not need to use all of these variables. Table 2 is a summary of the formulas for calculating the ratios. Table 3 is a list of the firms you may choose from. These are the firms in the database. As in real life, all of these variables may not be included in the dataset, and all the data in the dataset may not be required for the final product, but you should have what is necessary to complete the analytical procedures for the client risk assessment. Table 1 Data Description Abbreviation Data description ACT CURRENT ASSETS AT TOTAL ASSETS CEQ COMMON EQUITY CHE CASH AND EQUIVALENTS CLG CONTINGENT LIABILITIES DCLO CURRENT PORTION OF LT DEBT DLTT LT DEBT IBC INCOME BEFORE DISCONTINUED ITEMS – STMT OF CASH FLOWS INVT INVENTORY LCT LIABILITIES CURRENT TOTAL (TOTAL SHORT-TERM LIABILITIES) PPENT NET PROPERTY PLANT AND EQUIPMENT RE RETAINED EARNINGS RECTR TRADE RECEIVABLES COGS COST OF GOODS SOLD DVC CASH DIVIDENDS EBIT EARNINGS BEFORE INTEREST AND TAXES REVT TOTAL REVENUE XINT TOTAL INTEREST XRD R&D EXPENSE CSHO SHARES OUTSTANDING PRCC_F FISCAL YEAR END PRICE Table 2 Ratios Ratio Name Formula Current Ratio ACT/LCT Quick Ratio CHE/LCT Operating Cash Flow Ratio IBC/LCT Receivable Turnover REVT/RECTR Days Outstanding in Accounts Receivables 365/REVT/RECTR Inventory Turnover COGS/INVT Days of Inventory on Hand 365/COGS/INVT Gross Profit Margin (REVT-COGS)/REVT Profit Margin EBIT/REVT Return on Assets EBIT/AT Return on Equity EBIT/CEQ Debt to Equity DLTT/CEQ Time Interest Earned Ratio EBIT/XINT Market Value PRCC_F * CSHO Table 3 Companies to Choose From COMPANY NAMES CINTAS CORP TALON INTERNATIONAL INC KATE SPADE & CO COLUMBIA SPORTSWEAR CO OXFORD INDUSTRIES INC GILDAN ACTIVEWEAR INC PVH CORP BEBE STORES INC SUPERIOR UNIFORM GROUP INC DELTA APPAREL INC UNIFIRST CORP CARTER'S INC VF CORP IRONCLAD PERFORMANCE WEAR WACOAL HOLDINGS CORP UNDER ARMOUR INC NAKED BRAND GROUP INC EVER-GLORY INTL GROUP INC LVMH MOET HENNESSY LOUIS V KBS FASHION GROUP LTD VINCE HOLDING CORP LULULEMON ATHLETICA INC G-III APPAREL GROUP LTD CHINA XINIYA FASHION LTD-ADR ELLIS PERRY INTL INC RYU APPAREL INC GUESS INC MICHAEL KORS HOLDINGS LTD RALPH LAUREN CORP DELTA GALIL INDUSTRIES LTD TEFRON LTD