Case Study 1 Master Budgeting and Pro-Forma Financial Statements You have just been assigned to a new manager who believes you have exceptional budgeting skills. Since you began your job last summer, you have been showing management your latest spreadsheets and how you use your new-found knowledge of Managerial Decision Making to make sound business decisions. Your new manager is responsible for the nationwide distribution of the newly developed Self Protection Device (SPD), using an ear piercing ultrasonic sound emission, DNA material capture via retractable claw, a quick pulsating spray of bear repellant, and an amazing 250,000 lumen LED strobing light. Through multiple franchise agreements, sales have grown very rapidly, and the timing is right for you to join her team and to show your skills. You have just been given responsibility for all planning and budgeting of the entire division and the title of Staff Manager-Planning. Your first assignment is to prepare a master budget for the next three months, starting April 1, 2021. You accept this responsibility with enthusiasm, and you are anxious to impress your new manager and the president of the parent company (Allteq Solutions), who has a very high regard for you. To commence your new role, you have assembled the following pertinent information: Note: The company desires a minimum ending cash balance each month of $10,000. The SPD's are sold to retailers for $28 each and they are flying off the elves. Recent forecasted sales in units are provided below: January (actual) February (actual) March (actual) April May July August September 55,000 42,000 35,000 32,000 62,500 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 $14.00 direct from an offshore supplier with exclusive production rights. This cost includes all manufacturing costs, including packaging and shipping so your schedule should not include materials, labor and overhead but rather just purchases of inventory. Purchases are paid for in the following manner: 50% in the month of the purchase and the remaining 50% paid in the month following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 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 credit terms as favorable. Below is a display of the SPD division monthly selling and administrative expenses: Variable: Sales Commissions $1 per PDD Fixed: Wages and Salaries $65,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 (please note that the improper handling of these items have contributed to the dismissal of your predecessor). 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 $250,000 cash (ouch). The newly formed SPD division contributes to the corporate dividend at a rate of $120,000 each quarter, payable in the first month of the following quarter. SPD's balance sheet at the end of the first quarter is shown below: Balance Sheet as of March 31, 2021 Assets Cash Accounts receivable Inventory (34,200 units) 453,600 Prepaid insurance 16,200 Fixed assets, net of depreciation 472,700 Total Assets $1,713,500 XORNADA bilities and Stockholders Equity Accounts payable $245,700 Dividends payable 120,000 Capital Stock 750,000 Retained earnings 597,800 Total Liabilities and Stockholders Equity $1,713,500 An agreement with East Rivers Bank allows to borrow in increments of $1,000 at the beginning of each month, up to a total loan amount of $350,000. The interest rate on these loans is 6.5% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only (Warning: miscalculation of interest expense is unacceptable and will prevent correct financial statements. Seek help if necessary). At quarter end, SPD would pay East Rivers Bank of the all of the accumulated interest on the loan and as much of the balance of the loan as possible (in $1,000 increments) while retaining the minimum $10,000 cash balance. (Hint: use this excel formula to determine the amount needed to borrow) +IF(excess/deficit