Accounting case 4
Rayan20
MasterBudget-BlankFile.xlsSheet1
| Master Budget Data: | ||||
| List Your Product Name Here | ||||
| ENTER YOUR INDIVIDUAL CASE NUMBER HERE | 6,891 | |||
| (THIS IS LOCATED IN YOUR GRADEBOOK) | ||||
| First Name | Last Name | |||
| Enter Your Name Here | ||||
| BASE DATA: | ||||
| Foundation Data for Solving questions 1, 2 and 3: | ||||
| Budgeted Direct Materials Cost Per UNIT | $ 15.00 | |||
| Budgeted Direct Manufacturing Labor Per UNIT | $ 10.00 | |||
| Budgeted Manufacturing Overhead Per UNIT | $ 5.00 | |||
| IN ADDITION TO ABOVE USE THE FOLLOWING: | ||||
| This is Next Year's Budgeted Data for XYZ | ||||
| Budgeted Sales Price Per UNIT | $ 20.00 | |||
| Budgeted Sales - UNITS | 18,446 | Units | ||
| Target Ending Finished Goods Inventory in UNITS | 5,946 | Units | ||
| Target Beginning Finished Goods Inventory in UNITS | 5,296 | Units | ||
| Target Ending Direct Materials Inventory in UNITS | 11,946 | Units | ||
| Target Beginning Direct Materials Inventory in UNITS | 9,946 | Units | ||
| Note: It takes one unit of direct materials to make one production unit. | ||||
| WITH THE ABOVE DATA SOLVE THE FOLLOWING QUESTIONS REGARDING YOUR PRODUCT: | ||||
| 1) How many units should be produced next year? | ||||
| Record your answer, using excel formulas, here | ||||
| 2) What is the dollar amount budgeted for direct material purchased for next year? | ||||
| Record your answer, using excel formulas, here | ||||
| 3) What is the Budgeted Revenues for next year. | ||||
| Record your answer, using excel formulas, here | ||||
| USE ONLY THE FOLLOWING INFORMATION TO ANSWER THIS QUESTION. DO NOT USE ANY INFORMATION FROM ABOVE. | ||||
| Gamble Company has the following sales budget for the last six months: | ||||
| January | $ 16,891 | April | $ 14,391 | |
| February | $ 18,891 | May | $ 21,891 | |
| March | $ 10,891 | June | $ 9,391 | |
| Historically, the cash collection of sales has been as follows: | ||||
| The % of sales collected in the month of sale is | 50% | |||
| The % of sales collected in the month following the sale is | 30% | |||
| The % of sales collected in the second month following the sale is | 15% | |||
| The % of sales that are uncollectible | 5% | |||
| 4) What are the budgeted Cash collections for the month of May? | ||||
| Record your answer, using excel formulas, here | ||||
| Check the order of your formula calculations | ||||
| For example, if cell a1 is 15 and cell b1 is 4 and cell c1 is 2. | ||||
| You want to add cells a1 and b1 and then divide that sum by cell c1 | ||||
| If your excel formula is a1+b1/c1 then your calculation result is 16.333 | ||||
| If your excel formula is (a1+b1)/c1 then your calculation result is 9.5 | ||||
| The result 9.5 would be the correct answer. | ||||
| Case Part 4 Grading: | Possible Points | Earned Points | ||
| 3 points for each correct answer | 12 | |||
| 2 points for use of excel formulas to support answers | 8 | |||
| Total points for case part 3 | 20 | 0 |
&CACC 311
Interactive Case Part 4 - Master Budget
Blank File
&C&"Arial,Bold"&12Page &P of &N
