Please complete work in excel and show ALL work if you are able


Question Description:

35

Please complete work in excel and show ALL work if you are able to assist.  If you cannot do in excel and show work, please do NOT try to assist. 1. Kroger Corporation had the following budget information related to the unit sales: 2015 Units July             25,000 August           53,000 September        21,000 October          34,000 Of the units budgeted, the Electronics Division sells 55% at a price of $30 per unit and the remainder are sold by the Toy Division at a price of $20 per unit. INSTRUCTIONS Prepare separate sales budgets for each division and for the company in total for the third quarter of 2015. 2. The following budget information relates to the Staples Company unit sales: 2015 Units April               27,000 May                 32,000 June                17,000 July                21,000 August              16,000 The finished goods units on hand on March 31, 2015, was 2,000 units. Each unit requires 3 pounds of raw materials that are estimated to cost an average of $6 per pound. It is the company’s policy to maintain a finished goods inventory at the end of each month equal to 15% of next month’s anticipated sales. They also have a policy of maintaining a raw materials inventory at the end of each month equal to 25% of the pounds needed for the following month’s production. There were 10,000 pounds of raw materials on hand at March 31, 2015. INSTRUCTIONS Prepare the following budget reports for the second quarter of 2015, a) a production budget (b) a direct materials budget. 3. The following budget information relates to the Frontier Company. For the second quarter of 2015, the following data are developed: 1. Sales:  30,000 units; unit selling price:  $25 2. Variable costs per dollar of sales: Sales commissions                         6% Delivery expense                          2% Advertising                               8% 3. Fixed costs per quarter: Sales salaries                       $40,000 Office salaries                       26,000 Depreciation                           7,000 Insurance                              2,000 Utilities                              1,000 INSTRUCTIONS Prepare a selling and administrative expense budget for the second quarter of 2015. 4. Marshall Department Stores has budgeted sales revenues as follows: Credit sales        July     $250,000 August    190,000 September 150,000 October   140,000 In the past, 75% of the credit sales were collected in the month of sale, 20% were collected in the first month following the sale and 5% in the second month following the sale. Purchases of inventory are all on credit and 28% is paid in the month of purchase and 72% in the month following purchase. Budgeted inventory purchases are: July            $200,000 August           100,000 September        125,000 October          150,000 Other cash disbursements budgeted:  (a) selling and administrative expenses of $20,000 each month, (b) dividends of $50,000 will be paid in September, and (c) purchase of a van in October for $45,000 cash. The company wishes to maintain a minimum cash balance of $50,000 at the end of each month. Borrowed money is repaid in months when there is an excess cash balance. The beginning cash balance on September 1 was $50,000. If money is borrowed, ignore interest INSTRUCTIONS (a) Prepare separate schedules for (1) expected collections from customers and (2)expected payments for purchases of inventory. SHOW ALL CALCULATIONS. (b) Prepare a cash budget for the months of September and October. 5. The following budget information relates to Mercedes Benz Corporation. Variable manufacturing overhead costs per machine hour are as follows: Indirect Labor           $7.25 Indirect Materials        3.50 Maintenance                .95 Utilities                  .52 Fixed overhead costs per month are: Supervision               $800 Insurance                  350 Property Taxes             400 Depreciation               900 Th…

Answer

35