Master Budget Data: 1 HNM 2 3 List Your Product Name Here Ice Hockey Skates 39,668 First Name Last Name 5 ENTER YOUR INDIVIDUAL CASE NUMBER HERE 6 (THIS IS LOCATED IN YOUR GRADEBOOK) 7 8 9 Enter Your Name Here 10 11 BASE DATA: 12 Foundation Data for Solving questions 1, 2 and 3: 13 14 Budgeted Direct Materials Cost Per UNIT 15 16 Budgeted Direct Manufacturing Labor Per UNIT 17 18 Budgeted Manufacturing Overhead Per UNIT $ 15.00 $ 10.00 $ 5.00 19 20 21 IN ADDITION TO ABOVE USE THE FOLLOWING: 22 23 24 This is Next Year's Budgeted Data for XYZ 25 $ 20.00 34,834 Units 26 Budgeted Sales Price Per UNIT 27 28 Budgeted Sales - UNITS 29 30 Target Ending Finished Goods Inventory in UNITS 31 32 Target Beginning Finished Goods Inventory in UNITS 22,334 Units 21,684 Units 33 34 Target Ending Direct Materials Inventory in UNITS 28,334 Units 35 36 Target Beginning Direct Materials Inventory in UNITS 26,334 Units 37 38 Note: It takes one unit of direct materials to make one production unit. 39 40 WITH THE ABOVE DATA SOLVE THE FOLLOWING QUESTIONS REGARDING YOUR PRODUCT: 41 42 1) How many units should be produced next year? 43 44 Record your answer, using excel formulas, here 45 46 2) What is the dollar amount budgeted for direct material purchased for next year? 47 48 Record your answer, using excel formulas, here 49 50 3) What is the Budgeted Revenues for next year. 51 52 Record your answer, using excel formulas, here 53 54 55 USE ONLY THE FOLLOWING INFORMATION TO ANSWER THIS QUESTION. DO NOT USE ANY INFORMATION FROM ABOVE 56 57 Gamble Company has the following sales budget for the last six months: 58 January S 49,668 April February S 51,668 May 60 March S 43,668 June 59 S S s 47,168 54,668 42,168 61 62 Historically, the cash collection of sales has been as follows: 63 The % of sales collected in the month of sale is 64 The % of sales collected in the month following the sale is 65 The % of sales collected in the second month following the sale is 66 The % of sales that are uncollectible 67 68 4 What are the budgeted Cash collections for the month of May? 50% 30% 15% 5% 69 70 Record your answer, using excel formulas, here 71 72 Check the order of your formula calculations 73 For example, if cell al is 15 and cell bl is 4 and cell cl is 2. 72 Check the order of your formula calculations 73 For example, if cell al is 15 and cell bl is 4 and cell cl is 2. 74 You want to add cells al and bl and then divide that sum by cell cl 75 If your excel formula is al+b1/cl then your calculation result is 16.333 76 If your excel formula is (altbl)/cl then your calculation result is 9.5 77 The result 9.5 would be the correct
