Those of you who are done with the Data Entry part may proceed to the
next level - Data Analysis. Please follow the 3 steps mentioned below.
1.
Select rows 60 through 152 of the excel template;
2.
Click the right button of your mouse; and
3.
Select “Unhide” from the menu or just
press the letter ‘u’ in your keyboard.
Following images may help you follow the steps if you are still not clear what to do.
After you unhide the hidden rows, you would see the required ratio and other measures given to you. You then use basic formulas to calculate those measures using the dataset you prepared for the data entry task.
For example, in cell B65 [Column B and row 65] you calculate Current Ratio [= Current Assets / Current Liabilities] for 2011 by inserting this formula: “=B13/B17”(without the quotation marks) where B13 cell refers to Current Assets and B17 indicates Current Liabilities. After successful entry of a formula (or all the formulas) in 2011, you may select and drag along up to whatever year your dataset supports. Walla! All the formulas will be calculated automatically.
You may follow similar strategy for the Yearly Changes/Growths (%) measures as well. For example, for the cell B105 (yearly changes in cash ratio), please type “=(B64-C64)/C64”(without the quotation marks) to calculate yearly changes for 2011.
After you are done calculating all the formulas, now is the time for you to prepare a report using Microsoft word. You need to submit a short report (2 to 3 pages long) on your findings. You may talk about liquidity, solvency, asset management, profitability, market situation of your companies highlighting strengths and weaknesses. You may also talk about important trend or improvement/decline in certain areas in recent years looking at the yearly changes.
Final Report Submission Guidelines
You need to submit both soft-copy (through email attachments of the word report file and all excel files to both of my email addresses) and hard-copy of the word report along with the “Data Entry and Analysis” worksheet as attachments. Along with the hard copy you must submit all the annual reports or financial statements used for your data entry. You will receive bonus points if you do this earlier. Points will be deducted if submitted late. Do remember to use appropriate subject in your email, for example, “Report by [your name and id number]”.
Please don't hesitate to contact me if you have any questions.
After you unhide the hidden rows, you would see the required ratio and other measures given to you. You then use basic formulas to calculate those measures using the dataset you prepared for the data entry task.
For example, in cell B65 [Column B and row 65] you calculate Current Ratio [= Current Assets / Current Liabilities] for 2011 by inserting this formula: “=B13/B17”(without the quotation marks) where B13 cell refers to Current Assets and B17 indicates Current Liabilities. After successful entry of a formula (or all the formulas) in 2011, you may select and drag along up to whatever year your dataset supports. Walla! All the formulas will be calculated automatically.
You may follow similar strategy for the Yearly Changes/Growths (%) measures as well. For example, for the cell B105 (yearly changes in cash ratio), please type “=(B64-C64)/C64”(without the quotation marks) to calculate yearly changes for 2011.
After you are done calculating all the formulas, now is the time for you to prepare a report using Microsoft word. You need to submit a short report (2 to 3 pages long) on your findings. You may talk about liquidity, solvency, asset management, profitability, market situation of your companies highlighting strengths and weaknesses. You may also talk about important trend or improvement/decline in certain areas in recent years looking at the yearly changes.
Final Report Submission Guidelines
You need to submit both soft-copy (through email attachments of the word report file and all excel files to both of my email addresses) and hard-copy of the word report along with the “Data Entry and Analysis” worksheet as attachments. Along with the hard copy you must submit all the annual reports or financial statements used for your data entry. You will receive bonus points if you do this earlier. Points will be deducted if submitted late. Do remember to use appropriate subject in your email, for example, “Report by [your name and id number]”.
Please don't hesitate to contact me if you have any questions.
