FOREST COMMUNICATIONS COMPANY You?re hired! Kate Forest formed Forest Communications Company (FCC) in 2007 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 new management team members. The Company hires you as a financial analyst to assume direct responsibility for financial planning activities. Your first assignment is to prepare a financial plan for the forthcoming year. Since you are anxious to make a favorable impression on Ms. Forest, the President of FCC, you immediately begin to assemble relevant information. Ms. Forest is acutely aware of other growth-oriented companies within the high technology sector that have burned through cash and gone bankrupt. Having seen potentially viable businesses fail in the past particularly concerns Ms. Forest and, consequently, she wants to ensure that sufficient cash will be available to accommodate FCC?s expected growth. Thus, on your first day, the President meets with you to emphasize why the Company must thoroughly assess the impact of FCC?s planned growth on cash flow. She would like to present the financial plan to FCC?s board of directors and has requested that you construct a financial model that can be used to perform sensitivities and address a range of questions from top management and board members. She also requests that you provide the board with a supplemental cost-volume-profit graphical analysis. Other Company Information You recall from past coursework that the starting point for a financial plan is a reliable sales forecast. Thus you consult with Bogey Fields, the sales manager, and an outside market researcher. Bogey has studied sales and economic trends, as well as changes within the highly competitive handheld computing industry to establish the unit sales forecast, which is presented in the spreadsheet template. As shown, total monthly unit sales volume is expected to continue increasing over the quarters, but with sales mix shifting away from the basic model toward a new premium model with enhanced features. 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 20 days. Thus far, bad debts have been negligible. Since FCC?s policy is to never stock out of its pen-based input devices (PIDs), and potentially forfeit market share to competitors, the Company maintains buffer inventory stock. Heretofore, FCC has sold only the basic model PID-B, but FCC recently began carrying a premium model PID-P. FCC expects that the current sales mix will shift toward the premium model, along with experiencing ongoing competitive pricing pressure (as reflected in the Assumptions Sheet). The company?s quarterly operating expenses (organized by cost behavior) are also provided in the Assumptions Sheet of the excel template. All operating expenses are paid during the quarter, in cash, with the exception of depreciation and insurance expenses. New fixed assets, including personal computers and office furniture, will be purchased during the forthcoming year out of cash. The Company, which is privately owned with Ms. Forest as the majority shareholder, has declared dividends, payable in the first month of the following quarter. FCC?s actual balance sheet at December 31 is provided on the Income Statement and Balance Sheet tab. Although FCC currently has no debt financing on its balance sheet, the Company has recently established a revolving line-of-credit through which it can borrow from The Bank of Titusville. For simplicity, assume that interest expense is recognized during the quarter incurred, while cash payments for interest occur one quarter in arrears. The same assumption should be applied for income tax expense. Required borrowings are made at the beginning of a quarter, and repayments at the end of a quarter in any dollar amount. FCC 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 quarter as a bank minimum requirement for the LOC and to meet regular operating expenses. REQUIRED: Part 1: Financial Planning Model (40 Points) Download the Excel template from the course page ? DO NOT MODIFY THE FORMAT OF THE TEMPLATE. Save the file onto a disk? do not attempt to complete the template directly from the course page and do not copy and paste sheets into a new workbook. Project assumptions have already been provided in the template?s assumptions sheet. Be sure to understand Chapter 3 (CVP Models) and Chapter 13 (Planning and Budgeting) in your textbook before proceeding with the project. Regularly save your work as you complete the model. Remember that you must use formulas so that any changes in input data automatically update your model. Otherwise, your model will not work correctly and you will lose points. Part I requires you to complete the sheets of the Excel template, as indicated below. Complete Sheet 2 (Schedules) 1) Sales and merchandise purchase plans with supporting schedules. (8 points) a) A sales plan by quarter and in total. Include a schedule of projected cash collections from sales and accounts receivable, by quarter and in total. b) A purchases plan in units and in dollars. Include a schedule of projected cash payments for purchases, by quarter and in total. Note: The cost of inventory on hand is released to cost of goods sold before costs for the purchase of additional units (i.e., a FIFO cost flow is assumed). Show all work and related computations to receive credit. No points will be awarded for handwritten or unclear responses that do not answer the questions. Use formulas in all spreadsheet cells and reference given data cells as necessary. POINTS WILL BE DEDUCTED FOR ENTERING HARDCODED VALUES.

Forest Communications Company Key Assumptions Unit Volume Sensitivity UNIT SALES Month 3rd Quarter TOTAL 16,310 PID-B 16,310 PID-P TOTAL 16,310 Actual 4th Quarter 17,790 16,011 1,779 17,790 Sales Mix % 100% 0% PID-B PID-P 90% 10% Unit selling price PID-B PID-P Unit cost PID-B PID-P 1st Quarter 18,600 16,182 2,418 18,600 Sales Mix Sensitivity 3% 87% 13% 4th Quarter 25,500 18,615 6,885 25,500 1st Quarter 27,900 19,251 8,649 27,900 0.0% 4% -----------> 4% 83% 17% $215 $215 $380 Sales Price Sensitivity -1.00% -2.00% $213 $209 $376 $369 $160 $150 $220 Unit Cost Sensitivity $150 $220 Wtd Average Sales Price Wtd Average Unit Cost Gross Profit % 0.0% -----------> Forecast 2nd Quarter 3rd Quarter 21,600 23,400 17,928 18,252 3,672 5,148 21,600 23,400 5% 78% 22% 5% 73% 27% 69% 31% -3.00% $198 $350 0.0% -3.00% $192 $340 -----------> -----------> -2.00% $204 $361 $150 $220 $150 $220 $150 $220 $$150 $220 $231.50 $157.00 $234.09 $159.10 $235.81 $161.90 $238.94 $165.40 $239.38 $168.90 $238.10 $171.70 PID-B PID-P Wtd Average 30.2% 42.1% 32.2% 29.5% 41.5% 32.0% 28.1% 40.3% 31.3% 26.6% 39.1% 30.8% 24.4% 37.2% 29.4% 22.0% 35.3% 27.9% Operating expenses Sales commissions Royalties Shipping & handling Wages and salaries Rent Utilities Insurance expired Depreciation Other S&A Advertising Campaign Fixed Asset Purchases Dividends Declared LOC Interest rate (% of sales price) (% of sales price) (per unit) (quarterly) (quarterly) (quarterly) (quarterly) (quarterly) (quarterly) (quarterly) (quarterly) (quarterly) 1st Quarter 2nd Quarter 3rd Quarter 5.0% 5.0% 5.0% 4.0% 4.0% 4.0% $7.00 $7.00 $8.00 $388,000 $400,000 $420,000 $42,000 $42,000 $46,000 $8,700 $9,000 $9,000 $7,500 $7,500 $7,500 $38,000 $40,000 $48,000 $110,000 $120,000 $120,000 $$$$$105,000 $20,000 $20,000 $20,000 $20,000 7% 7% 7% 4th Quarter 5.0% 6.0% $8.00 $425,000 $46,000 $9,200 $7,500 $52,000 $130,000 $$40,000 $20,000 7% 1st Quarter 5.0% 6.0% $8.00 $440,000 $46,000 $9,200 $7,500 $54,000 $145,000 $$$20,000 7% Other Assumptions Effective income tax rate Ending cash balance per quarter Ending inventory as percentage of next quarter's sales Percentage of purchases paid in: Current quarter Following quarter Percentage of sales collected in: Current quarter Following quarter Second following quarter (annual) 30% $40,000 SENSITIVITY SUMMARY RESULTS NOI = Net Income = Line of Credit = - 60% 50% 50% 42% 35% 23% Part 2: (B3a) Inventory Turnover = Part 2: (B3b) Days Sales in acct. rec. = 0 ENTER FORMULA II.A.2C 0 Page 2 0 0 Forest Communications Company Detailed Operating Schedules Schedule a: Sales plan Weighted average sales price 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Year End Total Units Credit sales, 100% $0 NOTE: Actual A/R should be collected in 1st and 2nd Quarters. Schedule b: Cash collections 1st Quarter From current quarter's sales From sales 1 quarter before 1,438,322 From sales 2 quarters before Total collections $1,438,322 Schedule c: Purchases plan in units Desired units in ending inventory Plus units sold Total needed Less beginning inventory Required purchases 1st Quarter Schedule d: Purchases plan (dollars) Weighted average unit cost 1st Quarter Desired ending inventory Plus cost of goods sold Total requirements Less beginning inventory Total purchases Note: Actual A/P should be paid in 1st Quarter only. Schedule e: Cash pmts for purchases From accounts payable (12/31) 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Total payments 2nd Quarter 3rd Quarter 945,183 $945,183 2nd Quarter 4th Quarter $0 3rd Quarter Year End $0 4th Quarter $0 1,438,322 945,183 $2,383,504 Year End - - - - 0 0 0 0 0 0 2nd Quarter 3rd Quarter 4th Quarter Year End - - - - $0 $0 $0 $$0 $0 $0 1st Quarter $0 2nd Quarter $0 3rd Quarter $0 4th Quarter $0 Year End $0 $0 Forest Communications Company Projected Income Statements and Balance Sheet Projected Income Statements Sales Less variable expenses Cost of goods sold Sales commissions Royalties Shipping & handling Total variable expenses Contribution margin Less fixed expenses Wages and salaries Rent Utilities Insurance expense Depreciation expense Other Selling & Admin. Advertising Campaign Total fixed expenses Schedule (a) 1st Quarter 3rd Quarter 4th Quarter Total $- (d) - $- Inventory Prepaid insurance Fixed assets, net of depreciation Total assets Liabilities and Stockholders' Equity Accounts payable, purchases Interest payable Income tax payable Line-of-Credit Dividends payable Capital stock, no par Retained earnings Total liabilities and stockholders' equity - - $- $- $- $- - - - - - - - - $- Projected Balance Sheet 3rd Qtr $806,530 11,160 units - - Net Operating Income Less: Interest Expense Net Income before Taxes Less: Income Tax Expense Net Income Assets Cash & Equivalents Accounts receivable 2nd Quarter Actual (prior year) 4th Qtr $2,383,504 $- $- $- $Projected (Ending 12/31/11) $97,500 3,190,034 1,752,120 45,000 1,952,000 $7,036,654 $1,478,280 15,000 4,275,000 1,268,374 $7,036,654 $- $EQUAL Forest Communications Company Projected Statements of Cash Flow Projected Statements of Cash Flow Internal Budget Format Schedule Beginning cash balance Cash collections (b) Total cash available Cash disbursements Inventory purchases (e) Variable operating expenses Fixed operating expenses Capital Acquisitions Interest Tax Payments Dividends Total disbursements Minimum cash balance Total cash needed Cash excess (deficit) Financing Borrowing Repayments Total cash from financing Ending cash balance Line-of-Credit Balance 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter $- - - - - - - - - - - - - - - - - $$- $- $- $- $- $- $- $- $- $- Cash Flows from Investing Activities Capital Acquisitions Net Cash used by Investing Activities - Cash Flows from Financing Activities Line of credit Payment of cash dividends Net Cash Provided by Financing Activities Difference - - Projected Statements of Cash Flow External Reporting Format (SFAS 95 - Indirect Method) 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Cash Flows from Operating Activities Net Income Adjustments to reconcile net income to net cash provided by operating activities Depreciation and noncash deductions Decrease/(Increase) in accounts receivable Decrease/(Increase) in inventory Increase/(Decrease) in accounts payable Increase/(Decrease) in interest payable Increase/(Decrease) in income tax payable Net Cash Provided by Operating Activities - Net Change in Cash Cash at Beginning of Month Cash at End of Month Total - - $- - - - - - - Total - $EQUAL $EQUAL $EQUAL $EQUAL $EQUAL Graph Data Weighted average computations: Sales price per unit Variable costs per unit #DIV/0! Weighted average unit cost Sales commissions Royalties Shipping and handling Total variable costs per unit #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Fixed costs in total - CVP Graph Data: Sales Levels Revenue 0 #DIV/0! 20,000 #DIV/0! 40,000 #DIV/0! 60,000 #DIV/0! 80,000 #DIV/0! 100,000 #DIV/0! 120,000 #DIV/0! Fixed Costs Total Costs $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0