Al Ain Products Inc.

Shamma Zaid formed Al Ain Products, Inc. in 2010 when she obtained an exclusive franchise to nationally distribute a pen-based input device that provides effortless communication with standard personal computers. Recent high sales growth of the base model pen-based input device (PID-B), along with expected sales growth for a new premium model (PID-P), requires adding management team members. The company has hired you as a financial analyst to assist with the financial planning function. Your first assignment is to help complete a financial plan for the next 3 months, starting July one. Since you are anxious to make a favorable impression on Ms. Zaid, the President of AAP, you immediately begin to assemble pertinent information.

Ms. Zaid is keenly aware of other growth-oriented companies within the high technology sector that have run out of cash and gone into bankruptcy. Seeing potentially viable businesses fail particularly concerns Ms. Zaid and, consequently, she wants to ensure that sufficient cash will be available to accommodate AAP's expected growth. Thus, on your first day, Ms. Zaid meets with you to emphasize why the company must thoroughly assess the impact of AAP's planned growth on cash flow. She would like to present the financial plan to AAP's board of directors and requests a financial model that can be used to address questions from top management and members of the board of directors. She would also like to provide the board with a graphical cost-volume-profit analysis.

Company information

From your prior education and work experience, you understand that the starting point for a financial plan is a reliable sales forecast. Thus, you confer with Abdulla Hady, the sales manager, along with an outside marketing consultant. Abdulla has studied industry sales and economic trends, along with other important market factors within the highly competitive handheld computing industry to establish a unit sales forecast. As shown in Exhibit 1, total monthly unit sales volume is expected to continue increasing over the third quarter, but with sales mix shifting toward the new premium model (PID-P).

In addition, you determine through discussions with the accounts receivable manager that all sales to retailers are on account, with no discount, and payable within 15 days. However, the manager indicates that only 20% of a month's sales are typically collected by month-end. An additional 50% is collected in the month following, and the remaining 30% is collected in the second month following sale. Thus far, bad debts have been negligible due to diligent collection efforts.

Since Al Ain Products does not want to forfeit market share to competitors, it maintains a policy to never stock out of its pen-based input devices (PIDs). Therefore, the company maintains desired ending inventories equal to 75% of the next month's unit sales. Prior to June, AAP sold only the basic model (PID-B) at a price of $230 per unit. AAP purchases the PID-B model from an overseas manufacturer for $155 per unit. The company pays for inventory purchases as follows: 50% in the month of purchase and the remaining 50% the following month. In June, AAP began carrying a premium model PID-P, priced at $380, which costs $225 from the manufacturer. The current sales mix is 80% PID-B and 20% PID-P. However, the company expects this mix to shift toward the premium model, along with ongoing competitive pricing pressure (see Exhibit 1).

The company's monthly operating expenses (organized by cost behavior) are provided in Exhibit 2. For simplicity, the company assumes that all operating expenses are paid in cash during the month incurred, with the exception of depreciation and insurance expense. New fixed assets, including personal computers and office furniture, are expected to be purchased during September for $100,000 cash. The company, which is privately owned with Ms. Zaid as the majority shareholder, declares dividends of $20,000 at the end of each quarter, payable in the first month of the following quarter. Al Ain Products' actual balance sheet, as of June 30, is provided in Exhibit 3.

Although AAP currently has no debt, the company has recently established a revolving line-of-credit through which it can borrow from The Bank of Al Ain at 8% annual interest. For simplicity, assume that interest expense is recognized during the month incurred, while cash payments for interest occur 1 month in arrears.

Required borrowings are made at the beginning of a month, and repayments at the end of a month in any dollar amount. Al Ain Products wishes to use any excess cash to pay off loan principal as rapidly as possible. However, the company also desires a minimum ending cash balance each month of $40,000 to provide flexibility in meeting regular operating expenses. To simplify, assume no tax consequences.

2.3. Industry benchmark data
One of Shamma Zaid's priorities as President is to ensure that AAP remains highly competitive within the industry. She has contacted a benchmarking consultant to provide data regarding the performance level of other companies and the key competitor within the industry. Selected benchmarking data for assessing profitability and working capital management, relative to competing firms, are provided in Exhibit 4. The consultant recommends that AAP use this benchmarking data to assess its competitive position and to achieve ongoing operational improvements.

Requirements

Part 1: construct a financial planning model

Download and save the Excel template from blackboard and use it to complete the financial model relationships and perform the following estimations.

1.

(a) Prepare a sales plan by month and in total. Include a schedule of projected cash collections from sales and accounts receivable, by month and in total.

(b) Prepare a purchases plan in units and in dollars. Include a schedule of projected cash payments for purchases and accounts payable, by month and in total. Note: the cost of inventory on hand is assumed to be released to cost of goods sold before costs for the purchase of additional units, i.e., a FIFO cost flow is assumed for all months.

2. Prepare a projected contribution format income statement, by month and in total.

3. Prepare a projected balance sheet as of September 30. Hint: for July and August, compute the line-of-credit interest expense and then link (with a 1 month lag) to interest payments on the projected statements of cash flow (or vice versa). For September, compute the interest expense and link forward to interest payable on the projected balance sheet.

4. Prepare projected statements of cash flow, by month and in total. The first statement of projected cash flow is presented in an internal budget format. The second statement of projected cash flow is presented in an external financial reporting format. Note: projected cash flow should be consistent across formats.

5. Use the data in cells A14:H16 within the Graph worksheet to create a cost-volume-profit graph. Ensure that your graph closely resembles a standard CVP graph to receive full credit.

6. Use the income statement and calculate the breakeven in sales dollars for the quarter. Then calculate the margin of safety ratio for the quarter.

7. Refer to the baseline projected income statement and statements of cash.

(a) Compute and interpret AAPs "quality of income", where quality of income = operating cash flow ÷ net income. Operating cash flow = cash collections - cash disbursements before capital acquisitions.

(b) Explain why income is greater than operating cash flow and identify at least two specific areas in which Al Ain Products may not be effectively using cash (focus on working capital components). [Hint: examine operating cash flow items from the external format cash flow statement].

8. Refer to the baseline and review the selected benchmarking information provided by Al Ain Products' benchmarking consultant, as shown in Exhibit 4.

(a) Compute (annualize as necessary) AAP's financial metrics for the projected quarter.

(b) Discuss how well AAP's expected performance measures compare to its key competitor and the industry averages? Explain your results.

Attachment:- Case - Al Ain Products Inc.rar