You have just been hired as a management trainee by a company selling cameras. The company has an exclusive franchise on the distribution of the cameras, and sales have grown so rapidly over the last few years that it has become necessary to add new members to the management team. You have been given responsibility for all planning and budgeting. Your first assignment is to prepare a master budget for the next three months, starting April 1. You are anxious to make a favorable impression on the president and have assembled the information below. The company desires a minimum ending cash balance each month of $10,000. The cameras are sold to retailers for $8 each. Recent and forecasted sales in units are as follows: January (actual) . . . . . . . . . . . . . . . 20,000 February (actual) . . . . . . . . . . . . . . 24,000 March (actual) . . . . . . . . . . . . . . . . 28,000 April . . . . . . . . . . . . . . . . . . . . . . . . . 35,000 May . . . . . . . . . . . . . . . . . . . . . . . . 45,000 June . . . . . . . . . . . . . . . . . 60,000 July . . . . . . . . . . . . . . . . . . 40,000 August . . . . . . . . . . . . . . . 36,000 September . . . . . . . . . . . . 32,000 382 Chapter 8 The large buildup in sales before and during June is due to Fathers Day. Ending inventories are supposed to equal 90% of the next months sales in units. The cameras cost the company $5 each. Purchases are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 25% of a months sales are collected by month-end. An additional 50% is collected in the following month, and the remaining 25% is collected in the second month following sale. Bad debts have been negligible. The companys monthly selling and administrative expenses are given below: Variable: Sales commissions . . . . . . $1 per camera Fixed: Wages and salaries . . . . . . $22,000 Utilicameras . . . . . . . . . . . . . . . $14,000 Insurance . . . . . . . . . . . . . $1,200 Depreciation . . . . . . . . . . . $1,500 Miscellaneous . . . . . . . . . . $3,000 All operating expenses are paid during the month, in cash, with the exception of depreciation and insurance expired. Fixed assets will be purchased during May for $25,000 cash. The company declares dividends of $12,000 each quarter, payable in the first month of the following quarter. The companys balance sheet at March 31 is given below: Assets Cash . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $ 14,000 Accounts receivable ($48,000 February sales; $168,000 March sales) . . . . . . . . . . . . . . . . . . . . . . 216,000 Inventory (31,500 units) . . . . . . . . . . . . . . . . . . . . . . . 157,500 Prepaid insurance . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14,400 Fixed assets, net of depreciation . . . . . . . . . . . . . . . . 172,700 Total assets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $574,600 Liabilities and Stockholders Equity Accounts payable . . . . . . . . . . . . . . . . . . . . . . . . . . . . $ 85,750 Dividends payable . . . . . . . . . . . . . . . . . . . . . . . . . . . 12,000 Capital stock . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300,000 Retained earnings . . . . . . . . . . . . . . . . . . . . . . . . . . . 176,850 Total liabilities and stockholders equity . . . . . . . . . . . $574,600 The company can borrow money from its bank at 12% annual interest. All borrowing must be done at the beginning of the month, and repayments must be made at the end of the month. Repayments of principal must be in round $1,000 amounts. Borrowing (and payment of interest) can be in any amount. Interest is computed and paid at the end of each quarter on all loans outstanding during the quarter. Round all interest payments to the nearest whole dollar. Compute interest on whole months (1/12, 2/12 and so on). The company wishes to use any excess cash to pay off loans as rapidly as possible. 1. Prepare the following budgets for the three months, starting April 1 a. Sales b. Cash Collection c. Purchases d. Budgeted cash payments for purchases e. Cash Budget 2. Prepare a budgeted income statement for each month for the next three months. Include a common size income statement for the budgeted numbers. 3. Management has ask that you present this information in the meeting. You were also asked to take perform some additional projections based on the following: a. Sales decrease 30% over next 3 months b. Sales increase 35% over next 3 months c. Sales commission increases from $1 to $1.50 d. Cost to produce a camera increases to $7.50 e. Interest rate to borrow money increase to 15%. You need to report on each projection separately and then collaborate. Your projection should include the impact on the budgets prepared in requirements 1 as well as the budgeted income statement. Additional Requirements Level of Detail: Show all work Other Requirements: I need an excel document with proper details