Using Excel as Audit Software
You work for the audit firm of Check and Recheck CAs. One of your newest clients is Griffith Thermometers Limited. The company is only 3 years old. You are planning the audit for the year ending 30 June 2015. The client’s Finance Director provides you with the preliminary (draft) balance sheet and income statement information which includes the comparative figures for 2014, which your firm audited last year. (The Excel file “Ass3102152Qs.xlsx” has the data.)
Required
(A) You decide to perform some preliminary analytical review on the information provided by the Finance Director, in order to help with the planning of this year’s audit.
(1) Prepare a common-size (vertical) analysis of both the Balance Sheet and Income Statement for both years. Use “Total Assets” as your base figure for the Balance Sheet and “Sales” as your base figure for the income statement.
(2) Prepare a trend (horizontal) analysis for 2015, comparing all figures to the prior year and noting the % change in each.
(3) For both years, calculate:
2 liquidity ratios (current ratio and quick ratio);
4 activity ratios (accounts receivable turnover, days to collect, inventory turnover, days to sell);
2 long term solvency ratios (debt/equity, tangible net assets/equity); and
2 profitability ratios (net profit and return on total assets).
Use Excel to answer all three parts. Produce working sheets (these will show the formulae you used) to calculate the ratios. (12 marks).
(B) Write a brief (1 page only) memo to your audit senior, cross reference your comments to the appropriate spreadsheets. (2 marks).
The memo should list four areas of possible concern (overall issues and/or specific account balances) based on your findings in part (A) above. (6 marks).