Prepare a Cost of Goods Sold Budget using the following information:

REQUIRED

Prepare selected operating budgets in good form for Traynham Innovations for the months indicated below:

For January, February, and March of 2015:

Sales budget

Production budget

For February of 2015:

Direct materials purchases budget

Direct labor budget

Factory overhead budget

Cost of goods manufactured budget

Budgeted income statement

Be sure to verify you have complied with all formatting and project requirements prior to submission.

?

FORMATTING

Use Excel 2010 to create dynamic operating budgets for the specified months. Your work should be professionally formatted with the same font size and style used consistently throughout all budgets.

NOTE: If you are not competent in using Excel, you will want to work through the SkillSoft Excel 2010 tutorials prior to beginning the project. These tutorials are free to all UNF students and are accessible in the Quick Links section of myWings. After you log into SkillSoft from myWings, type Excel 2010 in the Search field using the category courses choice, and work through all tutorials containing enus in the course ID.

BASIC EXCEL WORKSHEET FORMATTING

All budgets should be prepared on a single worksheet in Excel. The work area on the worksheet should occupy an area no wider than what can be printed at 100% scaling on a standard 8" by 11" inch page. Hint: Choose File then Print from the Excel menu ribbon. The last item in the column under the Print button must display as No Scaling.

Set the zoom level so the page is viewed at 120%.

Set the print range for your budgets using portrait orientation with no print gridlines displayed.

Set the margins at 1" side, top and bottom margins, and 0.5" header and footer margins.

Set the worksheet to a print range such that your work will print with no page breaks within a particular budget.

Use a professional 11 or 12-point font.

Use unabbreviated, concise line item labels. Wrapping labels should only be used in extreme instances.

Be consistent in numeric column widths. Minimize the width to accommodate the content within the respective columns. Shorten line item labels if necessary.

Use standard U.S. punctuation, spacing, date format, capitalization, etc.

Display the numeric amounts with the following decimals:

Display with 2 decimals: Cost per unit, hours per unit, selling price per unit, or other quantities per unit, and percentages.

Display with no decimals (round to nearest whole number): All other amounts

Use consistent accounting number formatting.

Include $ signs on the first monetary amount and final total in each column of each budget (See textbook for placement).

Use bottom borders or underlines consistently in good form. Final totals of each budget require double underlines or borders. (See text for placement.)

Do not provide calculations outside of the standard budget formats. Remove any stray borders, shading, etc.

Run spell check and proof prior to submitting.

HEADER AND PAGE NUMBERS

Place the following in the upper right page header of the Excel worksheet:

Team x (replacing x with your team number) on the first line of the page header

The names of all team members on the second line of the page header (alpha order)

Place automatic page numbers in the lower right footer.

CELL REFERENCING and FORMULAS

All budgets that use amounts displayed in or generated by another budget must cell reference to the respective amounts in the source budgets. Your worksheet should be set up for what-if analysis, such that if the CEO makes a change in the units or selling price in the sales budget, all other budgets will be automatically be populated with the new data. To achieve this, you must cell reference ALL subsequent budgets amounts to the respective source budget(s) to the extent possible.

Calculate all amounts not specifically given in the project data using Excels mathematical formulas, making full use of cell referencing whenever possible. DO NOT hard code amounts unless a specific amount is given in the data that does not appear in another budget.

PLACEMENT and DESIGN OF BUDGETS

This section provides general guidance for each of the required budgets. The format will differ somewhat from your textbook budget formats, as the text illustrates only one raw material, and this companys product uses multiple direct materials.

?

General placement and design

Budgets should be placed vertically in the order in which they are typically prepared in the budget process (as assigned in the project).

Begin the sales budget in row 2 with line item labels in column A.

Leave two blank rows between each budget.

Create a standard 2-line statement heading (omit company name) for each budget, and use merge & center to align each heading line across the columns occupied by each respective budget.

Clearly and concisely label each line item in each budget using no abbreviations. Wrap text only in extreme cases in which concise labels are not possible.

Include extra blank lines within budgets only when it is necessary to present the budget information in professional format.

Sales and production budgets. These budgets should be prepared for three months using the following numeric column headers:

January

February

March

Direct labor budget. Use a single-column format to present numeric amounts,

?

Direct materials purchases budget. Use a separate column for numeric amounts of each material cost.

Resin

Bottom Plates

Material usage quantities should appear in the respective quantity denominations in which the company uses the materials, i.e., pounds, each. (Hint: Use the denomination as part of your line item label, (e.g., Yards of fabric needed for each shirt), and use only numeric amounts in numeric columns. Subsequent conversion to purchase quantities should be displayed as a separate line item. (Hint: Use one of Excels ROUND functions in a separate line item calculation to determine purchase quantities.) Display unit material quantities using 2 decimals.

Factory overhead budget. Use a single-column format to present numeric amounts.

Cost of goods sold budget. Use a separate line item for each of the three primary product cost components with additional separate line items for each direct material, to arrive at the total production cost. Include additional budget line items to convert the total production cost to unit costs for each product. (Do not use beginning and ending inventory amountsinventory levels are not material.)

?

Budgeted income statement. Prepare in multiple-step format. List operating expenses alphabetically. Costs that are not matched against product revenues should not be allocated to products.

?

?

DATA, INFORMATION, and COMPANY POLICIES

Selected data and information follows:

Selected account balances at January 31, 2015 are:

Cash	$60,800
Note payable, 6%	94,000

Budgeted unit sales of mugs for selected months of 2014 and 2015:

November, 2014 21,100

December, 2014	22,800
January, 2015	20,300
February, 2015	19,900
March, 2015	23,200
April, 2015 May, 2015	18,800 20,500

Selling price is $11 per mug. Twenty percent of the sales are cash while the remainder are credit sales. Traynham Innovations collects 32% of credit sales within the month in which they are sold, 52% in the following month, and 14% in the subsequent month. The remainder is not collected and is written off under the allowance method at 75 days after the end of the month in which the sale occurred (e.g. December uncollectibles are written off in March.) Traynham uses the percent of sales approach to estimate uncollectibles.

Traynham desires to have 10% of the next months projected sales of mugs on hand at the end of each month.

Each mug uses nine minutes of direct labor, 12 ounces of resin, and two aluminium bottom plates that are fused together during the production process. Labor is paid at the rate of $16 per hour. Fringe benefits are $2.25 per hour. Resin is ordered in 40 pound containers from the supplier at a cost of $1.62 per pound. Aluminium bottom plates cost $0.37 each and are purchased in cases of 100.

Traynham plans to have 22% of the resin and aluminium bottom plates needed for the subsequent months production needs on hand at the end of each month.

All direct materials are purchased on account. Forty percent of the purchases are paid for in the month of acquisition, with the remainder paid in the following month.

Production wages are paid on the 15^{th and the last day of each month for the time period just ended.}

Accounts payable is used only for materials purchases. Individual accrued liability accounts are used for other obligations.

Fixed overhead is budgeted to be applied at $0.95 per mug. Of the total, $4,650 per month represents depreciation. The company uses normal costing. Fixed overhead is paid in the month incurred.

Variable overhead is budgeted at $1.90 per direct labor hour. All variable overhead expenses are paid for in the month incurred.

Fixed selling and administration expenses total $21,800 per month, which includes $6,000 of depreciation.

Variable selling and administrative expenses are budgeted at $0.70 per mug sold.

All selling and administrative expenses are paid during the month after incurrence.

The note payable is dated January 1, 2014 and equal principal payments of $4,200 are paid on the last day of each month along with accrued interest based on the number of days. The given balance at January 31 is after the January 31 principal and interest payment.

Dividends declared in January will be paid on February 15 totalling $21,000.

Income taxes are 30% and are paid in the month after accrual. Taxes for January are budgeted at $21,200, while taxes for March are budgeted at $20,300.

I've already done the Sales Budget, Production Budget, and the Direct Materials Budget for you to reference.

Sales Budget	January	February	March
Units	20,300	19,900	23,200
Selling Price	$11	$11	$11
Projected Sales	$223,300	$218,900	$255,200
Production Budget	January	February	March
Budget sales (units)	20,300	19,900	23,200
Targeted finished goods inventory	1990	2320	1880
Total required units	22,290	22,220	25,080
Less beginning finished good inventory	2030	1,990	2,320
Units of finished goods to be produced	20,260	20,230	22,760
Direct Materials Purchases Budget	Resin in Pounds	Bottom Plates	Total
Needed to produce one mug	0.75	2	?
Mugs to be produced	20,230	20,230	?
Materials to be used in production	15,172.5	40,460.0	?
Target ending inventory	3,755.4	10,014.4	?
Total requirements	18,927.9	50,474.4	?
Less beginning inventory	3,338.0	8,901.2	?
Quantity need for production	15,590.0	41,573.2	?
Single order quantity	40	100	?
Number of orders to purchase	390	416	?
Total quantity to be ordered	15600	41600	?
Cost per unit	$1.62	$0.37	?
Cost for material purchases	$25,272.00	$15,392.00	$40,664.00

Please read all instructions and remember that all budgets that use amounts displayed in or genenrated by another budget must be cell referenced to the respective amounts in the source budgets. And calculate all amounts not specifecally given inthe project data using Excel's mathematical formulas. making full use of the cell referencing whenever possible. Do Not hard code amounts unless a specific amount is given inthe data that does not appear in another budget.