Study assignment
spyderhp220
FIN486r6_Wk2_Case_Study_Worksheet.xlsxTable1
| TABLE 1 SALES FORECAST (in units) NO Changes need to be made to this table | ||||||||||||
| First Quarter | Second Quarter | Third Quarter | Fourth Quarter | |||||||||
| October 2014 | 150 | January | 0 | April | 500 | July | 1,000 | |||||
| November | 75 | February | 0 | May | 1,000 | August | 500 | |||||
| December | 25 | March | 300 | June | 1,000 | September | 250 | |||||
| Student Template Use Instructions: | ||||||||||||
| 1. Use the table tabs below to reproduce the table data and to provide the written responses as directed in the assignment. | ||||||||||||
| 2. Save this file when completed. Remember to change the file name using the following naming convention: FIN486_Wk2_First Name_Last Name.xlsx | ||||||||||||
| 3. Submit this Microsoft® Excel and your Microsoft® Word document for this assignment. | ||||||||||||
Table 2
| TABLE 2 PRODUCTION SCHEDULE AND INVENTORY (seasonal production) | |||||||||||
| Beginning Inventory | Production This Month | Sales | End Inventory | Inventory ($2,000 per unit) | |||||||
| October 2014 | + | - | = | ||||||||
| November | |||||||||||
| December | |||||||||||
| January | |||||||||||
| February | |||||||||||
| March | |||||||||||
| April | |||||||||||
| May | |||||||||||
| June | |||||||||||
| July | |||||||||||
| August | |||||||||||
| September | |||||||||||
Table 3
| TABLE 3. SALES FORECAST, CASH RECEIPTS AND PAYMENTES, AND CASH BUDGET | ||||||||||||||||||||||||||
| October | November | December | January | February | March | April | May | June | July | August | September | |||||||||||||||
| Sales Forecast | ||||||||||||||||||||||||||
| Sales (units) | ||||||||||||||||||||||||||
| Sales (unit price: $3,000) | ||||||||||||||||||||||||||
| Cash Receipts Schedule | ||||||||||||||||||||||||||
| 50% cash | ||||||||||||||||||||||||||
| 50% from prior month’s sales | ||||||||||||||||||||||||||
| Total Cash Receipts | ||||||||||||||||||||||||||
| (Note: Sept. sales assumed to be $750,000) | ||||||||||||||||||||||||||
| Cash Payments Schedule | ||||||||||||||||||||||||||
| Production in Units | ||||||||||||||||||||||||||
| Production Costs (each = $2,000) | ||||||||||||||||||||||||||
| Overhead | ||||||||||||||||||||||||||
| Dividends and Interest | ||||||||||||||||||||||||||
| Taxes | ||||||||||||||||||||||||||
| Total Cash Payments | ||||||||||||||||||||||||||
| Cash Budget—Required Minimum Balance is $125,000 | ||||||||||||||||||||||||||
| Cash Flow | ||||||||||||||||||||||||||
| Beginning Cash | ||||||||||||||||||||||||||
| Cumulative Cash Balance | ||||||||||||||||||||||||||
| Monthly Loan or (Repayment) | ||||||||||||||||||||||||||
| Cumulative Loan | ||||||||||||||||||||||||||
| Ending Cash | ||||||||||||||||||||||||||
| Balance |
Table 4
| TABLE 4. TOTAL CURRENT ASSETS, FIRST YEAR | |||||||||
| Cash | Accounts* Receivable | Inventory | Total Current Assets | ||||||
| October | + | + | = | ||||||
| November | |||||||||
| December | |||||||||
| January | |||||||||
| February | |||||||||
| March | |||||||||
| April | |||||||||
| May | |||||||||
| June | |||||||||
| July | |||||||||
| August | |||||||||
| September | |||||||||
| *Equals 50 percent of monthly sales |
Table 5
| TABLE 5. CUMULATIVE LOAN BALANCE AND INTEREST EXPENSE (1 % PER Month) | ||||||||||||
| October | November | December | January | February | March | April | May | June | July | August | September | |
| Cumulative Loan Balance | ||||||||||||
| Interest Expense at 12.00% (Prime, 8.0%,+ 4.0%) | ||||||||||||
| Total Interest Expense for the Year: |
1C Response
| Write the required summary information below: |
2A Response
| Write the required summary information below: |
3D Response
| Write the required summary information below |
