Accounting Essay Assignment Help

ACCN 7140: ADVANCED MANAGERIAL ACCOUNTING

Case 1: Analysis and Charting

As a relatively new auditor, you have been assigned the task of performing analytical procedures on the year-end income statement accounts of Delaney Industries. Delaney has been a long-time client and your firm has always had a good working relationship with the company. However, like many clients, questions always arise about increasing audit fees and whether Delaney management will look to “go out for bid” to find less expensive audit services. The engagement partner has clearly indicated to this year’s audit team that they should actively look for ways to demonstrate how the firm’s knowledge of Delaney’s business translates into valuable business advice. The goal – circumvent Delaney’s interest in switching auditors.

Part 1 (use tabs in Excel file)

You have completed the traditional first step for the analytical procedures of income statement accounts by preparing a Horizontal and Vertical analysis of the income statement accounts. A summary of the results are as follows:

Required:

Excel requirements

  1. Complete the horizontal and vertical analysis using the Excel template provided on canvas.
  2. Prepare a breakeven analysis for the current fiscal year.
  3. Prepare TWO Excel visuals that analyze the income statement data.

Power Point requirements

  1. Prepare a 1-page PowerPoint slide that communicates your new insight, i.e., identify a problem you want to solve. (Requires a visual). NOTE: Think about the following problem statement structure – To (solve problem), someone/something SHOULD do (something). The 1st part of the problem statement identifies the problem (Slide 1). The 2nd part of the problem statement provides a recommendation.
  2. Add a 2nd Page to your PowerPoint slide that would communicate your recommendations. Remember the partner’s desire to retain the client.

Part 2 (use tab in Excel file)

Something about the results of your traditional analytical procedures sparked an idea that something more interesting might be going on behind the aggregate financial statement numbers. As a result, you made the decision to make an appointment with the VP of Sales. During the discussion you obtained additional information about the company’s various products. The information allowed you to categorize products by industry segment. You also were able to identify how long various products have been produced and sold by Delaney. As part of that analysis, you recognized that of the product being sold in 2020, one-third represented new products not produced in 2016. You also learned that sales made in previous years were classified as “lost sales” if: (1) contracts have expired, (2) products no longer produced, or (3) Delaney no longer sells to the customer. The VP of Sales also provided you with product profitability results for 2020 and you calculated the contribution margin percentage for each product.

Required:

Excel requirements

  1. Define “Named Ranges” for the Industry and Product variables.
  2. Demonstrate the use of Named Ranges in your formulas.
  3. Using SUMIF, compute the industry subtotals for each year. Be careful: Something might not be right.
  4. Prepare one Excel visual that analyzes the Sales History data.
  5. Prepare a 2nd Excel visual that analyzes the Sales History data with a 3-variable Bubble Chart (see Excel>Insert>Other Charts)

Power Point requirements

  1. Prepare a 1-page PowerPoint slide that communicates your new insight(s), i.e., how does the new insight affect the problem you are trying to solve. (Requires a visual).
  2. Add a 2nd Page to your PowerPoint slide that would communicate your recommendations. Remember the partner’s desire to retain the client.

Part 3 (use tabs in Excel file)

After analyzing the new data, you have an idea. Given the declining profitability of Delaney’s operations, perhaps the company would benefit from a multi-product breakeven and target profit analysis. Delaney’s operating assets for the current fiscal year are $750,000. Given the “low margin” business model, company investors would like 12.0% after-tax return on their investment. Assume a tax rate of 25.0%.

Required:

Excel requirements

  1. At breakeven, compute the level of sales required for each industry segment.
  2. Compute the before-tax target profit expected by the investors
  3. Compute the level of sales required for each industry segment to reach the expected target profit.
  4. For the current fiscal year, prepare a Pareto Chart of product-level sales.

Power Point requirements

  1. Prepare a 1-page PowerPoint slide that communicates your finding(s), i.e., identify a problem you want to solve. (Requires a visual).
  2. Add a 2nd Page to your PowerPoint slide that would communicate your recommendations. Remember the partner’s desire to retain the client.

Final Deliverable (Submit through Canvas):

  1. Submit ONE Power Point file that includes multiple slides. You MUST use the Power Point format provided on Canvas.
  2. Submit ONE Excel workbook file that includes multiple worksheets. You MUST use the spreadsheet provided on Canvas.
 

Leave a Reply