You will complete a financial planning model that is derived from a set of assumptions and link together statements of projected cash, income and balance sheets that articulate. An Excel template, which is available for downloading, is structured with a separate worksheet (or tab) for each of the following sections: Sheet (1) Assumptions; Sheet (2) Supporting Schedules; Sheet (3) Projected Income Statement and Balance Sheet; and Sheet (4) Projected Cash Flow. 2 Sheet (2) provides supporting schedules for detailed computations, which in turn links to the projected income statements and balance sheet contained within Sheet (3), along with projected cash flow in Sheet (4). To work correctly, all cells of your model should be linked together via formulas, or cell references, across all worksheets. (A 5th sheet labeled, Graph contains data to produce a cost- volume-profit graph from current period, baseline information.

You should use the ChartWizard in Excel to create the graph. The Excel template already contains the key assumptions as well as some formulas. Within a real-life practice situation, you would need to independently develop and obtain support for the necessary assumptions that have been provided to you. In Part I of the Excel case, you should complete the model formulas and linkages to Sheets (2), (3) and (4 To accomplish this, the financial model must articulate (i.e., interconnect, or link) across all schedules and statements. Thus, a change in assumption should flow through and automatically update all related schedules in Sheet (2) and projected statements in Sheets (3) and (4). This requires entering formulas within the cells to calculate the required values, rather than hardcoding values into the cells.

OUT-WEST PRODUCTS, INC. CASE Youre Hired! Cynthia Valley formed Out-West Products, Inc. (OWP) in 2020 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 management trainee to assume direct responsibility for financial planning activities.

Your first assignment is to prepare a financial plan for the next three months, starting July 1. CASH COOLECTION PATTERN is as follows: 30% of a months sales are collected by month-end. An additional 40% 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. Since OWPs policy is to never stock out of its pen-based input devices (PIDs), and potentially forfeit market share to competitors, the Company maintains fairly high inventory levels. 3 Therefore, desired ending inventories are equal to 70% of the next months sales in units. Prior to June, OWP sold only the basic model PID-B at a price of $230 per unit. The PID-B costs OutWest Products $155 each from the manufacturer and it pays for purchases as follows: CASH Disbursements PATTERN is as follows: 60% in the month of purchase and the remaining 40% the following month. The companys monthly operating expenses (organized by cost behavior) are also provided in the Assumptions Sheet of the excel template.

All operating expenses are paid during the month, in cash, with the exception of depreciation and insurance expenses. New fixed assets, including personal computers and office furniture, will be purchased during September for $90,000 cash. Ignore depreciation for these newly acquired since they are insignificant. The Company declares dividends of $20,000 each quarter, payable in the first month of the following quarter. OWPs actual balance sheet at June 30, 2020 is provided at the end of the templates Assumptions Sheet. However, the Company also desires a minimum ending cash balance each month of $35,000 to meet regular operating expenses. Assume borrowings occur at the beginning of the period. To further simplify, assume no tax consequences. REQUIRED: Financial Planning Model (100 Points) Download the Excel template from your Blackboard course page DO NOT MODIFY THE FORMAT OF THE TEMPLATE. Save the file and do not attempt to complete the template directly from the course page and do not copy and paste sheets into a new workbook. Case assumptions have already been provided in the templates assumptions sheet. If necessary, it may be helpful to review Chapter 3 (CVP Analysis) and Chapter 6 (Budgeting) in your textbook prior to beginning the project. Be sure to 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 adequately and you will lose points. First, complete the sheets of the Excel template, as indicated below. 4 Complete Sheet 2 (Schedules) 1) Sales and merchandise purchase plans with supporting schedules. (40 points) a) A sales plan by month and in total - including a schedule of projected cash collections from sales and accounts receivable, by month and in total. b) A purchases plan in units and in dollars, including a schedule of projected cash payments for purchases, by month 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., use a FIFO cost flow assumption for all months).] Complete Sheet 3 (Inc. St. and Bal. Sheet) 2) Projected contribution format income statements by month and in total. (20 points) 3) Projected balance sheet for the quarter as of September30. (20 points) Complete Sheet 4 (Statements of Cash Flow) 4) Projected statements of cash by month and in total internal budget format. (15 points) Graph (5 points) Use the data in the Graph worksheet to create a cost-volume-profit graph. Highlight the cells A13:H16. Click insert and Select a line chart type for the CVP graph (your choice of style). Be sure that your graph closely resembles a standard CVP graph or points will not be awarded.

| Home Cut Copy Format Painter D25 I A B C 1 Out-Vest Products, Inc. 2 Key Assumptions 3 4 5 Unit Sales 6 7 UNIT SALES 8 Month 9 TOTAL 7,650 10 PID-B 7,650 11 PID-P 12 TOTAL 7,650 13 14 Sales Mix Sensitivity --> 15 Sales Mix X PID-B 16 PID-P 17 18 Sales Price Sensitivity --> 19 Unit selling price PID-B $ 20 PID-P $ 21 22 Unit cost PID-B 23 PID-P $ 24 25 Veighted Average Sales Price $ 26 Veighted Average Unit Cost $ 27 28 Gross Profit 32.6% PID-B PID-P 29 40.8% 30 WTD AVG 34.5% 31 32 Other Assumptions 33 Ending cash balance (per month) $35.000 34 Ending inventory as percentage 35 of next month's sales 70% 36 Percentage of purchases paid in: 37 60% Current month Following month 38 40% 39 Percentage of sales collected in: 40 30% Current month Following month 41 40% 42 Second following month 30% 43 New fixed asset purch September $90,000 44 Quarterly dividends $20,000 45 Line-of-credit annual interest 8% 40 41 48 Balance Sheet: Actual as of June 30 43 Assets 50 Cash & Equivalents 51 Accounts Receivable May June 52 je 1.626.100 53 Inventory 7.140 unis 54 Prepaid insurance 55 Fixed assets, net of depreciation 56 Total Assets 57 58 Liabilities and Stockholder's Equity 59 Accounts payable, purchases 60 Line-of-credit 61 Dividends payable 62 Capital stock, no par 63 Retained earnings 64 Total liabilities and stockholder's equity 65 66 67 Assumptions File Paste Clipboard April Insert F X ACTUAL May 8.400 8,400 8,400 New Tab Arial B IU fx ####### D June 9,200 7,820 1,380 9,200 85% 15% 230 380 155 225 252.50 165.50 Page Layout 10 H~ A A ~A~ Font =(D19*D15+D20*D16) E F G Unit Volume Sensitivity --> July Schedules 10,200 8,466 1,734 10,200 $ $ Formulas Data = = == 2% 83% 17% -2.00% H 225 $ 221 $ 216 $ 372 $ 365 $ 358 $ 155 $ 155 $ 155 $ $ 225 $ 225 $ 225 $ $ 250.39 $249.70 $250.36 $ $ 166.90 $ 169.00 $ 171.80 $ 31.2% 29.8% 28.4% 39.6% 38.3% 37.1% 33.3% 32.3% 31.4% Operating expenses Variable: Sales commissions of sale (per unit) Shipping & handling. Fixed: Wages and salaries Rent Utilities Insurance expired Depreciation Other S&A Advertising Campaign $40,000 2,205,700 1,181,670 20,000 1,480,000 $4,927,370 $970,464 20,000 1,750,000 2,186,906 $4,927,370 I 0.0% FORECAST August Septembe October 11,400 12,700 13,200 9,120 9,652 9,900 2,280 3,048 3,300 11,400 12,700 13,200 1% 80% 76% 75% 20% 24% 25% -2.00% -2.00% -2.00% 212 350 155 225 246.73 172.50 Review View Developer Help Power Pivot ab Wrap Text Accounting .00 Merge & Center $ % 9.00 -8 Number 5 L M 0 == Alignment J 26.9% 35.8% 30.1% 5.0% $20.00 (per month) ####### 30,000 10,000 4.500 16,000 40,000 OTHER INFO Scenario IIA_2C Oper Inc LOC= Sensitivity ILC_2A Inv T/O 0 Sensitivity II_C_2B Days Sale #DIV/0! Qual of In #REF! Op Marqi #DIV/0! Asset TIC Inc St & Bal Sht Cash Flow Graph K + N P Q Conditional Formatting S R 1 Out-West Products, Inc. 2 Detailed Operating Schedules 3 4 5 Schedule a: Sales plan 6 Weighted average sales price 7 8 Total Units 9 Credit sales, 100% 10 11 12 NOTE: Actual A/R should be collected in July and Aug. 13 Schedule b: Cash collections 14 From current month's sales 15 From sales 1 month before 16 From sales 2 months before 17 Total collections 18 19 20 21 Schedule o: Purchases plan in units 22 Desired units in ending inventory 23 Plus units sold 24 Total needed 25 Less beginning inventory 26 Required purchases 27 28 29 30 Schedule d: Purchases plan (dollars) 31 Weighted average unit cost 32 33 Desired ending inventory 34 Plus cost of goods sold 35 Total requirements 36 Less beginning inventory 37 Total purchases 38 39 40 Note: Actual A/P should be paid in July only 41 Schedule e: Cash pmts for purchases 42 From accounts payable (6/30) 43 0.00 44 0.00 45 0.00 46 Total payments 47 48 49 50 Assumptions Schedules July August Inc St & Bal Sht September Cash Flow Graph Total 1 Out-West Products, Inc. 2 Projected Income Statements and Balance Sheet 3 4 5 Projected Income Statements 6 Schedule 7 Sales (a) 8 Less variable expenses 9 Cost of goods sold (d) 10 11 Sales commissions Shipping & handling 12 Total variable expenses 13 Contribution margin 14 Less fixed expenses 15 Wages and salaries 16 Rent 17 Utilities 18 Insurance expense Depreciation expense 19 20 Other Selling & Admin. Advertising Campaign 21 22 Total fixed expenses 23 24 Operating Income 25 Less: Interest Expense 26 Net Income before Taxes 27 28 29 Projected Balance Sheet 30 31 Cash & Equivalents 32 Accounts receivable 33 Inventory 34 Prepaid insurance 35 Fixed assets, net of depreciation 36 Total assets 37 38 Accounts payable, purchases 39 Interest payable 40 Line-of-Credit 41 Dividends payable 42 Capital stock, no par 43 Retained earnings 44 Total liabilities and stockholders' equity 45 46 47 48 49 50 July August September 3,518 4,629 6,004 ($3,518) ($4,629) ($6,004) Assets Liabilities and Stockholders' Equity Assumptions Schedules Inc St & Bal Sht Cash Flow Total 14,151 ($14,151) September Graph 6,004 900,602 EQUAL 1 Out-West Products, Inc. 2 Projected Statements of Cash Flow 3 4 5 Projected Statements of Cash Flow 6 Internal Budget Format 7 8 Schedule 9 Beginning cash balance 10 Cash collections (b) 11 Total cash available 12 Cash disbursements 13 Inventory purchases (e) 14 Variable operating expenses 15 Fixed operating expenses Capital Acquisitions 16 17 Interest 18 Dividends 19 Total disbursements 20 Minimum cash balance 21 Total cash needed 22 Cash excess (deficit) 23 Financing 24 Borrowing 25 Repayments 26 Total cash from financing 27 Ending cash balance 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 15 Line-of-Credit Balance | Assumptions Schedules July Inc St & Bal Sht August September 3,518 4,629 Cash Flow Graph Total 1 Graph Data 2 Weighted average computations: 3 Sales price per unit S 4 Variable costs per unit 5 Weighted average unit cost S 6 Sales commissions 7 Shipping and handling Total variable costs per unit S 8 9 10 Fixed costs in total 11 12 CVP Graph Data: 13 Sales Levels 14 Revenue 15 Fixed Costs 16 Total Costs 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 3 34 35 36 37 38 39042345 46 47 250.36 171.80 12.52 20.00 204.32 0 SO SO SO Assumptions Schedules Inc St & Bal Sht Cash Flow Graph 41 5,000 $1,251,785 10,000 15,000 20,000 $2,503,571 $3,755,356 $5,007,141 SO SO SO SO $1,021,589 $2,043,179 $3,064,768 $4,086,357 + 25,000 $6,258,927 SO $5,107,946 30,000 $7,510,712 SO $6,129,536 | Home Cut Copy Format Painter D25 I A B C 1 Out-Vest Products, Inc. 2 Key Assumptions 3 4 5 Unit Sales 6 7 UNIT SALES 8 Month 9 TOTAL 7,650 10 PID-B 7,650 11 PID-P 12 TOTAL 7,650 13 14 Sales Mix Sensitivity --> 15 Sales Mix X PID-B 16 PID-P 17 18 Sales Price Sensitivity --> 19 Unit selling price PID-B $ 20 PID-P $ 21 22 Unit cost PID-B 23 PID-P $ 24 25 Veighted Average Sales Price $ 26 Veighted Average Unit Cost $ 27 28 Gross Profit 32.6% PID-B PID-P 29 40.8% 30 WTD AVG 34.5% 31 32 Other Assumptions 33 Ending cash balance (per month) $35.000 34 Ending inventory as percentage 35 of next month's sales 70% 36 Percentage of purchases paid in: 37 60% Current month Following month 38 40% 39 Percentage of sales collected in: 40 30% Current month Following month 41 40% 42 Second following month 30% 43 New fixed asset purch September $90,000 44 Quarterly dividends $20,000 45 Line-of-credit annual interest 8% 40 41 48 Balance Sheet: Actual as of June 30 43 Assets 50 Cash & Equivalents 51 Accounts Receivable May June 52 je 1.626.100 53 Inventory 7.140 unis 54 Prepaid insurance 55 Fixed assets, net of depreciation 56 Total Assets 57 58 Liabilities and Stockholder's Equity 59 Accounts payable, purchases 60 Line-of-credit 61 Dividends payable 62 Capital stock, no par 63 Retained earnings 64 Total liabilities and stockholder's equity 65 66 67 Assumptions File Paste Clipboard April Insert F X ACTUAL May 8.400 8,400 8,400 New Tab Arial B IU fx ####### D June 9,200 7,820 1,380 9,200 85% 15% 230 380 155 225 252.50 165.50 Page Layout 10 H~ A A ~A~ Font =(D19*D15+D20*D16) E F G Unit Volume Sensitivity --> July Schedules 10,200 8,466 1,734 10,200 $ $ Formulas Data = = == 2% 83% 17% -2.00% H 225 $ 221 $ 216 $ 372 $ 365 $ 358 $ 155 $ 155 $ 155 $ $ 225 $ 225 $ 225 $ $ 250.39 $249.70 $250.36 $ $ 166.90 $ 169.00 $ 171.80 $ 31.2% 29.8% 28.4% 39.6% 38.3% 37.1% 33.3% 32.3% 31.4% Operating expenses Variable: Sales commissions of sale (per unit) Shipping & handling. Fixed: Wages and salaries Rent Utilities Insurance expired Depreciation Other S&A Advertising Campaign $40,000 2,205,700 1,181,670 20,000 1,480,000 $4,927,370 $970,464 20,000 1,750,000 2,186,906 $4,927,370 I 0.0% FORECAST August Septembe October 11,400 12,700 13,200 9,120 9,652 9,900 2,280 3,048 3,300 11,400 12,700 13,200 1% 80% 76% 75% 20% 24% 25% -2.00% -2.00% -2.00% 212 350 155 225 246.73 172.50 Review View Developer Help Power Pivot ab Wrap Text Accounting .00 Merge & Center $ % 9.00 -8 Number 5 L M 0 == Alignment J 26.9% 35.8% 30.1% 5.0% $20.00 (per month) ####### 30,000 10,000 4.500 16,000 40,000 OTHER INFO Scenario IIA_2C Oper Inc LOC= Sensitivity ILC_2A Inv T/O 0 Sensitivity II_C_2B Days Sale #DIV/0! Qual of In #REF! Op Marqi #DIV/0! Asset TIC Inc St & Bal Sht Cash Flow Graph K + N P Q Conditional Formatting S R 1 Out-West Products, Inc. 2 Detailed Operating Schedules 3 4 5 Schedule a: Sales plan 6 Weighted average sales price 7 8 Total Units 9 Credit sales, 100% 10 11 12 NOTE: Actual A/R should be collected in July and Aug. 13 Schedule b: Cash collections 14 From current month's sales 15 From sales 1 month before 16 From sales 2 months before 17 Total collections 18 19 20 21 Schedule o: Purchases plan in units 22 Desired units in ending inventory 23 Plus units sold 24 Total needed 25 Less beginning inventory 26 Required purchases 27 28 29 30 Schedule d: Purchases plan (dollars) 31 Weighted average unit cost 32 33 Desired ending inventory 34 Plus cost of goods sold 35 Total requirements 36 Less beginning inventory 37 Total purchases 38 39 40 Note: Actual A/P should be paid in July only 41 Schedule e: Cash pmts for purchases 42 From accounts payable (6/30) 43 0.00 44 0.00 45 0.00 46 Total payments 47 48 49 50 Assumptions Schedules July August Inc St & Bal Sht September Cash Flow Graph Total 1 Out-West Products, Inc. 2 Projected Income Statements and Balance Sheet 3 4 5 Projected Income Statements 6 Schedule 7 Sales (a) 8 Less variable expenses 9 Cost of goods sold (d) 10 11 Sales commissions Shipping & handling 12 Total variable expenses 13 Contribution margin 14 Less fixed expenses 15 Wages and salaries 16 Rent 17 Utilities 18 Insurance expense Depreciation expense 19 20 Other Selling & Admin. Advertising Campaign 21 22 Total fixed expenses 23 24 Operating Income 25 Less: Interest Expense 26 Net Income before Taxes 27 28 29 Projected Balance Sheet 30 31 Cash & Equivalents 32 Accounts receivable 33 Inventory 34 Prepaid insurance 35 Fixed assets, net of depreciation 36 Total assets 37 38 Accounts payable, purchases 39 Interest payable 40 Line-of-Credit 41 Dividends payable 42 Capital stock, no par 43 Retained earnings 44 Total liabilities and stockholders' equity 45 46 47 48 49 50 July August September 3,518 4,629 6,004 ($3,518) ($4,629) ($6,004) Assets Liabilities and Stockholders' Equity Assumptions Schedules Inc St & Bal Sht Cash Flow Total 14,151 ($14,151) September Graph 6,004 900,602 EQUAL 1 Out-West Products, Inc. 2 Projected Statements of Cash Flow 3 4 5 Projected Statements of Cash Flow 6 Internal Budget Format 7 8 Schedule 9 Beginning cash balance 10 Cash collections (b) 11 Total cash available 12 Cash disbursements 13 Inventory purchases (e) 14 Variable operating expenses 15 Fixed operating expenses Capital Acquisitions 16 17 Interest 18 Dividends 19 Total disbursements 20 Minimum cash balance 21 Total cash needed 22 Cash excess (deficit) 23 Financing 24 Borrowing 25 Repayments 26 Total cash from financing 27 Ending cash balance 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 15 Line-of-Credit Balance | Assumptions Schedules July Inc St & Bal Sht August September 3,518 4,629 Cash Flow Graph Total 1 Graph Data 2 Weighted average computations: 3 Sales price per unit S 4 Variable costs per unit 5 Weighted average unit cost S 6 Sales commissions 7 Shipping and handling Total variable costs per unit S 8 9 10 Fixed costs in total 11 12 CVP Graph Data: 13 Sales Levels 14 Revenue 15 Fixed Costs 16 Total Costs 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 3 34 35 36 37 38 39042345 46 47 250.36 171.80 12.52 20.00 204.32 0 SO SO SO Assumptions Schedules Inc St & Bal Sht Cash Flow Graph 41 5,000 $1,251,785 10,000 15,000 20,000 $2,503,571 $3,755,356 $5,007,141 SO SO SO SO $1,021,589 $2,043,179 $3,064,768 $4,086,357 + 25,000 $6,258,927 SO $5,107,946 30,000 $7,510,712 SO $6,129,536