Required:

1.Using Excel, prepare the following budgets for the twelve-month period from January 2018 to December 2018.The first worksheet should contain your raw data and assumptions and all future worksheets should be linked to this data.Use a different worksheet for each budget. Show all calculations and use Excel functions where possible.While you should use examples in your text as a guide, the key to using spreadsheets is that they are structured so that others can use them and follow the flow of information without difficulty.

a.Monthly Sales Revenue and Cash Collection Budget

b.Production Budget in Units

c.Direct Materials and Cash Purchases Budget

d.Direct Labour Budget

e.Manufacturing Overhead Budget (break into variable and fixed components)

f.Monthly Operating Cost Budget

g.Ending Inventory budget for Finished Goods

h.Cost of Sales Budget

i.Budgeted Income Statement for the year ended 31 December 2018

j.Monthly Cash Budget

Your budget spreadsheet must be submitted in Excel Format (not as an appendix in your WORD document).

2.report for your manager to present to the upcoming Board meeting.The report should include:

an overview of the expected results for the 2018 year;

an analysis of the Contribution Margin for each product and recommendations regarding the products Sun Worship Leisure Wear manufacture and sell;

recommendations regarding the cash position of the business; and

an analysis of current market conditions and future predictions that you believe should be considered when the 2019 budget is prepared.You should use broader industry data and trends from sources such as IBISWorld and the Australian Bureau of Statistics to support your recommendations.

Use visual aids such as tables and graphs to enhance your report presentation.

The report should be submitted as a WORD (not PDF) document.

ACC00146 MANAGEMENT ACCOUNTING MAJOR ASSIGNMENT (Session 2 2017) This assignment is worth 30 marks See MySCU for more details about the time and submission requirements Sun Worship Leisure Wear manufactures swimwear and accessories for men and women. They operate out of rented premises in Burleigh Heads where the factory is split into a manufacturing and storage area and a retail space. The business produces 4 products: Bikinis for women Board shorts for men Beach Towels Beach Bags You, as the management accountant for the firm, have been asked to prepare a range of budgets for the 2018 year. The following information has been gathered: Sales January 2018 February 2018 March 2018 April 2018 May 2018 June 2018 July 2018 August 2018 September 2018 October 2018 November 2018 December 2018 January 2019 February 2019 Bikini 1,500 1,200 1,300 1,000 400 500 400 200 1,300 1,200 1,200 1,500 1,600 1,400 Board short 1,200 1,100 1,160 1,000 600 700 500 400 1,160 1,100 1,100 1,200 1,400 1,200 Towel 400 300 260 140 80 40 40 40 260 300 300 400 440 400 Beach Bag 200 180 160 120 60 40 40 40 160 180 180 200 220 200 Selling Price Bikini $100 Board short $75 Towel $45 Beach Bag $65 Product Bikini Board short Direct Materials & Direct Labour per unit Fabric $18.75 $31.25 Elastic/Trim $13.50 $3.00 Direct Labour 1 hour 0.75 hours (sewing time/machine hours) Other Cost information: Direct Labour Indirect Labour Indirect Materials Utilities Insurance Factory Supervisor's Salary Administration Staff Wages General Office Expenses Rent Repairs and Maintenance Production Related Costs $28/hour $1/unit $2/unit $550/month $2,400/year $54,000/year $5,600/month See below Towel Beach Bag $26.25 $5.25 0.4 hours $25 $1.5 0.6 hours Operating Costs $50/month $6,000/year $2,500/month $1,500/month $1,400/month Inventory: At the end of each month the business plans to have 50% of the following month's sales units in stock as finished goods and 75% of the direct materials required for the next month's production. Cash Collections: 20% of sales are through the retail outlet at the factory. These customers pay for their purchases at the time of sale. The remainder of sales are to businesses that resell Sun Worship Leisure Wear products. Credit is extended to all business customers. It is estimated 60% of these sales are collected in the month of sale and the remaining 40% are collected in the following month. Cash Payments: All purchases, other than the purchase of direct materials, are paid at the time the expense is incurred. It is estimated 30% of accounts payable will be paid in the month the direct materials are purchased and the remaining 70% will be paid in the following month. The interest rate payable on the loan is 6% per annum. Interest is paid monthly. The loan is on an interest only basis and the principle can be repaid at any time. Depreciation of plant and equipment totals $9,000 for the year. Depreciation is recorded monthly. Repairs and maintenance related to manufacturing plant and equipment is estimated to be $2,500 each quarter payable in March, June, September, and December. Variable manufacturing overhead is allocated based on machine hours. Fixed manufacturing overhead is allocated based on units of production. Ignore GST and Income Tax. Round amounts other than unit costs to nearest dollar value. The opening Balance Sheet is provided below: Sun Worship Leisure Wear Balance Sheet As at 31 December 2017 Assets Current Assets Cash Accounts Receivable Finished Goods Inventory Materials Inventory Total Current Assets Non-current Assets Equipment Less: Accumulated Depreciation Total Non-Current Assets 48,250 123,650 99,150 114,375 $385,425 85,000 ( 8,000) $77,000 Total Assets Liabilities & Shareholder Equity Current Liabilities Accounts Payable Total Current Liabilities Long-Term Liabilities Bank Loan Total Long-Term Liabilities $462,425 Total Liabilities Shareholders' Equity Share Capital Retained Earnings Total Shareholder Equity $217,500 Total Liabilities & Shareholders' Equity $462,425 92,500 $92,500 125,000 $125,000 150,000 94,925 $244,925 Required: 1. Using Excel, prepare the following budgets for the twelve-month period from January 2018 to December 2018. The first worksheet should contain your raw data and assumptions and all future worksheets should be linked to this data. Use a different worksheet for each budget. Show all calculations and use Excel functions where possible. While you should use examples in your text as a guide, the key to using spreadsheets is that they are structured so that others can use them and follow the flow of information without difficulty. a. Monthly Sales Revenue and Cash Collection Budget b. c. d. e. f. g. h. i. j. Production Budget in Units Direct Materials and Cash Purchases Budget Direct Labour Budget Manufacturing Overhead Budget (break into variable and fixed components) Monthly Operating Cost Budget Ending Inventory budget for Finished Goods Cost of Sales Budget Budgeted Income Statement for the year ended 31 December 2018 Monthly Cash Budget Your budget spreadsheet must be submitted in Excel Format (not as an appendix in your WORD document). 2. Write a report (Maximum 1,500 words) for your manager to present to the upcoming Board meeting. The report should include: an overview of the expected results for the 2018 year; an analysis of the Contribution Margin for each product and recommendations regarding the products Sun Worship Leisure Wear manufacture and sell; recommendations regarding the cash position of the business; and an analysis of current market conditions and future predictions that you believe should be considered when the 2019 budget is prepared. You should use broader industry data and trends from sources such as IBISWorld and the Australian Bureau of Statistics to support your recommendations. Use visual aids such as tables and graphs to enhance your report presentation. The report should be submitted as a WORD (not PDF) document. Assessment Criteria Poor Spreadsheet layout and presentation (2.5 Marks) Spreadsheet Layout Insufficient data. including Raw Assumptions Spreadsheet has not been & Data, appropriate tabs separated into worksheets. correctly named for each Inappropriate layout. section of the budget and Poor use of Excel tools. use of Excel tools in spreadsheet. 0 Marks Preparation of Budgets (12.5 Marks) For each Budget: Budgets are mostly Appropriate layout inaccurate and incomplete. (monthly) broken into An understanding of the product categories; Clear application of the course Headings; Accurate content in a business calculations for monthly context has not been totals and annual totals. demonstrated. 0-2.5 Marks Report for the Board (15 Marks) The report must be Presentation is professionally presented unprofessional with and cover all issues raised numerous spelling and in the assignment grammatical errors and it instructions. A business does not cover the key report format must be requirements set out in the used along with visual aids assignment instructions. such as tables and graphs. Inadequate analysis. Not presented in the format of a business report. Very limited use of visual aids. 0-3.25 Marks Unsatisfactory Satisfactory Use of Worksheets but layout is difficult to follow. Worksheets have not been named. Limited use of Excel tools. Inconsistencies throughout. 1 Mark Use of appropriately named Worksheets. Reasonable use of Excel tools but application is inconsistent. Reasonable layout. Some Budgets are accurate but there are inconsistencies in the data or basic calculations are incorrect. Assignment instructions have not been followed and key items have been left out. 3-6 Marks Presentation is reasonable but the key issues have not been adequately addressed. Spelling and grammatical errors make the report unpresentable requiring the report to be rewritten before it could be presented. Opinions expressed lack insight, are unsupported (inadequate referencing) or referenced external sources lack relevance. Not all aspects of a business report format have been utilised. Limited use of visual aids. 3.5-7 Marks Good Excellent 1.5 Marks Use of appropriately named Worksheets. Consistent use of Excel tools. Layout appropriate but lacks consistency in some areas. 2 Marks Use of appropriately named Worksheets. Very good use of a range of Excel tools. All worksheets are linked resulting in a consistent flow of data between worksheets. 2.5 Marks Most budgets are accurate but there are inconsistencies in the data or basic calculations. Key items have been included and a reasonable understanding of the application of course content has been demonstrated. 6.5-9 Marks All budgets have been prepared and are mostly accurate - minor errors arising from careless mistakes and lack of attention to detail. A very good understanding of the application of course content has been demonstrated. 9.5-10.5 Marks All budgets have been accurately prepared. An excellent understanding of course content has been demonstrated and executed. Presentation is reasonable and the key issues have been addressed but more insight into the business could be demonstrated. Opinions expressed are unsupported (inadequate referencing) or referenced external sources lack relevance. Some minor spelling and grammatical errors. Most components of a business report format have been incorporated. Reasonable use of visual aids. Good, well written presentation and all issues have been covered. Demonstrates a solid insight into the business and recommendations are appropriate. Relevant external sources (at least 2) have been used to support recommendations and these have been appropriately referenced. All components of a business report format have been incorporated. Good use of visual aids. Excellent presentation with no spelling or grammatical errors. Demonstrates excellent insight into the business operations and external issues that may affect the business. Extensive use of relevant external resources to support recommendations. Appropriate referencing. All components of a business report format have been incorporated. Excellent use of visual aids. 7.5-11 Marks 11.25-12.75Marks 13-15 Marks 11-12.5 Marks Sales Bikini Board short Towel Beach Bag Jan-18 1,500 1,200 400 200 Feb-18 Mar-18 1,200 1,300 1,100 1,160 300 260 180 160 Apr-18 May-18 Jun-18 1,000 400 500 1,000 600 700 140 80 40 120 60 40 Jul-18 400 500 40 40 Aug-18 Sep-18 200 1,300 400 1,160 40 260 40 160 Oct-18 Nov-18 Dec-18 Jan-19 Feb-19 1,200 1,200 1,500 1,600 1,400 1,100 1,100 1,200 1,400 1,200 300 300 400 440 400 180 180 200 220 200 Finished Goods Inv 50% of following month sales units RM Inv 75% DM req for next month Sales Cash sales Credit sales 20% 80% Cash Collection In month of sales following month 60% 40% Accounts Payable Month of purch 30% Selling Price Product Direct Materials & Direct La Fabric Elastic/Tri m Direct Labour (sewing time/mac hine hours) Other Cost information: Direct Labour rate per hour Indirect Labour per unit Indirect Materials per unit Utilities per month Insurance per year Factory Supervisor's Salary year Administration Staff Wages month General Office Expenses pe month Rent per month Repairs and Maintenance Interest 6% p.a. payable monthly Depreciat 9000 per year R&M 2500 each quarter VMOH on the basis of MH FMOH on the basis of units produced following month 70% Sun Worship Leisure Wear Balance Sheet As at 31 December 2017 Assets Current Assets Cash 48,250 Accounts Receivab 123,650 Finished Goods I 99,150 Materials Inventory 114,375 Total Current Assets ### Non-current Assets Equipment 85,000 Less: Accumulate -8,000 Total Non-Current A $77,000 Total Assets ### Liabilities & Shareholder Equity Current Liabilites Accounts Payable 92,500 Total Current Liabilit $92,500 Long-Term Liabilities Bank Loan 125,000 Total Long-Term Liabi ### Total Liabilities ### Shareholders' Equity Share Capital 150,000 Retained Earnings 94,925 Total Shareholder Eq ### Total Liabilities & S ### Assumption No quanttavi details for beginning finished Goods inventory therefore not taken Accounts Receivable and accounts Payable assumed to be fo Bikini Board short $100 Towel Beach Bag $75 Bikini $45 Board short $65 Towel Beach Bag Direct Materials & Direct Labour per unit $18.75 $13.50 $31.25 $3.00 $26.25 $5.25 $25 $1.50 1 0.75 0.4 0.6 Other Cost information: Production Related Costs Direct Labour rate per hour Indirect Labour per unit Indirect Materials per unit Utilities per month Insurance per year Factory Supervisor's Salary per year $28 $1 $2 $550 $2,400 $54,000 Operating Costs $50 $6,000 Administration Staff Wages per month $2,500 General Office Expenses per month $1,500 Rent per month Repairs and Maintenance $5,600 See below payable in march, june, sept and dec. $1,400 ing finished Goods inventory given nts Payable assumed to be for Dec.2017 Monthly Sales Re Jan-18 Unit sales Bikini Board Short Towel Beach Bag Total Sales Revenue Cash sales Credit sales $ Feb-18 Mar-18 Apr-18 150,000 $ 120,000 $ 130,000 $ 100,000 90,000 82,500 87,000 75,000 18,000 13,500 11,700 6,300 13,000 11,700 10,400 7,800 271,000 227,700 239,100 189,100 54,200 45,540 47,820 37,820 216,800 182,160 191,280 151,280 Cash Collection B Cash sales AR Collection In same month In next month Total cash collection Jan-18 54,200 Feb-18 45,540 Mar-18 47,820 Apr-18 37,820 130,080 123,650 307,930 109,296 86,720 241,556 114,768 72,864 235,452 90,768 76,512 205,100 Monthly Sales Revenue May-18 Jun-18 $ 40,000 $ 50,000 $ 45,000 52,500 3,600 1,800 3,900 2,600 92,500 106,900 18,500 21,380 74,000 85,520 Cash Collection Budget May-18 Jun-18 18,500 21,380 44,400 60,512 123,412 51,312 29,600 102,292 Jul-18 Aug-18 Sep-18 Oct-18 N0v-18 Dec-18 40,000 $ 37,500 1,800 2,600 81,900 16,380 65,520 20,000 $ 130,000 $ 120,000 $ 120,000 $ 150,000 30,000 87,000 82,500 82,500 90,000 1,800 11,700 13,500 13,500 18,000 2,600 10,400 11,700 11,700 13,000 54,400 239,100 227,700 227,700 271,000 10,880 47,820 45,540 45,540 54,200 43,520 191,280 182,160 182,160 216,800 Jul-18 16,380 Aug-18 10,880 Sep-18 47,820 39,312 34,208 89,900 26,112 26,208 63,200 114,768 17,408 179,996 Oct-18 N0v-18 45,540 45,540 109,296 76,512 231,348 109,296 72,864 227,700 Dec-18 54,200 130,080 72,864 257,144 Total 2,228,100 Bikini Sales in units Desired Ending Inventory Total required units Beginning Inventory Production in units Board Short Sales in units Desired Ending Inventory Total required units Beginning Inventory Production in units Towel Sales in units Desired Ending Inventory Total required units Beginning Inventory Production in units Beach Bag Sales in units Desired Ending Inventory Total required units Beginning Inventory Production in units Production Budget Apr-18 May-18 Jan-18 Feb-18 Mar-18 1,500 600 2,100 1,200 650 1,850 600 1,250 1,300 500 1,800 650 1,150 1,000 200 1,200 500 700 400 250 650 200 450 1,100 580 1,680 550 1,130 1,160 500 1,660 580 1,080 1,000 300 1,300 500 800 600 350 950 300 650 300 130 430 150 280 260 70 330 130 200 140 40 180 70 110 80 20 100 40 60 180 80 260 90 170 160 60 220 80 140 120 30 150 60 90 60 20 80 30 50 2,100 1,200 550 1,750 1,750 400 150 550 550 200 90 290 290 roduction Budget Jun-18 Jul-18 Aug-18 Sep-18 500 200 700 250 450 400 100 500 200 300 200 650 850 100 750 1,300 600 1,900 650 1,250 1,200 600 1,800 600 1,200 1,200 750 1,950 600 1,350 1,500 800 2,300 750 1,550 1,600 700 2,300 800 1,500 11,700 700 250 950 350 600 500 200 700 250 450 400 580 980 200 780 1,160 550 1,710 580 1,130 1,100 550 1,650 550 1,100 1,100 600 1,700 550 1,150 1,200 700 1,900 600 1,300 1,400 600 2,000 700 1,300 11,220 40 20 60 20 40 40 20 60 20 40 40 130 170 20 150 260 150 410 130 280 300 150 450 150 300 300 200 500 150 350 400 220 620 200 420 440 200 640 220 420 2,560 40 20 60 20 40 40 20 60 20 40 40 80 120 20 100 160 90 250 80 170 180 90 270 90 180 180 100 280 90 190 200 110 310 100 210 220 100 320 110 210 1,560 Oct-18 N0v-18 Dec-18 Jan-19 Total for year Jan-18 Feb-18 Mar-18 Direct Material Budget Apr-18 Bikini Producton Fabric/ unit Total Fabric in dollars Elastc/ Trim per unit Total Elastc/ Trim in dollars Total DM in dollars 2,100 $18.75 $39,375 $13.50 $28,350 $67,725 1,250 1,150 700 $18.75 $18.75 $18.75 $23,438 $21,563 $13,125 $13.50 $13.50 $13.50 $16,875 $15,525 $9,450 $40,313 $37,088 $22,575 Board Short Producton Fabric/ unit Total Fabric in dollars Elastc/ Trim per unit Total Elastc/ Trim in dollars Total DM in dollars 1,750 $31.25 $54,688 $3.00 $5,250 $59,938 1,130 1,080 800 $31.25 $31.25 $31.25 $35,313 $33,750 $25,000 $3.00 $3.00 $3.00 $3,390 $3,240 $2,400 $38,703 $36,990 $27,400 Towel Producton Fabric/ unit Total Fabric in dollars Elastc/ Trim per unit Total Elastc/ Trim in dollars Total DM in dollars 550 $26.25 $14,438 $5.25 $2,888 17325 280 $26.25 $7,350 $5.25 $1,470 8820 200 $26.25 $5,250 $5.25 $1,050 6300 110 $26.25 $2,888 $5.25 $578 3465 Beach Bag Producton Fabric/ unit Total Fabric in dollars Elastc/ Trim per unit Total Elastc/ Trim in dollars Total DM in dollars 290 $25 $7,250 $1.50 $435 $7,685 170 $25 $4,250 $1.50 $255 $4,505 140 $25 $3,500 $1.50 $210 $3,710 90 $25 $2,250 $1.50 $135 $2,385 Total DM for all production Desired ending Inv Total DM required Beg Inv Purchase of Material $152,673 $69,255 $221,928 114,375 $107,553 $92,340 $84,088 $63,066 $41,869 $155,406 $125,956 $69,255 $63,066 $86,151 $62,891 $55,825 $29,993 $85,818 $41,869 $43,949 For Dec In same month In following month Total Payments to AP Jan-18 92,500 $32,266 124,766 Feb-18 Mar-18 Cash Payment Budget Apr-18 $25,845 $18,867 $13,185 $75,287 $60,305 $44,023 101,132 79,173 57,208 Direct Material Budget May-18 Jun-18 450 450 $18.75 $18.75 $8,438 $8,438 $13.50 $13.50 $6,075 $6,075 $14,513 $14,513 Jul-18 Aug-18 Sep-18 Oct-18 N0v-18 Dec-18 Jan-19 300 750 1,250 1,200 1,350 1,550 1,500 $18.75 $18.75 $18.75 $18.75 $18.75 $18.75 $18.75 $5,625 $14,063 $23,438 $22,500 $25,313 $29,063 $28,125 $13.50 $13.50 $13.50 $13.50 $13.50 $13.50 $13.50 $4,050 $10,125 $16,875 $16,200 $18,225 $20,925 $20,250 $9,675 $24,188 $40,313 $38,700 $43,538 $49,988 $48,375 650 600 450 780 1,130 1,100 1,150 1,300 1,300 $31.25 $31.25 $31.25 $31.25 $31.25 $31.25 $31.25 $31.25 $31.25 $20,313 $18,750 $14,063 $24,375 $35,313 $34,375 $35,938 $40,625 $40,625 $3.00 $3.00 $3.00 $3.00 $3.00 $3.00 $3.00 $3.00 $3.00 $1,950 $1,800 $1,350 $2,340 $3,390 $3,300 $3,450 $3,900 $3,900 $22,263 $20,550 $15,413 $26,715 $38,703 $37,675 $39,388 $44,525 $44,525 60 $26.25 $1,575 $5.25 $315 1890 40 $26.25 $1,050 $5.25 $210 1260 40 $26.25 $1,050 $5.25 $210 1260 150 $26.25 $3,938 $5.25 $788 4725 280 $26.25 $7,350 $5.25 $1,470 8820 300 $26.25 $7,875 $5.25 $1,575 9450 350 420 420 $26.25 $26.25 $26.25 $9,188 $11,025 $11,025 $5.25 $5.25 $5.25 $1,838 $2,205 $2,205 11025 13230 13230 50 $25 $1,250 $1.50 $75 $1,325 40 $25 $1,000 $1.50 $60 $1,060 40 $25 $1,000 $1.50 $60 $1,060 100 $25 $2,500 $1.50 $150 $2,650 170 $25 $4,250 $1.50 $255 $4,505 180 $25 $4,500 $1.50 $270 $4,770 190 $25 $4,750 $1.50 $285 $5,035 $39,990 $28,037 $68,027 $29,993 $38,034 $37,383 $20,556 $57,938 $28,037 $29,901 Cash Payment Budget May-18 Jun-18 210 $25 $5,250 $1.50 $315 $5,565 210 $25 $5,250 $1.50 $315 $5,565 $27,408 $58,278 $92,340 $90,595 $98,985 $113,308 $111,695 $43,708 $69,255 $67,946 $74,239 $84,981 $83,771 $71,116 $127,533 $160,286 $164,834 $183,966 $197,079 $20,556 $43,708 $69,255 $67,946 $74,239 $84,981 $50,560 $83,824 $91,031 $96,888 $109,727 $112,098 Jul-18 Aug-18 Sep-18 Oct-18 N0v-18 Dec-18 $11,410 $8,970 $15,168 $25,147 $27,309 $29,066 $32,918 $33,629 $30,764 $26,624 $20,931 $35,392 $58,677 $63,722 $67,821 $76,809 42,174 35,594 36,099 60,539 85,986 92,788 100,739 110,438 Jan-18 Feb-18 Mar-18 Direct Labor Budget Apr-18 May-18 Bikini Producton DLH per unit Total DLH Rate per DLH Direct Labor 2,100 1,250 1,150 700 450 1 1 1 1 1 2100 1250 1150 700 450 $28 $28 $28 $28 $28 $58,800 $35,000 $32,200 $19,600 $12,600 Board Short Producton DLH per unit Total DLH Rate per DLH Direct Labor 1,750 1,130 1,080 800 650 0.75 0.75 0.75 0.75 0.75 1312.5 847.5 810.0 600.0 487.5 $28 $28 $28 $28 $28 $36,750 $23,730 $22,680 $16,800 $13,650 Towel Producton DLH per unit Total DLH Rate per DLH Direct Labor 550 0.4 220 $28 $6,160 280 0.4 112 $28 $3,136 200 0.4 80 $28 $2,240 110 0.4 44 $28 $1,232 60 0.4 24 $28 $672 Beach bag Producton DLH per unit Total DLH Rate per DLH Direct Labor 290 0.6 174 $28 $4,872 170 0.6 102 $28 $2,856 140 0.6 84 $28 $2,352 90 0.6 54 $28 $1,512 50 0.6 30 $28 $840 $106,582 $64,722 $59,472 $39,144 $27,762 Total Direct Labor ct Labor Budget Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 N0v-18 Dec-18 Total 450 1 450 $28 $12,600 300 750 1,250 1,200 1,350 1,550 1 1 1 1 1 1 300 750 1250 1200 1350 1550 $28 $28 $28 $28 $28 $28 $8,400 $21,000 $35,000 $33,600 $37,800 $43,400 12500 600 0.75 450.0 $28 $12,600 450 780 1,130 1,100 1,150 1,300 0.75 0.75 0.75 0.75 0.75 0.75 337.5 585.0 847.5 825.0 862.5 975.0 $28 $28 $28 $28 $28 $28 $9,450 $16,380 $23,730 $23,100 $24,150 $27,300 11920 12500 8940 40 0.4 16 $28 $448 40 0.4 16 $28 $448 150 0.4 60 $28 $1,680 280 0.4 112 $28 $3,136 300 0.4 120 $28 $3,360 350 0.4 140 $28 $3,920 420 0.4 168 $28 $4,704 2780 40 0.6 24 $28 $672 40 0.6 24 $28 $672 100 0.6 60 $28 $1,680 170 0.6 102 $28 $2,856 180 0.6 108 $28 $3,024 190 0.6 114 $28 $3,192 210 0.6 126 $28 $3,528 1670 $26,320 $18,970 $40,740 $64,722 $63,084 $69,062 $78,932 1112 1002 Total Producton Units for all Indirect labor / unit Indirect material/ unit Indirect Material Indirect Labor Utlites Insurance Factory Suervosor's salary Rent Repairs & Maintenance Depreciaton Total manufacturing Overheads Variable MOH Fixed MOH Total manufacturing Overheads Total machine hours Total Units produced Variable MOH per MH Fixed MOH per unit Jan-18 4,690 $1 $2 Feb-18 2,830 $1 $2 $9,380 $4,690 $550 $200 $4,500 $5,600 $5,660 $2,830 $550 $200 $4,500 $5,600 Manufacturing Overheads Budget Mar-18 Apr-18 May-18 2,570 1,700 1,210 $1 $1 $1 $2 $2 $2 750 $750 $25,670 $20,090 $5,140 $3,400 $2,570 $1,700 $550 $550 $200 $200 $4,500 $4,500 $5,600 $5,600 2500 $750 $750 $21,810 $16,700 $750 $15,230 $14,070 $11,600 $25,670 $7,710 $14,100 $21,810 $3,630 $11,600 $15,230 $8,490 $11,600 $20,090 $5,100 $11,600 $16,700 $2,420 $1,210 $550 $200 $4,500 $5,600 ring Overheads Budget Jun-18 1,130 $1 $2 Jul-18 830 $1 $2 Aug-18 1,780 $1 $2 $2,260 $1,660 $1,130 $830 $550 $550 $200 $200 $4,500 $4,500 $5,600 $5,600 2500 $750 $750 $17,490 $14,090 $3,560 $1,780 $550 $200 $4,500 $5,600 $3,390 $14,100 $17,490 $2,490 $11,600 $14,090 Sep-18 2,830 $1 $2 Oct-18 N0v-18 2,780 3,040 $1 $1 $2 $2 $750 $16,940 $5,660 $5,560 $2,830 $2,780 $550 $550 $200 $200 $4,500 $4,500 $5,600 $5,600 2500 $750 $750 $22,590 $19,940 $750 $20,720 $5,340 $11,600 $16,940 $8,490 $14,100 $22,590 $9,120 $11,600 $20,720 $8,340 $11,600 $19,940 $6,080 $3,040 $550 $200 $4,500 $5,600 Dec-18 Total 3,480 $1 $2 $6,960 $3,480 $550 $200 $4,500 $5,600 2500 $750 $24,540 $10,440 $86,610 $14,100 $149,200 $24,540 $235,810 23554 28870 $3.68 $5.17 Utlites Insurance Administraton Staff Wages General Office Expenses Rent Interest Total Operating Costs Jan-18 $50 $500 $2,500 $1,500 $1,400 625 $6,575 Feb-18 $50 $500 $2,500 $1,500 $1,400 $625 $6,575 Mar-18 $50 $500 $2,500 $1,500 $1,400 $625 $6,575 Operating Cost Budget Apr-18 $50 $500 $2,500 $1,500 $1,400 $625 $6,575 Operating Cost Budget May-18 $50 $500 $2,500 $1,500 $1,400 $625 $6,575 Jun-18 $50 $500 $2,500 $1,500 $1,400 $625 $6,575 Jul-18 $50 $500 $2,500 $1,500 $1,400 $625 $6,575 Aug-18 $50 $500 $2,500 $1,500 $1,400 $625 $6,575 Sep-18 $50 $500 $2,500 $1,500 $1,400 $625 $6,575 Oct-18 N0v-18 $50 $50 $500 $500 $2,500 $2,500 $1,500 $1,500 $1,400 $1,400 $625 $625 $6,575 $6,575 Dec-18 Total $50 $600 $500 $6,000 $2,500 $30,000 $1,500 $18,000 $1,400 $16,800 $625 $7,500 $6,575 $78,900 Cost of Proction per unit Bikini Direct material Direct labor Variable MOH Fixed MOH Per unit COP Board short Direct material Direct labor Variable MOH Fixed MOH Per unit COP Towel Direct material Direct labor Variable MOH Fixed MOH Per unit COP Beach bag Direct material Direct labor Variable MOH Fixed MOH Per unit COP $32.25 $28 $3.68 $5.17 $69.10 $34.25 $21 $2.76 $5.17 $63.18 $31.50 $11.20 $1.47 $5.17 $49.34 $26.50 $16.80 $2.21 $5.17 $50.67 Ending Inventory Bikini Board Short Towel Beach bag Finished Goods Inv Raw material Inv Ending Inventory Units 800 700 220 110 COP $69.10 $63.18 $49.34 $50.67 Total $55,276 $44,223 $10,855 $5,574 $115,928 $83,771 $199,699 Cost of Goods Sold Bikini Board Short Towel Beach bag Total COS Units COP COS 11,700 $69.10 $808,412 11,220 $63.18 $708,833 2,560 $49.34 $126,307 1,560 $50.67 $79,052 $1,722,604 Income Statement For the year December 31, 2018 Sales revenue Cost of Goods Sold Gross Profit Expenses Utlites Insurance Administraton Staff Wages General Office Expenses Rent Interest Total Expenses Net Income 2,228,100 1,722,604 505,496 600 6,000 30,000 18,000 16,800 7,500 78,900 426,596 Beginning Cash Balance Cash collecton Total cash available Disbursements Payment for Purchases Direct labor Indirect Material Indirect Labor Utlites Insurance Factory Suervosor's salary Factory Rent Repairs & Maintenance Utlites Insurance Administraton Staff Wages General Office Expenses Rent Interest Total Disbursements Surplus/ (Deficit) Ending cash balance Jan-18 48,250 307930 356,180 Feb-18 93,337 241556 334,893 Mar-18 143,124 235452 378,576 Apr-18 212,297 205100 417,397 Cash Budget May-18 298,520 123412 421,932 124,766 101,132 79,173 57,208 42,174 $106,582 $64,722 $59,472 $39,144 $27,762 $9,380 $5,660 $5,140 $3,400 $2,420 $4,690 $2,830 $2,570 $1,700 $1,210 $550 $550 $550 $550 $550 $200 $200 $200 $200 $200 $4,500 $4,500 $4,500 $4,500 $4,500 $5,600 $5,600 $5,600 $5,600 $5,600 $0 $0 $2,500 $0 $0 $50 $50 $50 $50 $50 $500 $500 $500 $500 $500 $2,500 $2,500 $2,500 $2,500 $2,500 $1,500 $1,500 $1,500 $1,500 $1,500 $1,400 $1,400 $1,400 $1,400 $1,400 $625 $625 $625 $625 $625 262,843 191,769 166,280 118,877 90,991 45,087 49,787 69,172 86,223 32,421 93,337 143,124 212,297 298,520 330,940 Cash Budget Jun-18 330,940 102292 433,232 Jul-18 348,003 89900 437,903 Aug-18 362,919 63200 426,119 Sep-18 302,075 179996 482,071 Oct-18 N0v-18 302,947 352,658 231348 227700 534,295 580,358 Dec-18 384,012 257144 641,156 35,594 $26,320 $2,260 $1,130 $550 $200 $4,500 $5,600 $2,500 $50 $500 $2,500 $1,500 $1,400 $625 85,229 17,063 348,003 36,099 $18,970 $1,660 $830 $550 $200 $4,500 $5,600 $0 $50 $500 $2,500 $1,500 $1,400 $625 74,984 14,916 362,919 60,539 $40,740 $3,560 $1,780 $550 $200 $4,500 $5,600 $0 $50 $500 $2,500 $1,500 $1,400 $625 124,044 -60,844 302,075 85,986 $64,722 $5,660 $2,830 $550 $200 $4,500 $5,600 $2,500 $50 $500 $2,500 $1,500 $1,400 $625 179,123 873 302,947 92,788 $63,084 $5,560 $2,780 $550 $200 $4,500 $5,600 $0 $50 $500 $2,500 $1,500 $1,400 $625 181,637 49,711 352,658 110,438 $78,932 $6,960 $3,480 $550 $200 $4,500 $5,600 $2,500 $50 $500 $2,500 $1,500 $1,400 $625 219,735 37,409 421,420 100,739 $69,062 $6,080 $3,040 $550 $200 $4,500 $5,600 $0 $50 $500 $2,500 $1,500 $1,400 $625 196,346 31,354 384,012