Tandy, Inc. is in the business of manufacturing men’s and women’s leather comput
ID: 2513800 • Letter: T
Question
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
1. 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
2. No bags are currently in process at the beginning of January
3. Includes 150 completed bags (manufacturing overhead has been applied)
4. Monthly payments (interest and principle) are $ 2,500
5. $0.10 par value, 300,000 share authorized and 250,000 share 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 $205 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 (all wages are paid in the quarter they are incurred). 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. Tandy incurred $ 8,000 of total materials purchases in December of 2018.
Selling, General and Administrative Expenses
Tandy anticipates the following expenses for January through March (all expenses are fixed and stated in the aggregate for all three months):
Sales & Delivery $ 12,500
Executive’s Salaries $ 44,100
Advertising Expenses $ 5,500
Mortgage Payments $ 7,500
Utilities on Admin offices $ 4,500
Required :Please complete the entire operational budget using Microsoft Excel
What is the projected ending cash balance in March?
Notes: Please list out the working process.
Explanation / Answer
Sales Budget January February March Total April No.of bags 250 225 190 665 285 Sale value at $ 205/bag 51250 46125 38950 136325 Sales collections 80% collections 41000 36900 31160 109060 20% collections 6000 10250 9225 25475 Total sales collections 47000 47150 40385 134535 7790 Production Budget(No.of Bags) January February March Total April Sales budgeted 250 225 190 665 285 Desired Ending Inventory 101 86 128 128 98 Total needed 351 311 318 793 383 Less:Available in Beg. Inv. 150 101 86 150 128 Production reqd. 201 209 233 643 255 Raw materials needed: Production bags reqd. 201 209 233 643 255 Leather reqd. 4 ft./bag 805 837 931 2573 1020 Add: Desired Ending Inventory 306 306 Total needed 1237 2879 Less:Available in Beg. Inv.(2860-805-837) 1218 2860 Purchases reqd. 0 0 19 19 Purchase cost at $4 /ft.---------------------a 0 0 76 76 Nylon reqd.9 ft./bag 1811 1883 2095 5789 2295 Add: Desired Ending Inventory 689 689 Total needed 2783 6478 Less:Available in Beg. Inv.(5000-1811-1883) 1306 5000 Purchases reqd. 0 0 1477 1478 Purchase cost at $1.25 /ft.--------------------b 0 0 1847 1847 Gold buckles reqd. at 3/bag 604 628 698 1930 765 Add: Desired Ending Inventory 188 209 230 230 Total needed 792 837 928 2159 Less:Available in Beg. Inv. 510 188 209 510 Purchases reqd. 282 649 718 1649 Purchase cost at $ 5-----------------------c 1410 3245 3591 8246 Total Raw material Purchases------d= a+b+c 1410 3245 5514 10169 Payment for purchases 75% payment*Current month (d*75%) 1058 2433 4135 7627 25% payment*previous mth. (d*25%) 2000 353 811 3164 Total payment for raw mat. Purchases 3058 2786 4947 10791 Payment for direct labor Production bags reqd.--------------------1 201 209 233 643 Direct labor hrs. at 3.5 hrs /unit--------1*3.5 704 732 815 2251 Direct labor cost at $ 17/hr. 11974 12450 13849 38273 Cash Budget Beginning Balance 43200 29798 34512 43200 Total sales collections 47000 47150 40385 134535 Collection of bal a/cs.Rec.(9000-6000) 3000 3000 Total cash available------------1 93200 76947.84 74896.5 180735 Less: Disbursements: Total payment for raw mat. Purchases 3058 2786 4947 10791 Direct labor cost at $ 17/hr. 11974 12450 13849 38273 Monthly mortgage int.& principal 2500 2500 2500 7500 Sales & delivery 4167 4167 4167 12500 Executive's salaries 14700 14700 14700 44100 Advertising expenses 1833 1833 1834 5500 Mortgage payments 2500 2500 2500 7500 Utilities on admn.offices 1500 1500 1500 4500 Payment of balance a/cs.payables (14700-2000) 12700 12700 Wages payable (Dec 2018) 8470 8470 Total Disbursements---------2 63402 42436 45996 151834 Ending balance of cash -----1-2 29798 34512 28901 28901 Projected Ending Cash balance in March 28901 v
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.