|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
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,0
| 75,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,
| 1
| 50,000 |
)
(1,472,125) |
| Manufacturing Supplies |
250,000 |
1
| 60,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
