| Name: Type your name here | | | | | | | | | | | | Grading Rubric: | Will take off partial pts.
For each error within budget |
| Project 4 worth 15 pts. | | Save you file using your first initial, last name, and name of problem. | | | | | | | | | | #1-5 and #7 | 2 pts. for each budget |
| Due by Tuesday, February 3rd 11:59 pm Central time. Upload to project 4 link. | | | | | | | | | | | | #6 | 1 pt. |
| This project covers material in chapter 9 and I have extensive budget demonstrations in the chapter 9 course documents folder. | | | | | | | | | | | | #8 | 1 pt. |
| I have a template set up on the solution worksheet that you should use to complete the required budgets that are stated on the solution worksheet tab. | | | | | | | | | | | | #9 | 1 pt. |
| You need to use cell references in the development of your budgets. |
| You must use this worksheet to reference the data that is being inputted onto the budgets on the budget worksheet. |
| If you type in any numbers in the solution, I will take off 5 pts., since we use Excel so that we can update budgets or do what if analysis without retyping numbers. |
| You should use this worksheet as your data field and only use cell references and formulas in your budgets. |
| Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete. |
| For #9 you should only have to change one number on this worksheet to answer the question. |
| The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you |
| have used cell references and formulas throughout. |
| Data Scenario: |
| You have just been hired into a management position which requires the application of your budgeting skills. |
| You find out that budgeting has not been a priority of the company and that they have been experiencing cash shortages. |
| You have contacted various areas on the organization and have accumulated the information below to assist you |
| in preparing a comprehensive budget. |
| Following is actual information relates to the operations of a merchandiser named Sled Company, a wholesaler of sleds as of March 31. |
| Cash | | $3,000 |
| Accounts receivable | | 13,300 |
| Inventory | | 22,960 |
| Accounts Payable | | 17,002 |
| Actual and Budgeted sales dollar Data-Sales Budget: |
| March (actual) | | $38,000 |
| April | | $41,000 |
| May | | $42,000 |
| June | | $40,000 |
| July | | $43,000 |
| Sales are following type: | | 65% | Cash sales collected in month of sale |
| | | 35% | Credit sales collected in the following month of sale |
| Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result of March credit sales. |
| Cost of goods sold equals | | 80% | of sales price |
| At the end of each month, inventory is to be on hand(ending inventory) equal to |
| | | 70% | of following month's sales needs, stated at cost. Therefore, inventory on hand on March 31st is April's sales*.8*.7. |
| Inventory purchases are paid | | 47% | in month of purchase |
| | | 53% | in month after purchase |
| The accounts payable at March 31 are a result of March purchases of inventory. I have set up a calculation on the budget worksheet to show you this calculation. |
| Monthly selling, general administrative expenses are as follows and are paid in the month incurred if it is a cash expense. |
| Salaries and wages | | 12% | of sales dollar |
| Rent | | $2,000 | per month |
| Other cash expenses | | 4% | of sales dollar |
| Advertising | | $1,000 | per month |
| Depreciation | | $900 | for April only |
| Depreciation | | $1,000 | per the months of May and June |
| Equipment purchased for cash |
| in May | | $1,000 |
| Required: | Prepare the following budgets on the budget worksheet, which contains a template of budgets you should use. I have adapted the budget model to meet the needs of this company. |
| 1. Prepare a Cash Receipts Budget for the quarter ending June 30th, by month and quarter. | | | | | | | | See page 367 |
| You do not need a formal sales budget since the sales budget is above in the data. |
| 2. Prepare a merchandise purchase budget by month and quarter. A merchandiser purchases in dollars. |
| I have set up a formatted budget for you. You can see how I have made a few minor changes to Schedule A on page 373, which is a production budget which we are not preparing. |
| Make sure you think about the numbers you use in the quarter column. I am specifically talking about how you handle beginning and ending inventory, since for the quarter the beginning inventory should be the beginning inventory for the quarter. |
| 3. Prepare a Selling and Administrative Expense Budget, by month and quarter. | | | | | | | See page 366*** |
| My budget is a little bit different than the one in the text, because the expenses are more detailed in my budget analysis. |
| 4. Prepare a cash disbursements budget, by month and quarter. See page 368. |
| 5. Prepare a cash budget showing the months and quarter. |
| Use the format I have provided on the budget sheet. |
| 6. Based on the quarterly cash budget you prepared, do you have any recommendations |
| on cash management. Discuss the type of business and the cash flow problems a company in this industry might have. |
| Type your answer on the budget worksheet , where I have set out the question. |
| 7. Prepare a budgeted income statement for the quarter ending June 30, 20XX. |
| You do not need to show monthly columns. | | | | I entered Sales for you on the income statement |
| 8. What do you think about the survivability of this business? |
| 9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why? |
| What is the New Net income(Loss) for the quarter? |
| If you have linked everything correctly, you should only have to change the monthly rent |
| on this sheet to determine your answer to the questions asked. Please change the rent back to the original amount of 2,000 before you submit. |
Ash.k
1924625984-1.xls
