1) Prepare sales revenue Budget for July through September (excel spreadsheet)

2) Prepare a schedule of Cash collections for july through september

1) Prepare a sales revenue budget for July through September

What is the selling/budgeting price?

Please help Palm west product (B)!

Palm-West Products (B) 1. Background Valerie Preston formed Palm-West Products, Inc. in 2003 when she obtained an exclusive franchise to nationally distribute designer windows. Recent high sales growth of the base window (WIND-B), along with expected sales growth for a new enhanced window (WIND-E), requires adding management team members. Ms. Preston is keenly aware of other growth-oriented companies within the designer window sector that have run out of cash and gone into bankruptcy. Seeing potentially viable businesses fail particularly concerns Ms. Preston and consequently, she wants to ensure that sufficient cash will be available to accommodate PWP's expected growth. She would like to present a financial plan to PWP's board of directors and needs 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. 1.1 Company information From her prior education and work experience, Ms. Preston understands that the starting point for a financial plan is a reliable sales forecast. Thus, she conferred with Tom Sellers, the sales manager, along with an outside marketing consultant. Tom has studied industry sales and economic trends, along with other important market factors within the highly competitive designer window 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 enhanced window(WIND-E). In addition, Ms. Preston determined 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 indicates that only 20% of a month's 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 Palm-West Products does not want to forfeit market share to competitors, it maintains a policy to never stock out of its 1 Palm-West Products (B) windows. Therefore, the company maintains desired ending inventories equal to 75% of the next month's unit sales. Prior to June, PWP sold only the basic window (WIND-B) at a price of $230 per unit. PWP purchases the WIND-B model from an overseas manufacturer for $155 per unit. The company pays for inventory purchases as follows: 50% in the month ofpurchase and the remaining 50% the following month. In June, PWP began carrying a premium model WIND-P, priced at $380, which costs $225 from the manufacturer. The current sales mix is 80% WIND-B and 20% WIND-P. However, the company expects this mix to shift toward the premium model, along with ongoing competitive pricing pressure (see Exhibit 1). Unit Sales Total BASE PREMIUM July 10,200 7,956 2,244 Forecasted Sales Volume August September 11,400 12,700 8,550 9,017 2,850 3,683 October 13,200 9,240 3,960 Sales Price BASE PREMIUM 225 372 221 365 216 358 212 350 Unit Cost BASE PREMIUM 155 225 155 225 155 225 155 225 Exhibit 1. PWP's sales forecast and other sales information. Total sales revenue in May was $1,932,000, while it was $2,392,000 in June. 1.2 Requirements (in an Excel spreadsheet) 1. Prepare a Sales Revenue Budget for July through September. 2. Prepare a Schedule of Cash Collections for July through September