| 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) | - 0 |
| 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) | - 0 | - 0 |
| 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 Cash Budget |
| 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 |
esurmen
MAC7200ProjectWeek11.xlsx
