Can somebody fix the #REF's that were due to compatibility error issues?? The assignment is attached along with a semi corrupt solution spread sheet(solved bytutor Prof Ramesh) that I've gotten from this site. Can somebody please "fill in the blanks" for me?

BUS 122A - Term Project Tyler 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, 2015: 190,000 units at $25.00 each* For the year ended December 31, 2016: 200,000 units at $25.00 each For the year ended December 31, 2017: 210,000 units at $25.00 each *Expected sales for the year ended December 31, 2015 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 Nov Dec Percentage of sales 4% each 5% each 8% each 16% 25% 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. BUS 122A - Term Project 6. There are three types of raw material used in the production of Gadgets. Material #1 (Won) is a material purchased in powder form. Each Gadget requires 0.75 kilograms of Won, at a cost of $10.00 per kilogram. The supplier of Won tends to be somewhat erratic so Tyler finds it necessary to maintain an inventory balance equal to 50% of the following month's production needs as a precaution against stock-outs. Material #2 (Too) is purchased from an outside supplier. It is attached during the assembly process. For a small premium, Tyler has made a JIT agreement with the supplier which includes on-time and quality assurances. Each Gadget uses three (3) units of Too, which cost $0.50 each. The supplier of Too is paid in the month the product is supplied. The final component for the toy is a length of rope which is used to pull the Gadget. The rope is supplied by a student entrepreneur, who must be paid in cash. On the first day of every month she delivers exactly the right amount to manufacture the budgeted number of units for that month. It costs $1.60 per meter and Tyler uses one-quarter meter for each Gadget. 7. Accounts payable consists of WON purchases only. Tyler pays for 30% of a month's purchases in the month of purchase, 35% in the following month and the remaining 35% two months after the month of purchase. There is no early payment discount. 8. The manufacturing process for Gadgets is divided into three separate activities; forming, assembly and finishing. a. The forming process is where WON is formed into several shapes that snap together to make the Gadget. b. During the assembly stage, the shapes are fused together. The forming and assembly stages of the manufacturing process are highly automated, so the only employees are three supervisors, who are trained to operate the equipment and make repairs as required. The supervisors work shifts, allowing the plant to operate for longer hours during the busier months. They are also responsible for managing the employees who work in the finishing department c. The finishing stage is where the wheel and the pull rope are attached and the Gadget is prepared for shipping. This is the only part of the manufacturing process that employs direct labour. Most of the staff work on a part-time basis, so their hours can be set based on production requirements. This also eliminates the need for overtime. These employees are paid based on the number of units produced. They receive an average of $18.00 per hour including employee benefits. Each Gadget spends 12 minutes in the finishing department BUS 122A - Term Project 9. Because of the large difference in the manufacturing stages, Tyler uses two separate variable manufacturing overhead rates. The forming and assembly departments use similar equipment and with the company's concentration on a single product, the manufacturing overhead is allocated based on volume (i.e. the units produced). The combined unit variable overhead manufacturing rate for forming and assembly is $3.25, consisting of: Utilities--$1.50; Indirect Materials--$0.50; Plant maintenance-$0.75; environmental fee--$0.35; and Other--$0.15. The best cost driver for the finishing department is considered to be direct labour hours. Here the predetermined variable manufacturing overhead is expected to be $2.05 per hour. 10. Fixed manufacturing overhead costs are not separated between departments. The total costs for the entire year are as follows: Training and development Property and business taxes Supervisor's salary Amortization on equipment Insurance Other $ 43,200 39,000 269,400 178,800 96,000 117,600 $ 744,000 The property and business taxes are paid in one lump sum on June 30 of each year. The expected payment for next year (2016) is $39,600. The annual insurance premium is paid at the beginning of September each year. There should be no change in the premium for 2016, it should be the same as 2015. All other \"cash-related\" fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred. Tyler uses the straight line method of amortization. 11. Selling and administrative expenses are known to be a mixed cost; however, there is a lot of uncertainty about the portion that is fixed. Previous year's experience has provided the following information (rounded): Lowest level of sales: Highest level of sales: 140,000 units 220,000 units Total Operating Expenses: $778,200 Total Operating Expenses: $1,023,000 The annual amount of amortization on office furniture and equipment is only $24,000and this amount is not included in the fixed portion of the selling and administration expenses. Also not included in the above expenses is bad debt expense. Payments for selling and administrative expenses occur in the month in which they are incurred. 12. During the fiscal year ended December 31, 2016, Tyler will be required to make monthly income tax instalment payments of $5,000. Outstanding income taxes from the year ended December 31, 2015 must be paid in April 2016. Income tax expense BUS 122A - Term Project is estimated to be 25% of net income. Income taxes for the year ended December 31, 2016, in excess of instalment payments, will be paid in April, 2017. Notes: Income tax instalments are required of corporations that owed tax to IRS in the prior year (just like how as a \"personal\" tax payer you pay tax with each paycheque, companies are required to remit an amount monthly if they paid tax in the prior year). Instalments are calculated based on the amount owed from the prior year. When paid, companies usually put the amount into a balance sheet account called \"income tax receivable/payable\". Once the amount of income tax owed for the year is calculated (usually after year end) the company is able to calculate any remaining amount owed to or receivable from IRS as this should be the remaining balance in the \"income tax receivable/payable\" account. 13. Tyler is planning to acquire additional manufacturing equipment for $306,000 in February, 2016. They have a special agreement to pay the supplier in three equal instalments: in May, July and September. The manufacturing overhead costs shown above already include the amortization on this equipment. 14. An arrangement has been made with the local bank to have a line of credit at an interest rate of 8% per annum. All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month. Interest must be paid at the beginning of the following month. Interest is calculated on the balance on the 2nd last day of the month, which includes any amounts borrowed but not repaid that month. 15. Tyler Ltd. requires a minimum cash balance on hand at all times of $5,000. 16. Tyler Ltd. has a policy of paying dividends at the end of each calendar quarter. The CEO tells you that the board of directors is planning on continuing their policy of declaring dividends of $50,000 per quarter. 17. A listing of the estimated balances in the company's ledger accounts as of December 31, 2015 is given below: Assets Cash $ 83,365 Accounts receivable 490,438 Inventory-raw materials (WON) 15,000 Inventory-finished goods 31,950 Prepaid Insurance 64,000 Prepaid property and business taxes 19,200 Capital assets (net) 724,000 Total assets $1,427,953 Liabilities and Shareholders' Equity Accounts payable $ 112,481 Income taxes payable 22,500 BUS 122A - Term Project Capital stock Retained Earnings Total liabilities and shareholders' equity 1,000,000 292,971 $1,427,953 Required: 1. Prepare a monthly master budget for Tyler for the year ended December 31, 2016, including the following schedules: Sales Budget & Schedule of Cash Receipts Production Budget Direct Materials Budget & Schedule of Cash Disbursements Direct Labour Budget Manufacturing Overhead Budget Ending Finished Goods Inventory Budget Selling and Administrative Expense Budget Cash Budget 2. Prepare a monthly budgeted income statement for the year ended December 31, 2016. Include a total column that gives the total budgeted income statement for the year ended December 31, 2016. Sheet1 Tyler Ltd No of units in 2015 No of units in 2016 190,000 200,000 1. SALES BUDGET: Nov'15 16% 30,400 $25 $760,000 % of Sales Budgeted unit sales Selling price per unit Total Sales 2. SCHEDULE OF CASH RECEIPTS: Month of Sale Following month Month thereafter Nov'15 Sales Dec'15 Sales Total Cash Collections 55.00% 35.00% 9.50% $ 760,000 $ 1,187,500 3. PRODUCTION BUDGET: Budgeted unit sales Add desired ending inventory Total needs Less beginning inventory Required Production Units 30% 30,400 14,250 44,650 (9,120) 35,530 4. DIRECT MATERIAL BUDGET: Required Production Units Material #1 Qty Reqd Add desired ending inventory Total Qty needed Less beginning inventory Required Purchase of Mat#1 Purchase cost of Mat #1 Material #2 Qty Reqd Add desired ending inventory 35,530 0.75 3 Page 1 26,648 13,369 40,017 (13,324) 26,693 $266,930 Sheet1 Total Qty needed Less beginning inventory Required Purchase of Mat#2 Purchase cost of Mat #2 Material #3 Rope Qty Reqd Add desired ending inventory Total Qty needed Less beginning inventory Required Purchase of Mat#3 Purchase cost of Mat#3 Rope Total Direct Material Cost 0.25 5. DIRECT LABOUR BUDGET: Required Production Units Direct Labour Hrs reqd Direct Labour cost 12 mins = 12/60 = 0.20 hr 6. MANUFACTURING OVERHEAD BUDGET: No of units produced Variable Mfg OH 1 No of Direct Labour Hrs used Var Mfg OH 2 Total Variable Mfg Ohs : Fixed costs : Training & Dev Property & Busienss Tax Supervisor Salary Amortization on Eqpt Insurance Other Total Fixed Mfg Ohs Total Mfg Ohs $ 3.25 $ 2.05 $ 43,200.00 $ 39,000 $ 269,400 $ 178,800 $ 96,000 $ 117,600 $ 744,000 7. ENDING FINISHED GOODS INVENTORY BUDGET: No of units produced Direct Material pu Direct Labour pu Avge Mfg OH pu Total Mfg cost pu No of Unit in Ending Inventory Ending Inventory cost 8. SCHEDULE OF CASH DISBURSEMENTS: Nov'15 Page 2 Sheet1 Additional Storage Account Payable (Nov'15) Account Payable (Dec'15) Accounts payable -In same month Next Month 2nd Month Too (Mat #2) Student (Rope) Total cash payments for Purchases $ 15,000 Materail #1 30% 35% 35% Materail #2 Materail #3 9. Selling and Administrative expense budget: Nov'15 Unit Sales Variable Selling & Admin Exp pu Fixed Selling & Admin Exp Office Furn & Eqpt Amoortization Bad Debt Expense Total Selling and Admin Expenses $ $ $ 3.06 349,800 24,000 0.50% 10. Cash Budget Taylor Ltd CASH BUDGET FOR THE YEAR ENDING Dec 31, 2016 Nov'15 #REF! $0 #REF! Cash balance Add collections from customers Total cash available Less Disbursements Merchandise purchases G&A Expenses Land Salaries Sales Commissions Maint Exp Equipment purchases Taxes Payable Total Disbursements $ 144,000 $ 90,000 20% 3,000 $ $0 #REF! #REF! $152,000 #REF! $96,000 #REF! Excess (deficiency) of receipts over disbursements Financing: Borrowings Repayments Interest Total financing #REF! 1% $0 Cash balance, ending #REF! Page 3 Sheet1 11. Budgeted income statement Taylor Ltd BUDGETED INCOME STATEMENT FOR THE YEAR ENDING Dec 31, 2016 Sales Variable expenses: Cost of goods sold Commissions Contribution Margin Fixed expenses: G&A Expenses Salaries Maint Exp Depreciation Net operating income (EBIT) Interest expense EBT Income Tax Net Income 24000 Unit@$30 #VALUE! 40% Page 4 #VALUE! $40,000 #REF! #REF! #REF! #VALUE! Sheet1 Raw Material : Qty pu Rate Inv Reqt Material #1 0.75 $ 10.00 50% Material #2 3.00 $ 0.50 0 Fina Component 0.25 $ 1.60 0 Direct Labour : DL hr pu DL Hr rate Finish Dept 0.20 $ 18.00 Mfg Var OH : Rate pu Basis Var MOH 1 $ 3.25 Units Var MOH 2 $ 2.05 DL Hrs Dec'15 Jan'16 Feb Mar Apr 25% 4% 4% 4% 5% 47,500 8,000 8,000 8,000 10,000 $25 $25 $25 $25 $25 $1,187,500 $200,000 $200,000 $200,000 $250,000 May Jun Jul 8% 8% 8% 16,000 16,000 16,000 $25 $25 $25 $400,000 $400,000 $400,000 Jan'16 Feb Mar Apr $110,000 $110,000 $110,000 $137,500 $70,000 $70,000 $70,000 $19,000 $19,000 $72,200 $415,625 $112,813 $597,825 $292,813 $199,000 $226,500 May Jun Jul $220,000 $220,000 $220,000 $87,500 $140,000 $140,000 $19,000 $23,750 $38,000 $0 $0 $67,688 $326,500 $451,438 $398,000 47,500 2,400 49,900 (14,250) 35,650 Jan'16 8,000 2,400 10,400 (2,400) 8,000 Feb 8,000 2,400 10,400 (2,400) 8,000 Mar 8,000 3,000 11,000 (2,400) 8,600 Apr 10,000 4,800 14,800 (5,000) 9,800 May 16,000 4,800 20,800 (4,800) 16,000 Jun 16,000 4,800 20,800 (4,800) 16,000 Jul 16,000 3,000 19,000 (5,000) 14,000 35,650 Jan'16 8,000 Feb 8,000 Mar 8,600 Apr 9,800 May 16,000 Jun 16,000 Jul 14,000 26,738 3,000 29,738 (13,369) 16,369 $163,690 6,000 3,000 9,000 (3,000) 6,000 $60,000 6,000 3,225 9,225 (3,000) 6,225 $62,250 6,450 3,675 10,125 (3,225) 6,900 $69,000 7,350 6,000 13,350 (3,675) 9,675 $96,750 12,000 12,000 6,000 5,250 18,000 17,250 (6,000) (6,000) 12,000 11,250 $120,000 $112,500 10,500 3,750 14,250 (5,250) 9,000 $90,000 24,000 - 24,000 - 25,800 - 29,400 - Page 5 48,000 - 48,000 ### 42,000 - Sheet1 Dec'15 24,000 24,000 $12,000 24,000 24,000 $12,000 25,800 25,800 $12,900 29,400 29,400 $14,700 2,000 2,000 2,000 $3,200 $75,200 2,000 2,000 2,000 $3,200 $77,450 2,150 2,450 2,150 2,450 2,150 2,450 $3,440 $3,920 $85,340 $115,370 4,000 4,000 3,500 ### 4,000 4,000 3,500 ### 4,000 4,000 3,500 $6,400 $6,400 $5,600 $150,400 $142,900 $116,600 Jan'16 8,000 1,600 $28,800 Feb 8,000 1,600 $28,800 Mar 8,600 1,720 $30,960 Apr 9,800 1,960 $35,280 May 16,000 3,200 $57,600 Jun 16,000 3,200 $57,600 Jul 14,000 2,800 $50,400 Jan'16 8,000 $26,000 Feb 8,000 $26,000 Mar 8,600 $27,950 Apr 9,800 $31,850 May 16,000 $52,000 Jun 16,000 $52,000 Jul 14,000 $45,500 1,600 $3,280 $29,280 1,600 $3,280 $29,280 1,720 $3,526 $31,476 1,960 $4,018 $35,868 3,200 $6,560 $58,560 3,200 $6,560 $58,560 2,800 $5,740 $51,240 $ 3,600 $ 3,600 $ 3,600 $ 3,600 3,600 $ 3,600 22,450 22,450 22,450 22,450 22,450 $ 3,600 39,600 22,450 9,800 $35,850 $65,130 9,800 $35,850 $65,130 9,800 $35,850 $67,326 9,800 $35,850 $71,718 9,800 9,800 $35,850 $75,450 $94,410 $134,010 9,800 $35,850 $87,090 Jan'16 8,000 $ 9.40 $ 3.60 $ 8.14 $ 21.14 2,400 $50,736 Feb 8,000 $ 9.68 $ 3.60 $ 8.14 $ 21.42 2,400 $51,408 Mar 8,600 $ 9.92 $ 3.60 $ 7.83 $ 21.35 3,000 $64,050 Apr 9,800 $ 11.77 $ 3.60 $ 7.32 $ 22.69 4,800 $108,912 Jan'16 Feb Mar Apr Page 6 48,000 48,000 $24,000 $ $ $ $ $ May 16,000 9.40 3.60 5.90 18.90 4,800 $90,720 May 48,000 ### 48,000 $24,000 42,000 42,000 $21,000 22,450 Jun 16,000 $ 8.93 $ 3.60 $ 8.38 $ 20.91 4,800 $100,368 Jul 14,000 $ 8.33 $ 3.60 $ 6.22 $ 18.15 3,000 $54,450 Jun Jul Sheet1 $93,426 $57,292 $18,000 Dec'15 $57,292 $18,675 $21,000 $12,000 $3,200 $168,718 $12,000 $3,200 $96,967 $20,700 $21,788 $21,000 $12,900 $3,440 $63,488 Jan'16 8,000 $24,480 $29,150 $2,000 $1,000 $56,630 Feb 8,000 $24,480 $29,150 $2,000 $1,000 $56,630 Mar 8,000 $24,480 $29,150 $2,000 $1,000 $56,630 $29,025 $24,150 $21,788 $14,700 $3,920 $74,963 Apr 10,000 $30,600 $29,150 $2,000 $1,250 $63,000 Dec'15 Jan'16 Feb Mar Apr #REF! $36,000 #REF! #REF! $36,000 $0 $597,825 $292,813 $199,000 $226,500 #REF! $633,825 #REF! #REF! $262,500 $0 $168,718 #REF! #REF! $150,000 #REF! #REF! $237,500 $40,000 #REF! #REF! $28,800 $72,000 $90,000 #REF! #REF! #REF! #REF! #VALUE! #REF! $96,967 #REF! $74,963 #REF! #REF! $63,488 #REF! $150,000 #REF! $40,000 #REF! $28,800 $90,000 #REF! #REF! #REF! #REF! #REF! $40,000 #REF! $96,000 #REF! $0 #REF! -$15,000 $0 -$15,000 $0 #REF! #REF! #REF! #REF! $50,000 #REF! $72,000 #REF! $36,000 $33,750 $27,000 $33,863 $42,000 $39,375 $24,150 $33,863 $42,000 $24,000 $24,000 $21,000 $6,400 $6,400 $5,600 $94,013 $109,613 $108,375 May 16,000 $48,960 $29,150 $2,000 $2,000 $82,110 Jun 16,000 $48,960 $29,150 $2,000 $2,000 $82,110 Jul 16,000 $48,960 $29,150 $2,000 $2,000 $82,110 May Jun Jul #REF! #REF! $36,000 $326,500 $451,438 $398,000 #REF! #REF! $434,000 $94,013 $109,613 $108,375 #REF! #REF! #REF! $150,000 #REF! #REF! #REF! $80,000 $80,000 $80,000 #REF! #REF! #REF! $96,000 $28,800 $72,000 $90,000 #REF! #REF! #REF! #REF! #REF! #REF! $0 #REF! #REF! -$15,000 $0 -$15,000 $0 #REF! -$15,000 $0 -$15,000 #REF! #REF! #REF! #REF! #REF! Page 7 Sheet1 $200,000 #VALUE! #VALUE! #REF! #VALUE! $0 #VALUE! #VALUE! #VALUE! Page 8 Sheet1 Units High (e) Low (f) Diff (g=e-f) Var Cost Tot cost Unit (a) (b) 220,000 $1,023,000 140,000 $778,200 80,000 $244,800 $ 3.06 =diff cost/diff Vol High Low Total Cost $1,023,000 $778,200 Var Cost $ 673,200 $ 428,400 Fixed cost $ 349,800 $ 349,800 Aug Sep Oct Nov Dec'16 Total 5% 5% 8% 16% 25% 10,000 10,000 16,000 32,000 50,000 200,000 $25 $25 $25 $25 $25 $250,000 $250,000 $400,000 $800,000 $1,250,000 $5,000,000 Aug Sep $137,500 $137,500 $140,000 $87,500 $38,000 $38,000 $0 $0 $0 $315,500 $263,000 Oct $220,000 $87,500 $23,750 $331,250 Nov $440,000 $140,000 $23,750 $0 Dec'16 $687,500 $280,000 $38,000 $0 $0 $603,750 $1,005,500 $5,011,076 Aug 10,000 3,000 13,000 (3,000) 10,000 Sep 10,000 4,800 14,800 (3,000) 11,800 Oct 16,000 9,600 25,600 (5,000) 20,600 Nov 32,000 15,000 47,000 (9,600) 37,400 Dec'16 Jan'17 50,000 8,400 2,520 52,520 (15,000) 37,520 Aug 10,000 Sep 11,800 Oct 20,600 Nov 37,400 Dec'16 Jan'17 37,520 7,500 8,850 4,425 7,725 11,925 16,575 (3,750) (4,425) 8,175 12,150 $81,750 $121,500 15,450 14,025 29,475 (7,725) 21,750 $217,500 28,050 14,070 42,120 (14,025) 28,095 $280,950 28,140 28,140 (14,070) 14,070 $140,700 61,800 - 112,200 - 112,560 - 30,000 - 35,400 - Page 9 Sheet1 30,000 30,000 $15,000 35,400 35,400 $17,700 61,800 61,800 $30,900 112,200 112,200 $56,100 112,560 112,560 $56,280 2,500 2,950 2,500 2,950 2,500 2,950 $4,000 $4,720 $100,750 $143,920 5,150 5,150 5,150 $8,240 $256,640 9,350 9,350 9,350 $14,960 $352,010 9,380 9,380 9,380 $15,008 $211,988 Aug 10,000 2,000 $36,000 Sep 11,800 2,360 $42,480 Oct 20,600 4,120 $74,160 Nov 37,400 7,480 $134,640 Dec'16 Jan'17 37,520 7,504 $135,072 Aug 10,000 $32,500 Sep 11,800 $38,350 Oct 20,600 $66,950 Nov 37,400 $121,550 Dec'16 Jan'17 37,520 $121,940 2,000 $4,100 $36,600 2,360 $4,838 $43,188 4,120 $8,446 $75,396 7,480 $15,334 $136,884 7,504 $15,383 $137,323 $ 3,600 $ 3,600 22,450 22,450 22,450 22,450 96,000 9,800 9,800 $35,850 $131,850 $72,450 $175,038 9,800 $35,850 $111,246 9,800 $35,850 $172,734 $ 3,600 $ 3,600 Aug 10,000 $ 10.08 $ 3.60 $ 7.25 $ 20.93 3,000 $62,790 Sep 11,800 $ 12.20 $ 3.60 $ 14.83 $ 30.63 4,800 $147,024 Oct 20,600 $ 12.46 $ 3.60 $ 5.40 $ 21.46 9,600 $206,016 Nov 37,400 $ 9.41 $ 3.60 $ 4.62 $ 17.63 15,000 $264,450 Aug Sep Oct Nov $ 3,600 $ 43,200 $ 39,600 22,450 $ 269,400 178,800 $ 178,800 $ 96,000 9,800 $ 117,600 $214,650 $ 744,600 $351,973 $ 1,468,255 Dec'16 Jan'17 37,520 $ 5.65 $ 3.60 $ 9.38 $ 18.63 2,520 $46,948 Dec'16 Page 10 Sheet1 $15,000 $15,000 $24,525 $31,500 $39,375 $15,000 $4,000 $95,400 $36,450 $28,613 $31,500 $17,700 $4,720 $96,563 $65,250 $42,525 $28,613 $30,900 $8,240 $151,388 $84,285 $76,125 $42,525 $56,100 $14,960 $217,935 $42,210 $98,333 $76,125 $56,280 $15,008 $216,668 Aug 10,000 $30,600 $29,150 $2,000 $1,250 $63,000 Sep 10,000 $30,600 $29,150 $2,000 $1,250 $63,000 Oct 16,000 $48,960 $29,150 $2,000 $2,000 $82,110 Nov 32,000 $97,920 $29,150 $2,000 $4,000 $133,070 Dec'16 50,000 $153,000 $29,150 $2,000 $6,250 $190,400 Aug Sep #REF! #REF! $315,500 $263,000 #REF! #REF! Oct $36,000 $331,250 $367,250 Nov Dec'16 #REF! #REF! $603,750 $1,005,500 #REF! #REF! $151,388 #REF! $217,935 #REF! #REF! $80,000 #REF! $72,000 #REF! $160,000 #REF! $96,000 #REF! $96,563 #REF! $150,000 #REF! $50,000 #REF! $28,800 $90,000 #REF! #REF! #REF! $216,668 #REF! $150,000 #REF! $250,000 #REF! $28,800 $90,000 #REF! #REF! #REF! #REF! #REF! #REF! $95,400 #REF! #REF! $50,000 #REF! $96,000 #REF! $0 $0 #REF! -$15,000 $0 -$15,000 #REF! #REF! #REF! #REF! $0 $0 #REF! #REF! #REF! Page 11 Sheet1 Page 12 Sheet1 (2) 1. SALES BUDGET: Jan Budgeted unit sales Selling price per unit Total Sales Feb 6,000 $45 $270,000 8,000 $45 $360,000 Jan $67,500 $315,000 Feb $90,000 $210,000 $121,500 $382,500 $421,500 2. SCHEDULE OF EXPECTED CASH COLLECTIONS: Cash Sales December sales Jan Sales Feb Sales March Sales 25% $ $ $ $ 525,000 202,500 60%/40% 270,000 337,500 Total Cash Collections 3. MERCHANDISE PURCHASES BUDGET: Jan Budgeted unit sales Add desired ending inventory Total needs Less beginning inventory Required purchases Cost of purchases $ 6,000 2,000 8,000 (5,000) 3,000 $90,000 30 per unit Feb 8,000 2,500 10,500 (2,000) 8,500 $255,000 4. BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES: Jan Feb Accounts payable $ 360,000 20%/80% $72,000 $288,000 Jan Purchase $ 90,000 $18,000 Feb Purchase $ 255,000 Mar purchase $ 292,500 Total cash payments $72,000 $306,000 5. Monthly selling and general and administrative expense budgets: Jan Sales $270,000 Sales Commision 20% $54,000 Sales Salaries $ 90,000 $7,500 G&A Salaries $ 144,000 $12,000 Total Selling and G&A Expenses $73,500 6. Cash Budget Barb Weyer Corporation Page 1 Feb $360,000 $72,000 $7,500 $12,000 $91,500 Sheet1 (2) CASH BUDGET FOR THE THREE MONTHS ENDING MAR 31 Jan Feb $36,000 $182,850 $382,500 $421,500 $418,500 $604,350 Cash balance Add collections from customers Total cash available Less Disbursements Merchandise purchases G&A Expenses $ Land Salaries $ Sales Commission Maint Exp $ Equipment purchases Taxes Payable Total Disbursements 144,000 90,000 20% 3,000 Excess (deficiency) of receipts over disbursements Financing: Borrowings Repayments Interest Total financing 1% Cash balance, ending $72,000 $12,000 $306,000 $12,000 $7,500 $54,000 $3,000 $72,000 $7,500 $72,000 $3,000 $96,000 $220,500 $496,500 $198,000 $107,850 -$15,000 -$150 -$15,150 $0 $182,850 $107,850 7. Budgeted income statement for the entire first quarter Barb Weyer Corporation BUDGETED INCOME STATEMENT FOR THE THREE MONTHS ENDED MAR 31 Sales Variable expenses: Cost of goods sold 24000 Unit@$30 Commissions Contribution Margin Fixed expenses: G&A Expenses Salaries Maint Exp Depreciation 21425 Net operating income (EBIT) Interest expense EBT Income Tax 40% Net Income Page 2 $720,000 $216,000 $36,000 $22,500 $9,000 $21,425 Sheet1 (2) Mar 10,000 $45 $450,000 Quarter Apr 24,000 $45 $1,080,000 Mar $112,500 $81,000 $162,000 Quarter $270,000 $525,000 $202,500 $162,000 $355,500 $1,159,500 Mar 10,000 2,250 12,250 (2,500) 9,750 $292,500 Quarter Apr 24,000 6,750 30,750 (9,500) 21,250 $637,500 Mar Quarter Apr $360,000 $90,000 $51,000 $0 $501,000 $72,000 $51,000 $123,000 Mar $450,000 $90,000 $7,500 $12,000 $109,500 9,000 $45 $405,000 $108,000 $337,500 $445,500 9,000 (2,250) May $204,000 $292,500 $496,500 Quarter $1,080,000 $216,000 $22,500 $36,000 $274,500 Page 3 Sheet1 (2) MAR 31 Mar $107,850 $355,500 $463,350 Quarter $326,700 $1,159,500 $1,486,200 $123,000 $12,000 $150,000 $7,500 $90,000 $3,000 $28,800 $90,000 $504,300 $501,000 $36,000 $150,000 $22,500 $216,000 $9,000 $196,800 $90,000 $1,221,300 -$40,950 $264,900 $76,950 $0 $76,950 -$15,000 -$150 $61,800 $76,950 $36,000 $1,080,000 $936,000 $144,000 $88,925 $55,075 $150 $54,925 $21,970 $32,955 Page 4