Prepare a monthly Sales budget & Schedule of Cash Receipts. Tvler Ltd. Tyler Ltd
ID: 2580545 • Letter: P
Question
Prepare a monthly Sales budget & Schedule of Cash Receipts.
Tvler Ltd. Tyler Ltd. is a company that manufactures and sells a single product, called a Gadget. For planning and control purposes they utilize a monthly master budget, which is usually developed at least six months in advance of the budget year. Their fiscal year end is December 31 Senior management and department leaders have met and gathered data on the plans for the future of the company The company has recently had to fire its Controller. The CEO, needing the budget completed, has approached you, a management accounting student, for help in preparing the budget for the coming fiscal year. Your conversations with the CEO and your investigations of the company's records have revealed the following information: 1. Their sales forecast: For the year ended December 31, 2017: 190,000 units at $25.00 each* For the year ended December 31, 2018: 200,000 units at $25.00 each For the year ended December 31, 2019: 210,000 units at $25.00 each Expected sales for the year ended December 31, 2017 are based on actual sales to date and budgeted sales for the duration of the year 2. Sales are seasonal with the peak months being the summer months and Christmas season. The following table shows expected distribution of sales for each month based on percentage of the total budgeted sales. Months Jan, Feb, Mar Apr, Aug. Sept May, Jun, Jul & Oct centag 4% each 5% each 8% each 16% 25% Dec 3. Sales are on a cash and credit basis, with 55% collected during the month of the sale. 35% the following month and 9.5% the month thereafter. ½ of 1% of sales are considered uncollectible (bad debt expense). 4. From previous experience, management has determined that an ending inventory equal to 30% of the next month's sales is required to meet the buyer's demands. 5. Because sales are seasonal, Tyler must rent an additional storage facility for October and November to house the additional finished goods inventory on hand. The only related cost is a flat $15,000 per month, payable at the beginning of the month.Explanation / Answer
Monthly Sales Budget Month Calculation Sales (in Quantity) Sale Price Sales (in amount) Jan-17 190000 x 4% 7600 $25 $190,000 Feb-17 190000 x 4% 7600 $25 $190,000 Mar-17 190000 x 4% 7600 $25 $190,000 Apr-17 190000 x 5% 9500 $25 $237,500 May-17 190000 x 8% 15200 $25 $380,000 Jun-17 190000 x 8% 15200 $25 $380,000 Jul-17 190000 x 8% 15200 $25 $380,000 Aug-17 190000 x 5% 9500 $25 $237,500 Sep-17 190000 x 5% 9500 $25 $237,500 Oct-17 190000 x 8% 15200 $25 $380,000 Nov-17 190000 x 16% 30400 $25 $760,000 Dec-17 190000 x 25% 47500 $25 $1,187,500 Jan-18 200000 x 4% 8000 $25 $200,000 Feb-18 200000 x 4% 8000 $25 $200,000 Mar-18 200000 x 4% 8000 $25 $200,000 Apr-18 200000 x 5% 10000 $25 $250,000 May-18 200000 x 8% 16000 $25 $400,000 Jun-18 200000 x 8% 16000 $25 $400,000 Jul-18 200000 x 8% 16000 $25 $400,000 Aug-18 200000 x 5% 10000 $25 $250,000 Sep-18 200000 x 5% 10000 $25 $250,000 Oct-18 200000 x 8% 16000 $25 $400,000 Nov-18 200000 x 16% 32000 $25 $800,000 Dec-18 200000 x 25% 50000 $25 $1,250,000 Jan-19 210000 x 4% 8400 $25 $210,000 Feb-19 210000 x 4% 8400 $25 $210,000 Mar-19 210000 x 4% 8400 $25 $210,000 Apr-19 210000 x 5% 10500 $25 $262,500 May-19 210000 x 8% 16800 $25 $420,000 Jun-19 210000 x 8% 16800 $25 $420,000 Jul-19 210000 x 8% 16800 $25 $420,000 Aug-19 210000 x 5% 10500 $25 $262,500 Sep-19 210000 x 5% 10500 $25 $262,500 Oct-19 210000 x 8% 16800 $25 $420,000 Nov-19 210000 x 16% 33600 $25 $840,000 Dec-19 210000 x 25% 52500 $25 $1,312,500 600000 $25 $15,000,000 Schedule of Cash Rreceipts Month Current Month Collection 55% Previous Month Collection 35% Earlier than previous month's collection 9.5% Total Collection in the month Jan-17 $104,500 Data missing Data missing $104,500 Feb-17 $104,500 $66,500 Data missing $171,000 Mar-17 $104,500 $66,500 $18,050.0 $189,050 Apr-17 $130,625 $66,500 $18,050.0 $215,175 May-17 $209,000 $83,125 $18,050.0 $310,175 Jun-17 $209,000 $133,000 $22,562.5 $364,563 Jul-17 $209,000 $133,000 $36,100.0 $378,100 Aug-17 $130,625 $133,000 $36,100.0 $299,725 Sep-17 $130,625 $83,125 $36,100.0 $249,850 Oct-17 $209,000 $83,125 $22,562.5 $314,688 Nov-17 $418,000 $133,000 $22,562.5 $573,563 Dec-17 $653,125 $266,000 $36,100.0 $955,225 Jan-18 $110,000 $415,625 $72,200.0 $597,825 Feb-18 $110,000 $70,000 $112,812.5 $292,813 Mar-18 $110,000 $70,000 $19,000.0 $199,000 Apr-18 $137,500 $70,000 $19,000.0 $226,500 May-18 $220,000 $87,500 $19,000.0 $326,500 Jun-18 $220,000 $140,000 $23,750.0 $383,750 Jul-18 $220,000 $140,000 $38,000.0 $398,000 Aug-18 $137,500 $140,000 $38,000.0 $315,500 Sep-18 $137,500 $87,500 $38,000.0 $263,000 Oct-18 $220,000 $87,500 $23,750.0 $331,250 Nov-18 $440,000 $140,000 $23,750.0 $603,750 Dec-18 $687,500 $280,000 $38,000.0 $1,005,500 Jan-19 $115,500 $437,500 $76,000.0 $629,000 Feb-19 $115,500 $73,500 $118,750.0 $307,750 Mar-19 $115,500 $73,500 $19,950.0 $208,950 Apr-19 $144,375 $73,500 $19,950.0 $237,825 May-19 $231,000 $91,875 $19,950.0 $342,825 Jun-19 $231,000 $147,000 $24,937.5 $402,938 Jul-19 $231,000 $147,000 $39,900.0 $417,900 Aug-19 $144,375 $147,000 $39,900.0 $331,275 Sep-19 $144,375 $91,875 $39,900.0 $276,150 Oct-19 $231,000 $91,875 $24,937.5 $347,813 Nov-19 $462,000 $147,000 $24,937.5 $633,938 Dec-19 $721,875 $294,000 $39,900.0 $1,055,775 $8,250,000 $4,790,625 $1,220,513 $14,261,138