Please advise on q2-3 using excel
Your new manager is responsible for the nationwide distribution of the newly developed Self Protection Device (SPD). using an ultrasonic sound emission, DNA material capture via a Tetractable claw, and an amazing 250.000 lumen LED flashing light. Prepare a master budget for the next three months, starting April 1, 2021. Pertinent information: Note: The company desires a minimum ending cash balance each month of $15.000. The SPDs are sold to retailers for $35 each and they are flying off the shelves. Recent forecasted sales in units are provided below: January (actual) 20,000 June 50.000 February (actual) 22,000 July 42,000 March (actual) 30,000 August 36,000 April 38,000 September 33.000 May 65,000 The increased sales volume before and during May is due to Mother's Day with SPD being a favorite. Ending inventories are supposed to be equal to 90% of the next month's sales in units. The cost of each SPD is $18. Purchases are paid for in the following manner: 50% in the month of the purchase and the remaining 50% paid in the monthly following the purchase. All sales to the distributors are made on credit terms with no discount (for now) and payable withing 15 days. The SPD division has determined that only 25% of a month's sales are collected by the end of the month in which the sale occurred. An additional 50% is collected in the month following the sale and the remaining 25% is collected in the second month following the sale. Bad debts have been negligible. supporting the favorable credit terms. Below is a display of the SPD division monthly selling and administrative expenses: Variable Sales Commissions $ 2 per SPD Fixed: Wages and Salaries $60,000 Utilities $42,000 Insurance $1,800 Depreciation $3,500 Miscellaneous $15,000 Selling and administrative expenses are all paid during the month, in cash, with the exception of depreciation of course) and insurance is pre-paid for the duration of the policy. Due to a violation of an existing copyright lawyers for Allteq Solutions have negotiated a one-time infringement penalty to be expensed and paid during May for $300,000 cash. The newly formed SPD division contributes to the corporate dividend at a rate of $175,000 each quarter, payable in the first month of following quarter. SPD's balance sheet at the end of the first quarter is shown below: Balance Sheet as of March 31, 2021 $44,000 810,000 Assets Cash Accounts receivable Inventory (34.200 Units Prepaid insurance Fixed assets, net of depreciation Total Assets 478,800 16,200 472.700 $1.821.700 Liabilities and Stockholders Equity Accounts payable $259,000 Dividends payable 175,000 Capital Stock 750,000 Retained earnings 637.700 Total Liabilities and Stockholders Equity 51.821,700 2) Schedule of expected cash collections from sales, by month and total 3) Merchandise purchases budget in units and in dollars, by month and total
