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.

Answer 1

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