Pangea Corporation Pangea manufactures sunglasses with various fashion frame options.Their sunglasses generally sell for $10-$15 per pair. The frames are created in-house (molded), with the lenses and screws purchased from other companies. As the cost accountant for their Delaware plant, you are responsible for budgeting, rate setting and investment analysis. Sometimes the plant management's plans and the requirements from corporate do not align. In "real life", you would discuss any differences with plant management and corporate to reach an agreement. For this project, use the data included below, as well as your best judgement and document any decisions you make as well as any assumptions as well. Week 1 Assignment 1)Create an annual operating budget for Pangea's Delaware plant for calendar year 2018.Use the budget methodologies in the text as well as the assumptions below. Use the table (blue) and data provided below to create your budgets.Document any methodology, calculations or decisions.(You may simply leave formulas in the cells for calculations.) 2) Write a few bullet points or a short summary explaining your process, any difficulties, and how any decisions were reached. Extra Credit 1)Prepare an annual financial (cash) budget for Pangea's Delaware plant for 2018.Use the budget methodologies in the text as well as the assumptions below, Required Submit 1 Excel file for this project.The excel file should include the 2018 Operating budget with all backup calculations. If Extra Credit is submitted, include the 2018 Cash budget in the same Excel file (different tab is fine, if preferred). Summary of the process, difficulties, etc can be either included in a text box or Excel files (like this data). Your file should be formatted for printing, so that if the file is printed, no data is cut off. (Portrait or Landscape are both acceptable.) Data for Operating Budget Data for Cash Budget 2016 Full Year 2017 August YTD 2016 Full Year 2017 August YTD Sales 7,687,500 6,000,000 Beginning Cash Balance 300,000 379,333 Purchased Raw Materials 3,075,000 2,400,000 Minimum Cash Balance desired 353,333 359,375 Available Cash Balance (53,333) 19,958 Expense Category Salaries and Wages 775,000 485,667 Cash receipts/disbursements Fringe Benefits 372,000 242,833 Collections from Customers 7,500,000 4,811,645 Travel & Entertainment 40,000 28,000 Payments for Raw Materials (3,000,000) (2,068,958) Outside Contractors 145,000 100,000 Payments for Operating Expenses (2,150,000) (1,472,125) Manufacturing Supplies 250,000 160,000 Payments for Sales Expenses (450,000) (300,000) Parts and Tools 60,000 40,000 Purchase of new equipment (150,000) - Utilities 300,000 220,000 Net Cash receipts/disbursements 1,750,000 970,562 Depreciation 150,000 110,000 Warehousing Costs 80,000 50,000 Excess/(def) cash before financing 1,696,667 990,520 Total Operating Expenses 2,172,000 1,436,500 Borrowing (Beg of year) - - Sales and Advertising Exp 450,000 300,000 Repayments (End of year) (1,200,000) (800,000) Interest (117,334) (58,667) Income from Operations 1,990,500 1,863,500 Net Cash Inc/(Decr) from Financing (1,317,334) (858,667) Volume 615,000 480,000 Ending Cash Balance 379,333 131,853 Data for Operating Budget Pangea anticipates 5% increase in demand from the Delaware plant for 2018 (vs 2017). Sales are higher in the warmer portion of the year, so April-Sept sales are usually double Oct-Mar sales. Salaries and Wages includes 10 full time employees. Salaries will increase 3% for the same headcount (annual raises). Fringe Benefits are expected to be 50% of Salaries and Wages in 2018. 2016 and 2017 Outside Contractors includes 3 consultants used regularly. The plant wants to hire an additional outside contractor to help maintain the equipment in 2018. Manufacturing Supplies, Utilites and Warehousing Costs are generally volume-related. The plant is working on some efficiency improvements; these would reduce volume-related costs by approximately 3% for the 2nd half of 2018. Sales expenses are expected to be consistent based on volume The plant is also interested in adding a new mixing tank to assist with the extra volume: cost would be $65,000 with estimated life of 10 years. Pangea hopes to finish paying off the loan on the Delaware plant in 2018.As of August 2017, there is $1,200,000 remaining on the 4% loan. Pangea plans to pay off $400,000 in Sep-Dec 2017 and the remaining $800,000 in 2018.Interest is paid monthly.The loan does not compound interest, For simplicity sake, ignore income taxes. Additional Data for CashBudget Collections from customers are one month lagging, on average. Payments for raw materials and operating expenses are also one month lagging, on average.Materials are purchased evenly throughout the year. The plant is also interested in adding a new mixing tank to assist with the extra volume: cost would be $65,000 with estimated life of 10 years. If possible, the plant would prefer to pay cash for the new tank; if not, financing is available at 5% interest over 5 years (not compounding) Minimum Cash Balance Desired is 1 month of Raw Material Purchases plus $100,000. Pangea hopes to finish paying off the loan on the Delaware plant in 2018.As of August 2017, there is $1,200,000 remaining on the 4% loan. Pangea plans to pay off $400,000 in Sep-Dec 2017 and the remaining $800,000 in 2018.Interest is paid monthly.The loan does not compound interest, Employees 2017 Salary Plant Manager 120,000 Engineer 90,000 Engineer 90,000 Scheduler 75,000 Senior Operator 85,000 Senior Operator 8,500 Operator 65,000 Operator 65,000 Operator 65,000 Operator 65,000