I need help completing a Financial Planning and Modeling case for ACC Inf Sys by noon today. I have already completed the majority of the work and the attempt was graded. The prof has given me til NOON today to make changes and resubmit. Please help....
Out-West Products, Inc. Key Assumptions Unit Sales UNIT SALES Month TOTAL PID-B PID-P TOTAL Unit Volume Sensitivity --> April 7,650 7,650 7,650 ACTUAL May 8,400 8,400 8,400 June 9,200 7,360 1,840 9,200 July 10,294 8,030 2,265 10,294 Sales Mix Sensitivity --> Sales Mix % PID-B PID-P 80% 20% 0.9% FORECAST August September 11,505 12,817 8,629 9,100 2,876 3,717 11,505 12,817 October 13,322 9,325 3,997 13,322 2% 78% 22% 3% 75% 25% 4% 71% 29% 1% 70% 30% 0.00% $230 $380 0.00% $230 $380 0.00% $230 $380 Unit selling price PID-B PID-P $230 $380 0.00% $230 $380 Unit cost PID-B PID-P $155 $225 $155 $225 $155 $225 $155 $225 $155 $225 $260.00 $169.00 $263.00 $170.40 $267.50 $172.50 $273.50 $175.30 $275.00 $176.00 Sales Price Sensitivity --> Weighted Average Sales Price Weighted Average Unit Cost Gross Profit % PID-B PID-P WTD AVG Other Assumptions Ending cash balance (per month) Ending inventory as percentage of next month's sales Percentage of purchases paid in: Current month Following month Percentage of sales collected in: Current month Following month Second following month New fixed asset purchases September Quarterly dividends Line-of-credit annual interest Balance Sheet: Actual as of June 30 Assets Cash & Equivalents Accounts Receivable May $579,600 32.6% 40.8% 35.0% $40,000 75% 50% 50% 20% 50% 30% $100,000 $20,000 8% 32.6% 40.8% 35.2% 32.6% 40.8% 35.5% 32.6% 40.8% 35.9% Operating expenses Variable: Sales commissions (% of sales) Shipping & handling (per unit) Fixed: Wages and salaries Rent Utilities Insurance expired Depreciation Other S&A Advertising Campaign 32.6% 40.8% 36.0% 8.0% $15.00 (per month) $315,000 26,000 9,500 4,500 16,000 38,000 50,000 OTHER INFO June $1,913,600 $47,000 2,493,200 Inventory 7,650 units Prepaid insurance Fixed assets, net of depreciation Total Assets 1,292,850 20,000 1,480,000 $5,333,050 Liabilities and Stockholder's Equity Accounts payable, purchases Line-of-credit Dividends payable Capital stock, no par Retained earnings Total liabilities and stockholder's equity $889,075 20,000 1,750,000 2,673,975 $5,333,050 Scenario II_A_2C Oper Inc = 1,000,000 LOC = #REF! Sensitivity II_C_2A Inv T/O = 15.55 Sensitivity II_C_2B Days Sales = 36.11 Out-West Products, Inc. Detailed Operating Schedules Schedule a: Sales plan Weighted average sales price July $263.00 August $267.50 September $273.50 Total Units Credit sales, 100% 10,294 $2,707,410 11,505 $3,077,703 12,817 $3,505,574 34,617 $9,290,688 NOTE: Actual A/R should be collected in July and Aug. Schedule b: Cash collections From current month's sales From sales 1 month before From sales 2 months before Total collections July $541,482 1,196,000 579,600 $2,317,082 August $615,541 1,353,705 717,600 $2,686,846 September $701,115 1,538,852 812,223 $3,052,189 Total $1,858,138 4,088,557 2,109,423 $8,056,117 September 9,992 12,817 22,809 9,613 13,196 Total Total Schedule c: Purchases plan in units Desired units in ending inventory Plus units sold Total needed Less beginning inventory Required purchases July 8,629 10,294 18,923 7,721 11,203 August 9,613 11,505 21,119 8,629 12,489 Schedule d: Purchases plan (dollars) Weighted average unit cost July $169.00 August $170.40 September $172.50 $1,470,394 1,743,346 3,213,740 1,304,807 $1,908,933 $1,658,258 1,966,566 3,624,824 1,470,394 $2,154,430 $1,751,520 2,219,983 3,971,504 1,658,258.36 $2,313,246 Desired ending inventory Plus cost of goods sold Total requirements Less beginning inventory Total purchases Note: Actual A/P should be paid in July only Schedule e: Cash pmts for purchases From accounts payable (6/30) July August September Total payments July $889,075 954,466 $1,843,541 August 954,466 1,077,215 $2,031,682 September 1,077,215 1,156,623 $2,233,838 Total 9,992 34,617 44,609 7,721 36,888 $1,751,520 5,929,895 $7,681,416 1,304,807 $6,376,609 Total $889,075 1,908,933 2,154,430 1,156,623 $6,109,061 Out-West Products, Inc. Projected Income Statements and Balance Sheet Projected Income Statements Schedule Sales (a) Less variable expenses Cost of goods sold (d) Sales commissions 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 Operating Income Less: Interest Expense Net Income before Taxes July $2,707,410 August $3,077,703 September $3,505,574 Total $9,290,688 1,743,346 135,371 154,415 2,033,131 1,966,566 153,885 172,581 2,293,033 2,219,983 175,279 192,262 2,587,524 5,929,895 464,534 519,258 6,913,688 2,377,000 $315,000 26,000 9,500 4,500 16,000 38,000 50,000 459,000 $315,000 $26,000 $9,500 $4,500 $16,000 $38,000 $50,000 459,000 $315,000 $26,000 $9,500 $4,500 $16,000 $38,000 $50,000 459,000 945,000 78,000 28,500 13,500 48,000 114,000 150,000 1,377,000 215,279 1,462 $213,817 325,671 2,147 $323,524 459,050 3,154 $455,896 1,000,000 6,763 $993,237 Projected Balance Sheet September Assets Cash & Equivalents Accounts receivable Inventory Prepaid insurance Fixed assets, net of depreciation Total assets Liabilities and Stockholders' Equity Accounts payable, purchases Interest payable Line-of-Credit Dividends payable Capital stock, no par Retained earnings Total liabilities and stockholders' equity $40,000 $3,727,770 $1,758,515 6,500 1,580,000 $7,112,785 $1,156,623 3,154 473,076 $20,000 1,750,000 3,647,212 $7,050,064 NOT EQUAL Graph Data Weighted average computations: Sales price per unit Variable costs per unit $273.50 Weighted average unit cost Sales commissions Shipping and handling Total variable costs per unit $175.30 21.88 15.00 $212.18 Fixed costs in total 1,377,000 CVP Graph Data: Sales Levels Revenue Fixed Costs 0 $0 $1,377,000 5,000 $1,367,500 $1,377,000 10,000 $2,735,000 $1,377,000 15,000 $4,102,500 $1,377,000 20,000 $5,470,000 $1,377,000 25,000 $6,837,500 $1,377,000 30,000 $8,205,000 $1,377,000 Total Costs $1,377,000 $2,437,900 $3,498,800 $4,559,700 $5,620,600 $6,681,500 $7,742,400 CPV Analysis $ 9,000,000 $ 8,000,000 Axis T it le $ 7,000,000 $ 6,000,000 $ 5,000,000 $ 4,000,000 $ 3,000,000 $ 2,000,000 $ 1,000,000 $0 0 5,000 10,000 15,000 20,000 25,000 30,000 Out-West Products, Inc. Key Assumptions Unit Sales UNIT SALES Month TOTAL PID-B PID-P TOTAL Unit Volume Sensitivity --> April 7,650 7,650 7,650 ACTUAL May 8,400 8,400 8,400 June 9,200 7,360 1,840 9,200 July 10,200 7,956 2,244 10,200 Sales Mix Sensitivity --> Sales Mix % PID-B PID-P 80% 20% 0.0% FORECAST August September 11,400 12,700 8,550 9,017 2,850 3,683 11,400 12,700 October 13,200 9,240 3,960 13,200 2% 78% 22% 3% 75% 25% 4% 71% 29% 1% 70% 30% -2.00% $221 $365 -2.00% $216 $358 -2.00% $212 $350 Unit selling price PID-B PID-P $230 $380 -2.00% $225 $372 Unit cost PID-B PID-P $155 $225 $155 $225 $155 $225 $155 $225 $155 $225 $260.00 $169.00 $257.74 $170.40 $256.91 $172.50 $257.42 $175.30 $253.65 $176.00 Sales Price Sensitivity --> Weighted Average Sales Price Weighted Average Unit Cost Gross Profit % PID-B PID-P WTD AVG Other Assumptions Ending cash balance (per month) Ending inventory as percentage of next month's sales Percentage of purchases paid in: Current month Following month Percentage of sales collected in: Current month Following month Second following month New fixed asset purchases September Quarterly dividends Line-of-credit annual interest Balance Sheet: Actual as of June 30 Assets Cash & Equivalents Accounts Receivable May $579,600 32.6% 40.8% 35.0% $40,000 75% 50% 50% 20% 50% 30% $100,000 $20,000 8% 31.2% 39.6% 33.9% 29.8% 38.3% 32.9% 28.4% 37.1% 31.9% Operating expenses Variable: Sales commissions (% of sales) Shipping & handling (per unit) Fixed: Wages and salaries Rent Utilities Insurance expired Depreciation Other S&A Advertising Campaign 26.9% 35.8% 30.6% 5.0% $15.00 (per month) $315,000 26,000 9,500 4,500 16,000 38,000 - OTHER INFO June $1,913,600 $47,000 2,493,200 Inventory 7,650 units Prepaid insurance Fixed assets, net of depreciation Total Assets 1,292,850 20,000 1,480,000 $5,333,050 Liabilities and Stockholder's Equity Accounts payable, purchases Line-of-credit Dividends payable Capital stock, no par Retained earnings Total liabilities and stockholder's equity $889,075 20,000 1,750,000 2,673,975 $5,333,050 Scenario II_A_2C Oper Inc = LOC = Sensitivity II_C_2A Inv T/O = #REF! 0 Sensitivity II_C_2B Days Sales = #DIV/0! Out-West Products, Inc. Detailed Operating Schedules Schedule a: Sales plan Weighted average sales price July Total Units Credit sales, 100% NOTE: Actual A/R should be collected in July and Aug. Schedule b: Cash collections From current month's sales From sales 1 month before From sales 2 months before Total collections August $0 July July Schedule d: Purchases plan (dollars) Weighted average unit cost July September August September August $- $$0 July $0 - $0 Total September $- $0 0 0 Total $$0 September $0 - $0 1,196,000 1,297,200 $2,493,200 0 0 $$0 August $0 Total $0 $0 0 0 $- Total $0 $0 717,600 $717,600 0 0 Desired ending inventory Plus cost of goods sold Total requirements Less beginning inventory Total purchases Note: Actual A/P should be paid in July only Schedule e: Cash pmts for purchases From accounts payable (6/30) July August September Total payments $0 August $0 1,196,000 579,600 $1,775,600 Schedule c: Purchases plan in units Desired units in ending inventory Plus units sold Total needed Less beginning inventory Required purchases September $0 $0 $0 Total $0 $0 $0 $0 Out-West Products, Inc. Projected Income Statements and Balance Sheet Projected Income Statements Schedule Sales (a) Less variable expenses Cost of goods sold (d) Sales commissions 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 July August September Total $0 - - - - - - 1,462 ($1,462) Operating Income Less: Interest Expense Net Income before Taxes - - 2,147 ($2,147) 3,154 ($3,154) 6,763 ($6,763) Projected Balance Sheet September Assets Cash & Equivalents Accounts receivable Inventory Prepaid insurance Fixed assets, net of depreciation Total assets $0 Liabilities and Stockholders' Equity Accounts payable, purchases Interest payable Line-of-Credit Dividends payable Capital stock, no par Retained earnings Total liabilities and stockholders' equity 3,154 473,076 $476,230 NOT EQUAL Graph Data Weighted average computations: Sales price per unit Variable costs per unit $257.42 Weighted average unit cost Sales commissions Shipping and handling Total variable costs per unit $175.30 12.87 15.00 $203.17 Fixed costs in total - CVP Graph Data: Sales Levels Revenue Fixed Costs 0 $0 $0 5,000 $1,287,080 $0 10,000 $2,574,160 $0 15,000 $3,861,240 $0 20,000 $5,148,320 $0 25,000 $6,435,400 $0 30,000 $7,722,480 $0 Total Costs $0 $1,015,854 $2,031,708 $3,047,562 $4,063,416 $5,079,270 $6,095,124 ACCOUNTING INFORMATION SYSTEMS SPRING 2015 EXCEL ASSIGNMENT: FINANCIAL PLANNING AND MODELING CASE GENERAL INSTRUCTIONS: READ THIS ENTIRE ASSIGNMENT CAREFULLY BEFORE STARTING YOUR WORK. Failure to follow the instructions will result in point deductions. Projects not received by the due date will receive a late penalty of 10 percent per day and a grade of zero if more than 5 days late. Download all files from the blackboard course page, including the accompanying excel template, and save them. DO NOT COPY AND PASTE THE EXCEL TEMPLATE INTO A NEW WORKBOOK AND DO NOT WORK THROUGH THE WEB BROWSER. Show all work and related computations to receive full credit. Use formulas in all spreadsheet cells and reference given data cells as necessary. POINTS WILL BE DEDUCTED FOR ENTERING HARDCODED VALUES. BE SURE TO REGULARLY SAVE YOUR WORK. Excuses about lost work will not be accepted. Maintain a second (backup) copy of your file as well. CLEARLY LABEL AND SUBMIT FILES OF THE \"BASELINE SCENARIO\EXCEL ASSIGNMENT SELECTED BASELINE CHECK FIGURES 1. Check figures for schedules. 2. Check figures for projected income statements and balance sheet. 1
