AutoSave On . : Budgeting Assignment (Final)(1) (1) - Saved Search Laura Maslin LM X File Home Insert Page Layout Formulas Data Review View Help Share Comments & Cut Calibri v 12 ~ A" A " ay Wrap Text X _ AutoSum Custom AY O Paste [ Copy Fill Conditional Format as Cell Insert Delete Format Sort & Find & Analyze Sensitivity Format Painter BIUVA E Merge & Center $ ~ % " Formatting > Table Styles Clear Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity H42 X V 39000 A B C D E F G H K 85 86 87 g. Ending finished goods inventory budget at 30 June 2021 88 h. Cost of goods sold from 1 Jan to 30 June 2021 89 90 XT1 Mfg costs/unit 91 DM 92 DL 93 Variable OH 94 Fixed OH 95 Cost/unit 96 Total ($) 97 Cost El 30 June 2021 98 Cost of goods sold Jan-Jun 2021 99 100 XT2 Mfg costs/unit 101 DM 102 DL 103 Variable OH 104 Fixed OH 105 Cost/unit 106 Total ($) 107 Cost El 30 June 2021 108 Cost of goods sold Jan-Jun 2021 109 110 Data Section Master Budget + Ready + 100% Search O X CO 56 (7 4) @ ENG 7:49 AM 18/05/2021AutoSave . Off) Budgeting Assignment Instructions - Saved to this PC Search Laura Maslin LM X File Home Insert Design Layout References Mailings Review View Help Share Comments & Cut Times New Romar ~ 12 ~ A" A Aa Ap Find AaBbCcI 1 AaBb( AaBbCcI AaBbC AaBbCel AaBbCcI Lol Paste [ Copy S'c Replace BIU ab X X A LA 1 LO_List INL 1 Normal 1 Problem... 1 Problem... 1 No Spac.. Dictate Sensitivity Editor Reuse Format Painter Select v Files Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor Reuse Files | 1 . 2 . 1 . 1 . 1 . X L . 1 . 1 . 2 . 1 . 3 . 1 . 4 . 1 . 5 . 1 . 6 . 1 . 7 . 1 . 8 . 1 . 9 . 1 . 10 . 1 . 11 . 1 . 12 . 1 . 13 . 1 . 14. 1 . 15 . 1 . 16 1 . 17. 1. 18. Swisstech Pty Ltd produces two high tech components for the computers industry, the XT1 and XT2. The components are manufactured in a three-step process and each process is treated as a separate cost centre. In the first process, manufacturing, parts of the components are manufactured. In the second process, assembly, these parts are assembled in each component. In the third process, finishing, the components are tested and packaged. 1. Actual sales (from October to December 2020) and projected sales (from January 2021 onwards) of components in units are the following: Month XT1 XT2 October 5,000 3,000 November 6,000 4,000 December 7,500 5,000 January 8,000 6,000 February 9,000 7,000 March 9,000 7,000 April 8,000 6,000 May 7,500 5.000 June 9,000 7.000 July 9,500 8,000 August 10,000 9,000 2. The current selling price of XT1 is $100 and XT2 is $150. Swisstech is planning to increase the selling price of these components on 1st April to $110 XT1 and $180 XT2. No other changes in the selling prices are planned. All sales are on credit. Pattern of Collection is: 50% are collected in the month following the sale, 30% in the second month following the sale and 20% in the third month following Page 2 of 8 2659 words DX English (United States) " Focus + 100% Search O m CO 58 (7 41) @ ENG 7:51 AM 18/05/2021AutoSave . Off) Budgeting Assignment Instructions - Saved to this PC Search Laura Maslin LM X File Home Insert Design Layout References Mailings Review View Help Share Comments & Cut Times New Romar ~ 12 ~ A" A Aa Ap Find AaBbCcI 1 AaBb( AaBbCcI AaBbC AaBbCel AaBbCcI Lol Paste [ Copy S'c Replace BIU ab X X A LA 1 LO_List INL 1 Normal 1 Problem... 1 Problem... 1 No Spac. Dictate Sensitivity Editor Reuse Format Painter Select v Files Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor Reuse Files | 1 . 2 . 1 . 1 . 1 . ) . 1 . 1 . 1 . 2 . 1 . 3 . 1 . 4 . 1 . 5 . 1 . 6 . 1 . 7 . 1 . 8 . 1 . 9 . 1 . 10 . 1 . 11 . 1 . 12 . 1 . 13 . 1 . 14. 1 . 15 . 1 . 16 1 . 17 . 1 . 18. the sale, 30% in the second month following the sale and 20% in the third month following the sale. 4. Materials are purchased on credit. Swisstech pays 60% of purchases in the month of purchase and the remaining 40% in the following month. Data regarding direct materials used in the components are shown in the following schedules (the cost per unit has not changed in the last 12 months): 2 2104AFE Management Accounting T1 2021 Master Budget Case Study Direct material Cost per Units of direct materials per Units of direct materials per init finished component XT1 finished component XT2 No. 101 $2.40 6 4 No. 102 $3.60 2 Page 2 of 8 2659 words DX English (United States) " Focus + 100% Search O L X w CO 50 (7. () @ ENG 7:51 AM 18/05/2021AutoSave . Off) Budgeting Assignment Instructions - Saved to this PC Search Laura Maslin LM X File Home Insert Design Layout References Mailings Review View Help Share Comments & Cut Times New Romar ~ 12 ~ A" A Aa Ap Find AaBbCcI 1 AaBb( AaBbCcI AaBbC AaBbCel AaBbCcI Lol Paste [ Copy S'c Replace BIUab X X A LA 1 LO_List INL 1 Normal 1 Problem... 1 Problem... 1 No Spac. Dictate Sensitivity Editor Reuse Format Painter Select v Files Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor Reuse Files | 1 . 2 . 1 . 1 . 1 . . 1 . 1 . 1 . 2 . 1 . 3 . 1 . 4 . 1 . 5 . 1 . 6 . 1 . 7 . 1 . 8 . 1 . 9 . 1 . 10 . 1 . 11 . 1 . 12 . 1 . 13 . 1 . 14 . 1 . 15 . 1 . 16 1 . 17 . 1 . 18. 5 . The desired monthly ending inventory for all direct materials is an amount sufficient to produce 60% of the next month's production needs. The inventory levels at 31 December are: Direct material No. 101: 48,000 units Direct material No. 102: 30,960 units 5. The ending balance of work in process each moth is 0. Each month all the units started in work in process are completed and transferred to finished goods inventory. 7. The desired monthly ending finished goods inventory (inventory of completed components) XT1 and XT2 is 80% of the next month's estimated sales. There are 6,400 units of XT1 and 4,800 units of XT2 in inventory on December 31, 2020. Assume the cost per unit of the inventory of XT1 and XT2 at 31 December 2020 is the same unit manufacturing cost for the period January - June 2021. 8. Labour costs are paid as they are incurred. Data regarding direct labour for each component in each department (process) are as follows (the cost per DLH has not changed in the last 12 months): Process Cost per Direct labour hours per Direct labour hours per DLH finished component XT1 finished component XT2 Manufacturing $40 0.400 0.600 Assembly $32 1.000 1.200 Finishing $36 0.125 0.200 7. All overhead costs (except depreciation) are paid as they are incurred. Total fixed overhead Page 3 of 8 2659 words DX English (United States) " Focus + 100% Search O L CO 50 (7. ) @ ENG 7:51 AM 18/05/2021AutoSave . Off) Budgeting Assignment Instructions - Saved to this PC Search Laura Maslin LM X File Home Insert Design Layout References Mailings Review View Help Share Comments & Cut Times New Romar ~ 12 ~ A" A Aa Ap Find AaBbCcI 1 AaBb( AaBbCcI AaBbC AaBbCel AaBbCcI Lol Paste [ Copy S'c Replace 1 LO_List INL 1 Normal 1 Problem... 1 Problem... 1 No Spac... Dictate Sensitivity Editor Reuse Format Painter BIU ab X X A LA Select v Files Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor Reuse Files | 1 . 2 . 1 . 1 . 1 . ) . 1 . 1 . 1 . 2 . 1 . 3 . 1 . 4 . 1 . 5 . 1 . 6 . 1 . 7 . 1 . 8 . 1 . 9 . 1 . 10 . 1 . 11 . 1 . 12 . 1 . 13 . 1 . 14 . 1 . 15 . 1 . 16 1 . 17 . 1 . 18. 7. All overhead costs (except depreciation) are paid as they are incurred. Total fixed overhead costs are estimated to be $15,500 per month (including $7,200 depreciation per month), which is the same as the fixed costs per month in the period October - December 2020. Fixed overhead costs are allocated to products (XTI and XT2) according to the number of units produced. Variable overhead costs are based on direct labour hours at the rate of $3.6/DLH in each process or department. 3 2104AFE Management Accounting T1 2021 Master Budget Case Study 8. Fixed selling and administrative expenses are estimated are $12,000 per month (including $1,200 of depreciation of office equipment). Variable selling and administrative expenses (sales commissions) are 1.8% of total sales dollars of the same month. Selling and administration expenses are paid in the month incurred. 9. Swisstech's management has a policy of maintaining a cash balance of $12,000 at the end of each month to cover unforeseen needs of cash. If this requirement cannot be met, Swisstech has a standby line of credit arranged with its bank to borrow the exact amount needed to achieve the desired cash balance. If Swisstech estimates a cash shortage by the end of the month, sufficient cash is borrowed at the first day of the month to cover the Page 3 of 8 2659 words DX English (United States) " Focus + 100% Search O m L CO 50 (7. () @ ENG 7:51 AM 18/05/2021AutoSave . Off) Budgeting Assignment Instructions - Saved to this PC Search Laura Maslin LM X File Home Insert Design Layout References Mailings Review View Help Share Comments & Cut Times New Romar ~ 12 ~ A" A Aa Ap Find AaBbCcI 1 AaBb( AaBbCcI AaBbC AaBbCel AaBbCcI Lol Paste [ Copy S'c Replace BIUab X X A LA 1 LO_List INL 1 Normal 1 Problem... 1 Problem... 1 No Spac... Dictate Sensitivity Editor Reuse Format Painter Select v Files Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor Reuse Files | 1 . 2 . 1 . 1 . 1 . ) . 1 . 1 . 1 . 2 . 1 . 3 . 1 . 4 . 1 . 5 . 1 . 6 . 1 . 7 . 1 . 8 . 1 . 9 . 1 . 10 . 1 . 11 . 1 . 12 . 1 . 13 . 1 . 14 . 1 . 15 . 1 . 16 1 . 17 . 1 . 18. 9 . Swisstech's management has a policy of maintaining a cash balance of $12,000 at the end of each month to cover unforeseen needs of cash. If this requirement cannot be met, Swisstech has a standby line of credit arranged with its bank to borrow the exact amount needed to achieve the desired cash balance. If Swisstech estimates a cash shortage by the end of the month, sufficient cash is borrowed at the first day of the month to cover the shortage. If Swisstech has an estimated cash balance greater than $12,000 at the end of any month and an outstanding balance in the line of credit at the beginning of the month, then the cash over $12,000 is used to repay the bank at the first day of the month. The interest rate applicable to the line of credit is 9% per annum to be paid the first day of the following month, calculated on the principal amount outstanding at the end of the month. The line of credit used (owed to the bank) at 31 December 2020 is $4,310,000. 10. The tax rate is 30%. Income tax will be paid on July 2021. 11. The following balance sheet at 31 December 2020 has been prepared from the accounting records of Swisstech Ltd: Balance S Swisstech Ltd As at 31 December 2020 Cash $12,000 Accounts payable $152,000 Accounts receivable 2,290,000 Interest payable 32,325 Materials inventory 226,656 Loan payable (line of credit) 4,310,000 Finished goods inventory 1,021,905 Shareholders' equity 2,656,236 Plant and equipment (net) 6,000,000 Retained earnings 2,400,000 Total Assets 9,550,561 Total liabilities and equity 9,550,561 Page 4 of 8 2659 words DX English (United States) " Focus + 100% Search O m L CO 50 (7. () @ ENG 7:52 AM 18/05/2021AutoSave On. F Budgeting Assignment (Final)(1) (1) - Saved Search Laura Maslin LM X File Home Insert Page Layout Formulas Data Review View Help Share Comments & Cut Calibri 12 ~ A" A " Paste [ Copy ay Wrap Text General X _ AutoSum AY O Format Painter BIU HA Merge & Center $ ~ % " Conditional Format as Cell Fill Insert Delete Format Sort & Find & Analyze Clear Sensitivity Formatting * Table Styles Filter ~ Select Clipboard Data Font Alignment Number Styles Cells Editing Analysis Sensitivity M72 X B C D E F G H K Master Budget Section (24 marks according to marking guidelines) 2 Total sales = Sales (units) x Price Per 3 a. Sales budget (dollars) Jan-21 Feb-21 Mar-21 Apr-21 May-21 Jun-21 Total 4 XT 1 Oct-20 800,000 No 900,000 900,000 880,000 825,000 990,000 5 XT2 900,000 1,050,000 1,050,000 1,080,000 900,000 1,260,000 6 Total 9 b. production budget (units) Jan Feb Mar Apr May 10 XT1 Jun Total Jul Aug 11 Budgeted sales (units) 8,000 9,000 9,000 8,000 7,500 12 Plus El 9,000 7,200 9,500 10,000 7,200 6,400 6,000 7,200 13 Units required 7,600 15,200 8,000 16,200 15,400 14,000 14,700 16,600 14 Less O 17,500 6,400 7,200 7,200 6,400 6,000 15 Total production budget XT1 7,200 8,800 7,600 9,000 3,000 8,200 7,600 8,700 9,400 16 Total production XT1 roundown 9,900 17 XT2 61,600 18 Budgeted sales (units) 6,000 7,000 7,000 6,000 5,000 7,000 19 Plus El 8,000 9,000 5,600 5,600 4,800 4,000 5,600 6,400 20 Units required 1,600 7,200 12,600 11,800 10,000 10,600 21 Less Ol 13,400 4,800 15,200 5,600 5.600 4,800 4,000 5,600 22 Total production budget XT2 6,400 6,800 7,200 7,000 5,200 5,200 5,600 7,800 23 Total production XT2 roundown 8,800 24 Total production (XT1 + XT2) 48,400 25 110,000 26 Data Section Master Budget + Select destination and press ENTER or choose Paste + 100% Search O X W ~ CO 50 (7. 41) @ ENG 7:55 AM 18/05/2021AutoSave On. F Budgeting Assignment (Final)(1) (1) - Saved Search Laura Maslin LM X File Home Insert Page Layout Formulas Data Review View Help Share Comments & Cut Calibri v 12 ~ A" A " ay Wrap Text X _ AutoSum General AY O Fill Paste [G Copy BIU HA E Merge & Center $ ~ % " Conditional Format as Cell Insert Delete Format Sort & Find & Analyze Sensitivity Format Painter Formatting * Table Styles Clear Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity M72 X Y fx A B C D E F G H K 26 27 c. DM purchases budget (units and $ Jan Feb Mar Apr May Jun Total Jul 28 Production XT1 8,800 9,000 3,200 7,600 8,700 9,400 61,600 9900 29 Production XT2 6,800 7000 6200 5,200 6,600 7800 48400 8800 30 DM No. 101 31 Production needs for XT1 52,800 54,000 49200 45,600 52200 56,400 369,600 59400 32 Production needs for XT2 27,200 28,000 24,800 20800 26400 31,200 193,600 35,200 33 Production needs for XT1 and XT2 80,000 82,000 74,000 66,400 78,600 87600 563,200 94,600 34 Plus E 49,200 44,400 39840 47160 52,560 56,760 35 Total needed 129,200 126,400 13840 113,560 131,160 144,360 36 Less O 48000 49,200 44,400 39,840 47160 52560 37 Purchases 81,200 77,200 69,440 73720 84000 91,800 38 Cost/unit $2.40 $2.40 $2.40 $2.40 $2.40 $2.40 2.4 39 Cost purchases 194,880 185,280 166,656 176928 201,600 220,320 1,145,664 40 DM No. 102 41 Production needs for XT1 17,600 18000 16,400 15,200 17,400 18800 123,200 19800 42 Production needs for XT2 34,000 35,000 31,000 26,000 33,000 39,000 242,000 44000 43 Production needs for XT1 and XT2 51,600 53,000 47,400 41,200 50,400 57,800 365,200 63,800 44 Plus El 31,800 28,440 24,720 30240 34,680 38280 45 Total needed 83,400 81440 72120 71,440 85,080 96,080 46 Less O 30,960 31,800 28,440 24,720 30,240 34680 47 Purchases 52,440 49640 43680 46,720 54,840 61,400 308,720 48 Cost/unit $3.60 $3.60 3.6 $3.60 $3.60 3.6 49 Cost purchases 188,784 178,704 157,248 168,192 197424 221,040 1111392 50 Total cost purchases 2,257,056 51 Data Section Master Budget + Ready + 100% Search O L X w 60 56 (7. 4) @ ENG 7:55 AM 18/05/2021AutoSave On. F Budgeting Assignment (Final)(1) (1) - Saved Search Laura Maslin LM X File Home Insert Page Layout Formulas Data Review View Help Share Comments & Cut Calibri v 12 ~ A" A " ay Wrap Text General X _ AutoSum AY O Paste [G Copy Fill BIU HA E Merge & Center $ ~ % " Conditional Format as Cell Insert Delete Format Sort & Find & Analyze Sensitivity Format Painter Formatting * Table Styles v Clear Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity M72 X V A B C D E F G H K 52 53 d. DL budget ($) Jan Feb Mar Apr May Jun Total 54 XT1 55 DL Cost Manufacturing 140,800 144,000 131,200 212,600 139,200 150,400 827,200 56 DL Cost Assembly 281,600 288,000 262,400 243,200 278,400 300,800 1,654,400 57 DL Cost Finishing 39,600 40,500 36,900 34,200 39,150 42,300 232,650 58 Total DL Cost XT1 462,000 472,500 430,500 399,000 456,750 193,500 2,714,250 59 XT2 60 DL Cost Manufacturing 163,200 168,000 148,800 124,800 158,400 187,200 950,400 61 DL Cost Assembly 261,120 268,000 238,080 199,680 253,440 299,520 1,520,640 62 DL Cost Finishing 48,960 50,400 44.640 37,440 47,520 56,160 285,120 63 Total DL Cost XT2 473,280 487,200 431,520 361,920 459,360 542,880 64 Total DL Cost 2,756,160 65 66 67 e. Overhead budget Jan Feb Mar Apr May Jun Total 68 Variable OH Cost 69 XT1 48,312 49,410 45,018 41,724 47,763 51,606 70 XT2 48,960 50,400 44.640 37,440 17,520 6,160 71 Total variable OH 97,272 99,810 89,658 79,164 95,283 107,766 568,953 72 Fixed OH Cost 73 Fixed Costs (cash) 3,300 3,300 8,300 8,300 8,300 3,300 74 Fixed Cost (Depreciation) 7,200 7,200 7,200 7,200 7,200 7,200 75 Total Fixed Costs 15,500 15,500 15,500 15,500 15,500 15,500 661,953 76 77 Data Section Master Budget + Ready + 100% Search O L w CO 50 (7 4) @ ENG 7:55 AM 18/05/2021