Complete The Following Activities In Good Form Using Excel ✓ Solved

Complete The Following Activities In Good Form Use Excel Orword Only

Complete the following activities in good form. Use Excel or Word only. Provide all supporting calculations to show how you arrived at your numbers.

Sample Paper For Above instruction

Part A: Capital Budgeting Decisions

Matheson Electronics is considering the development of a new electronic device with broad market appeal. The company’s marketing and cost studies have provided detailed financial data relevant to this project. The first step involves evaluating the expected cash flows from sales, including necessary initial investments, operating costs, and salvage values, and then calculating the net present value (NPV) of the investment based on a required rate of return of 13%. This analysis will inform whether the device should be produced and marketed.

Project Details and Assumptions

  • Initial Equipment Cost: $138,000 with a useful life of six years and a salvage value of $24,000. The equipment depreciation is straight-line based on cost minus salvage value.
  • Sales Forecast: Yearly sales in units are projected to be 7,000 units in Year 1, 8,500 units in Year 2, 10,000 units in Year 3, 11,500 units in Year 4, 13,000 units in Year 5, and 16,000 units in Year 6.
  • Pricing and Costs: Sale price per unit is $55; variable costs per unit are $35. Fixed annual costs total $149,000, including salaries, maintenance, property taxes, insurance, and straight-line depreciation based on equipment cost less salvage value.
  • Additional Working Capital: $46,000 required to finance accounts receivable, inventories, and daily cash needs, which will be recovered at project completion.
  • Advertising Expenses: $75,000 in Years 1-2, $55,000 in Year 3, $45,000 in Year 4-6.

Required Analysis

  1. Calculate the net cash inflows for each of the six years, considering contribution margins minus fixed expenses (excluding depreciation) and adjustments for advertising and working capital.
  2. Determine the net present value of the investment using the cash inflows, salvage value, and initial investment, discounted at 13%.
  3. Provide a recommendation whether to proceed with the project based on the NPV result.

Part B: Master Budget Preparation

Earrings Unlimited, a distributor of earrings to retail outlets, needs a master budget for the upcoming second quarter. Past sales data, current inventory policies, purchase costs, and operating expenses are available. The budget should include detailed sales forecasts, cash collections, merchandise purchase schedules, cash disbursements, a cash budget, an income statement, and a balance sheet.

Key Data and Assumptions

  • Sales Units: Actual for January-March; budgeted for April-June. Sale price is $13 per pair.
  • Inventory Policy: End-of-month inventory to cover 40% of next month’s sales.
  • Payment Terms: Half of purchases paid in current month, half in following month; sales collections are 20% in month of sale, 70% in following month, 10% two months later.
  • Expenses: Variable sales commissions at 4%; fixed expenses include advertising ($230,000 annually), rent ($21,000/month), salaries ($112,000/month), utilities ($8,500/month), insurance ($3,300/month), and depreciation ($17,000/month).
  • Capital Expenditures: Planned equipment purchases of $17,500 in May and $43,000 in June, paid in cash.
  • Dividends: $17,250 per quarter, payable at the beginning of each quarter.
  • Minimum Cash Balance: $53,000, with borrowing allowed in $1,000 increments at a 1% interest rate.

Required Task

  1. Prepare a sales budget, cash collections schedule, merchandise purchases budget, and cash disbursement schedule for the period.
  2. Create a cash budget for each month, showing borrowings needed to maintain minimum cash levels.
  3. Develop a budgeted income statement (using contribution margin approach) for the three months.
  4. Construct a budgeted balance sheet as of June 30 based on the projected financials.

Part C: Variance Analysis for Decision Making

Marvel Parts, Inc. produces adjustable car seat covers and uses standard costing. During August, actual costs deviated from standards. The task is to compute variances to evaluate performance.

Details

  • Standard monthly production: 2,110 sets, requiring 1,055 hours at standard costs.
  • Standard costs: Materials $24.30 per set, Labor $5.00 per set, Variable overhead $2.30 per set.
  • Actual production: 2,100 sets, using 1,000 labor-hours, with actual costs of $49,980 for materials, $10,920 for labor, and $5,460 for overhead.
  • Materials consumed: 6,800 yards, standard material per set is 3 yards.

Analysis Tasks

  1. Calculate materials price and quantity variances.
  2. Compute labor rate and efficiency variances.
  3. Determine variable overhead rate and efficiency variances.

Part D: Measures of Internal Business Process Performance

DataSpan, Inc. implemented a flexible manufacturing system and is adopting Lean Production principles. Over four months, various process times and performance measures have been recorded. Calculations include throughput time, delivery cycle time, and manufacturing cycle efficiency (MCE). The exercise involves evaluating the company's performance, especially when queue and inspection times are eliminated in subsequent months.

Given Data (for month 4)

  • Move time per unit: 0.6 days
  • Process time per unit: 2.5 days
  • Wait time before production: 30.2 days
  • Queue time per unit: 7.0 days
  • Inspection time per unit: 0.5 days

Tasks

  1. Calculate throughput time, delivery cycle time, and MCE for each month.
  2. Evaluate performance trends over four months.
  3. Estimate the impact on throughput time and MCE if queue time and inspection time are eliminated in month 5 and 6.

Part E: Preparing Statements of Cash Flows

Using Weaver Company’s comparative balance sheets and income statements, prepare a statement of cash flows for the current year. The analysis should follow the direct method, adjusting net income for non-cash items, and then reconcile changes in balance sheet accounts to determine cash flows from operating, investing, and financing activities.

Key Data

  • Assets include cash, accounts receivable, inventory, prepaid expenses, property and equipment, investments.
  • Liabilities include accounts payable, accrued liabilities, taxes payable, bonds payable, dividends payable.
  • Income statement data include sales, cost of goods sold, operating expenses, and non-operating items like gains and losses.
  • Additional transactions: sale of equipment and investments, stock repurchases, dividends paid, and no bond retirements this year.

Required Tasks

  1. Adjust the income statement for cash basis using the direct method.
  2. Prepare a comprehensive statement of cash flows, detailing cash from operating, investing, and financing activities.

Related Assignments