Posted: September 16th, 2017

Cost Accounting

Cost Accounting

The following assignment is a take-home portion of Exam 2. It is worth 25 points of the exam.

Budget Problem:
John Plants, Inc. manufactures small biodegradable seedling starter trays.  David Mitchell, owner, wants you to prepare the 2014 master budget for the company. The balance sheet at the end of 2013 follows:

Sales volume in Quarter 4 -2013 was 2,600,000 trays. Based on their research, the marketing department forecasts sales volume will be: 1,500,000 trays in Quarter 1 – 2014; 900,000 trays in Quarter 2 – 2014; 1,225,000 trays in Quarter 3 – 2014; and 1,950,000 trays in Quarter 4 – 2014.  For Quarter 1 – 2015 and Quarter 2 – 2015 they forecast that 1,800,000 and 950,000 trays will be sold, respectively.  To achieve the projected sales volume, David estimates a selling price of $3.75 per tray.

Based on previous estimates of sales fluctuations, the marketing department believes that the desired ending finished goods inventory each quarter should equal 16% of the following quarter’s sales quantity.  The beginning finished goods inventory in 2014 is expected to meet the inventory policy.  The accounting department estimates that the units in beginning finished goods will have an assigned cost of $1.30 per tray.

The trays are produced using a material purchased from a supplier for $.30 per ounce.  It takes .6 ounces of material to produce one tray.  To avoid material stock-outs, David maintains ending raw materials inventory equal to 19% of the following quarter’s production needs.  Materials inventory at the beginning of 2014 meets this policy.  The cost of beginning materials inventory is expected to be $0.20 per ounce.

Based on a study of the production processes, it takes 3 minutes to produce one tray.  Direct labor workers are paid $13.5 per hour.

The accounting department has gathered the following information about the remaining product and period costs.  (For Manufacturing Costs: all variable rates are per unit produced, and all fixed rates are per quarter. For Selling, General, and Administrative Expenses (SGA): all variable rates are per unit of sales, and all fixed rates are per quarter).

Assume that there is no beginning or ending work-in-process inventories.

Cash information:
Cash collections of sales revenue are 80% in the quarter of sale; 18% in the quarter following the sale; and 2% uncollectible. John Plants recognizes bad debt expense on an accrual basis rather than the direct-write off method.

Materials purchased for production are the source of accounts payable. Purchases are paid 75% in the quarter of purchase; and 25% in the quarter following purchase. We pay all our debts.

At the beginning of Quarter 1 – 2014, John Plants borrowed $100,000 from a local financial institution at an annual interest rate of 4%. Repayment of the loan will take place over the year with quarterly payments (at end of each quarter) of $25,000 principal plus accrued interest. The loan will be used to increase factory space and make improvements to the plant. The amounts spent will be as follows: $30,000 in Quarter 1 – 2014; $20,000 in Quarter 2 – 2014; $20,000 in Quarter 3 – 2014; and $25,000 in Quarter 4 – 2014.

Required:
Using Excel, prepare the master budget. Begin with the Balance Sheet – 2013; include all operating budgets; include a cash budget; and end with the Balance Sheet – 2014. You must prepare your own budget template and answers. Group work on Excel will not receive credit.
•    All inputs must be on the initial page of the workbook and all subsequent worksheets linked to the input page. Budgets that are not properly linked will not receive credit!
•    Each budget must be on a separate worksheet within the Excel workbook.
•    Each worksheet tab must be labeled with the name of the budget.
•    Each budget must be properly formatted (title, labels, etc.).
•    Any worksheet cell that involves a calculation must include a formula.
•    Any worksheet cell that uses a value from another budget or table must include a cell reference.
.

Qtr 1    Qtr 2    Qtr 3    Qtr 4    Year
Total sales    $5,625,000     $3,375,000     $4,593,750     $7,312,500     $20,906,250

Units to be produced    1404000    952000    1341000    1926000    5623000

Direct materials purchases    $210,278.40     $184,663.80     $261,387     $337,719.60     $994,048.80

Total direct labor cost    $947,700     $642,600     $905,175     $1,300,050     $3,795,525

Total manufacturing overhead costs    $868,320     $719,160     $847,530     $1,040,580     $3,475,590

Total SGA expenses    $2,020,000     $1,240,000     $1,662,500     $2,605,000     $7,527,500

Ending cash balance    $2,162,032.20     $3,140,954.75     $3,825,543.55     $5,292,902.10     $5,292,902.10

Unit cost of finished goods    1.468656411    for year
Ending finished goods inventory    $422,973.05     2014

Cost of goods sold    $8,147,281.95     for year

Net income    $4,810,843.05     for year

Total assets    $11,974,033.95     2014

Expert paper writers are just a few clicks away

Place an order in 3 easy steps. Takes less than 5 mins.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00
Live Chat+1-631-333-0101EmailWhatsApp