Please assist with the cells marked in red on the excel sheet. Those cells need to equal the the highlighted yellow areas below it and I am having difficulty. I have pulled the necessary information for the problem below.

As shown in Exhibit 1, total monthly unit sales volume is expected to continue increasing through the third quarter and into the fourth, but with sales mix shifting toward the new premium model. 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 has found that only 20% of a months sales are collected by month-end. An additional 50% is collected in the month following sale, and the remaining 30% is collected in the second month following sale. Thus far, bad debts have been negligible.

Since PDAs policy is to never stock out of its inventory, and potentially forfeit market share to competitors, the Company maintains buffer inventory. Therefore, desired ending inventories are equal to 75% of the next months sales in units. Prior to June, PDA sold only the basic model (BASE) at a price of $230 per unit. The BASE model costs PDA, Inc. $155 each from a contracted, overseas manufacturer and it pays for purchases as follows: 50% in the month of purchase and the remaining 50% the following month. In June, PDA began carrying a premium model (PREM), which sells for $380 per unit and costs $225 from the overseas manufacturer. The current sales mix is 80 percent BASE and 20 percent PREM. However, going forward the Company expects this mix to shift toward the premium model, along with ongoing competitive pricing pressure (as reflected in Exhibit 1). Inventory is assumed to be sold on a first-in, first-out (FIFO) method. NOTE: this must be modeled when determining COGS.

EXHIBIT 1

PDA, Inc.s Sales Forecast and Other Sales Information

Unit Sales Unit Volume Sensitivity 0.0% UNIT SALES Month TOTAL BASE PREMIUM ACTUAL April May 7,650 8,400 7,6508 ,400 June 9.200 7,360 1,840 July 10,200 7,956 2.244 FORECAST August September 11,400 12,700 8,550 9,017 2,850 3,683 October 13, 2001 9,240! 3,960: na Overall Sales Mix Sensitivity 0.00% 2% 1% Sales Mix Sensitivity Sales Mix % 4% 71% 80% 3% 75% 25% BASE PREMIUM 70% 78% 22% 20% 29% 30% Sales Price Sensitivity Unit selling price BASE PREMIUM 230 380 $ IS -2.00% 225.40 $ 372 $ -2.00% 220.89S 365 $ -2.00% 216.47S 358S -2.00% 212.14 350 Unit cost $ $ 155 BASE PREMIUM 155 225 155 225 155 $ 225$ 155 $ 225$ 225 1 Weighted Average Sales Price Weighted Average Unit Cost 260.00 169.00 $ 257.74 $ 170.40 $ 256.91 $ 172.50 S 257.42 $ 175.30 $ 253.65 176.00 Gross Profit % BASE PREMIUM WTD AVG 32.6% 40.8% 34.2% 31.2% 39.6% 33.1% 29.8% 38.3% 32.0% 28.4% 37.1% 30.9% 26.9% 35.8% 29.6%) $ 40,000 (% of sales) (per unit) 75% $ 50% 50% Other Assumptions Minimum 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 Operating expenses Variable: Sales commissions Shipping & handling Fixed: Wages and salaries Rent Utilities Insurance expired Depreciation Other S&A Advertising Campaign 5.0% 15.00 (per month) 315,000 26,000 9.500 4,500 16,000 38,000 20% 50% 30% 100,000 20,000 $ $ 8% Click and hold down "Ctrl" and "Home" keys to go back to the top PDA, Inc. Budget Schedules TOTAL Schedule a: Sales plan Weighted average sales price July 257.74 August September 256.91 $ 257.42 $ $ Total Units Credit sales, 100% $ 10,200 2,628,948'S $2,628,948 11,400 2,928,740'S $2,928,740 1 2,700 3,269,183 $ $3,269,183 34,300 8,826,871 $8,826,871 Baseline Model Check Fig's Note: Actual June 30 A/R on Balance Sheet must be received in July and August Schedule b: Cash collections July From current month's sales 525,790 From sales 1 month before 1,196,000 From sales 2 months before 579,600" Total collections 2,301,390 Accounts Receivable at September 30 Baseline Model Check Fig's $2,301,390 August 585,748 1,314.474' 717.600 2,617,822 September 653,837 1,464,370 717,600 2,835,807 TOTAL 1,765,374 3,974,844 2,014,800 7,755,018 7,755,018 $7,826,103 $ $2,617,822 $2,906,891 July August 9,5257 11,400 Schedule c: Purchases plan in units Desired units in ending inventory Plus units sold Total needed Less beginning inventory Required purchases Baseline Model Check Fig's 8,550 10,200 18,750 7,650 11,100 11,100 20.925 8,550 September 9,900 12,700 22,600 9,525 13,075 13,075 TOTAL 27,975 34,300 62,275 25,725 36,550 36,550 12,375 12,375 Schedule d: Purchases plan (dollars) July August September TOTAL '$ Desired ending inventory Cost of goods sold Total needs Less beginning inventory Total purchases Baseline Model Check Fig's 1,456,920'S 1,738,080 3,195,000 1,303,560 1,891,440'S $1,891,440 1,643,063 '$ 1,795,185 3,438,248 1,303,560 2,134,688 '$ $2,134,688 1,735,470 $ 1,860,137.50 3,595,608 1,303,560 2,292,048 $ $2,292,048 4,835,453 5,393,403 10,228,855 ,910,680 6,318,175 $6,318,175 3 $ August $ Note: Actual June 30 A/P on Balance Sheet should be paid in July only Schedule e: Cash pmts for purchases July From accounts payable (6/30) $ 1,891,440 July 100,000 August September Total payments $ 1,791,440 S Accounts Payable at September 30 Baseline Model Check Fig's $1,791,850 2,134,688 100,000 September TOTAL 1,891,440 2,234,688 2.292,048 2.392,048 100,000 100.000 2,192,048 $ 6,018, 175 $ 6,018, 175.00 $2,213,368 $6,018,281 2,034,688 S $2,013,064