Shamma Zaid formed Al Ain Products LLC. in 2014 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 July1. 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 AAPs expected growth. Thus, on your first day, Ms. Zaid meets with you to emphasize why the company must thoroughly assess the impact of AAPs planned growth on cash flow. She would like to present the financial plan to AAPs 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 Mohammad Ed Adly, Al Ain Products head of marketing. Mohammeds team 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).

Exhibit 1. AAPs sales forecast and other sales information.

In addition, you determine through discussions with Awah Devine 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 months 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 months 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 companys 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.

Exhibit 2. AAPs planned operating expenses per month.

Exhibit 3. AAPs (actual) balance sheet as of June 30.

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 Zaids 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.

Exhibit 4. Benchmarking data.

Requirements

Construct a financial planning model

Download from Blackboard and save the Excel template (do not copy from another as it may effect some formulas) and use it to complete the financial model relationships and perform the following estimations. Print your solution for each one of the five requirements to provide a hardcopy submission. Also submit your complete excel file.

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. You do not need to formally compute breakeven-points to complete this requirement.