Please present your answers in Excel in a professional format using good titles
ID: 2548032 • Letter: P
Question
Please present your answers in Excel in a professional format using good titles and proper use of formulas where necessary.
Problem #1
Tandy, Inc. is in the business of manufacturing men’s and women’s leather computer bags. On January 1, 2019 they started their new fiscal year. The following is their trial balance as of December 31, 2018:
Account Dr Cr
Cash $ 43,200
Accounts Receivable $ 9,000
Raw Materials Inventory (1) $ 20,240
Work in Process Inv. (2) $ 0
Finished Goods Inv. (3) $ 8,100
Land $ 225,000
Equipment $ 310,000
Vehicles $ 84,200
Accumulated Depreciation $ 55,100
Long-term Investments $ 78,500
Accounts Payable $ 14,700
Wages Payable $ 8,470
Mortgage Payable (4) $ 214,500
Common Stock (5) $ 25,000
APIC $ 125,000
Retained Earnings $ 335,470
Totals $ 778,240 $ 778,240
Includes 2,860 feet of leather at $4 per foot, 5,000 feet of nylon lining at $1.25 per foot, and 510 golden buckles at $5 per buckle
No bags are currently in process at the beginning of January
Includes 150 completed bags (manufacturing overhead has been applied)
Monthly payments (interest and principle) are $ 2,500
$0.10 par value, 300,000 shares authorized and 250,000 shares outstanding
For the coming year, you have been put in charge of creating the operational budget schedules and the cash budget. Below is information for the first three months of the fiscal year.
Sales Forecasts
For this coming year, you have raised your sales price to $125 per bag. Based on sales contracts you have signed with your major corporate customers, you anticipate the following sales for the first three months of 2019:
January 250 bags
February 225 bags
March 190 bags
According to the terms of the sales contracts, you require each customer to pay 80% of the sales price in the month of sale and 20% in the month following. In December 2018, Tandy had $30,000 in total sales.
The production process
Each bag produced requires 4 feet of leather, 9 feet of nylon, 3 gold buckles, and 3.5 hours of direct labor. Currently, you are paying your assembly workers $17 / hour. In order to meet the following month’s demand, Tandy desires to keep 45% of the next month’s sales in Finished Goods Inventory. Tandy is forecasting sales in April 2019 of 285 bags. Additionally, they also keep 30% of next month’s production needs in raw materials inventory. Tandy anticipates production of 255 units in April 2019. Tandy has signed contracts with their suppliers to purchase leather at $4 a foot and buckles at $5 per buckle for the following year. Their policy is to pay for 75% of raw materials at the time of purchase and the remaining 25% in the following month.
Required
Please prepare the sales and production budgets for January – March of 2010.
Explanation / Answer
Sales Budget.
Month
Units Forecasted(A)
Price per unit($) (B)
Total Sales Revenue($) (A)*(B)
January
250
125
31,250
February
225
125
28,125
March
190
125
23,750
83,125
Purchase Budget.
Month
Opening inventory
Sales
Closing inventory
Number of units to be produced
Cost /unit ($)
Total cost of production ($)
January
150
250
101
201
102
20,502
February
101
225
86
210
102
21,420
March
86
190
128
232
102
23,664
643
102
65,586
Working notes
Cost per unit Computation.
Direct Material Cost
Direct Material
No.of unit
Cost /Unit ($)
Total Cost ($)
leather
4
4
16
nylon
9
1.25
11.25
gold buckles
3
5
15
Total direct material cost.
42.25
Direct Labor cost.
Labor hrs/unit
Cost /hr($)
Total cost
3.5
17
59.5
Cost of production
$
Direct Material
42.25
Conversion cost
59.5
Total
101.75
Rounded to nearest
102
Production for a month= sales forecasted +closing inventory forecasted - opening inventory forecasted.
Closing inventory= 45% of next month sales.
In January opening inventory is given, For rent of the months opening inventory = closing inventory of previous month
Month
Units Forecasted(A)
Price per unit($) (B)
Total Sales Revenue($) (A)*(B)
January
250
125
31,250
February
225
125
28,125
March
190
125
23,750
83,125
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.