Answer the four (4) questions regarding your product using excel formulas to arrive at the correct answer

Please Indicate your answer with the cell number

1) How many units should be produced next year?

2) What is the dollar amount budgeted for direct material purchased for next year?

3) What is the Budgeted Revenues for next year.

4) What are the budgeted Cash collections for the month of May?

Master Budget Data: 1 2 3 List Your Product Name Here 4 6,891 Last Name $ 15.00 $ 10.00 5 ENTER YOUR INDIVIDUAL CASE NUMBER HERE 6 (THIS IS LOCATED IN YOUR GRADEBOOK) 7 8 First Name 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 19 20 21 IN ADDITION TO ABOVE USE THE FOLLOWING: 22 23 24 This is Next Year's Budgeted Data for XYZ 25 26 Budgeted Sales Price Per UNIT 27 28 Budgeted Sales - UNITS Sheet1 Sheet2 Sheet3 + $ 5.00 $ 20.00 18,446 Units Ready 100% 24 This is Next Year's Budgeted Data for XYZ 25 26 Budgeted Sales Price Per UNIT $ 20.00 27 28 Budgeted Sales - UNITS 18,446 Units 29 30 Target Ending Finished Goods Inventory in UNITS 5,946 Units 31 32 Target Beginning Finished Goods Inventory in UNITS 5,296 Units 33 34 Target Ending Direct Materials Inventory in UNITS 11,946 Units 35 36 Target Beginning Direct Materials Inventory in UNITS 9,946 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 Sheet1 Sheet2 Sheet3 + Ready 3 100% F G H B D E 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 16,891 April S 59 February S 18,891 May S 60 March S 10,891 June S 61 62 Historically, the cash collection of sales has been as follows: 63 The % of sales collected in the month of sale is 50% 64 The % of sales collected in the month following the sale is 30% 65 The % of sales collected in the second month following the sale is 15% 66 The % of sales that are uncollectible 5% 67 68 4) What are the budgeted Cash collections for the month of May? 14,391 21,891 9,391 69 70 Record your answer, usintg 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. 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 (al+bl)/cl then your calculation result is 9.5 77 The result 9.5 would be the correct answer. 78 79 Case Part 4 Grading: Possible Points 80 3 points for each correct answer 12 81 2 points for use of excel formulas to support answers 9 Sheet1 Sheet2 Sheet3 Earned Points Ready 6 100% D E F G H B 65 The % of sales collected in the second month following the sale is 15% 66 The % of sales that are uncollectible 5% 67 68 4) What are the budgeted Cash collections for the month of May? 69 70 Record your answer, usintg 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. 74 You want to add cells al and bl and then divide that sum by cell cl 75 If your excel formula is al+bl/cl then your calculation result is 16.333 76 If your excel formula is (al+bl)/cl then your calculation result is 9.5 77 The result 9.5 would be the correct answer. 78 79 Case Part 4 Grading: Possible Points 80 3 points for each correct answer 12 81 2 points for use of excel formulas to support answers 8 82 Total points for case part 3 20 83 Earned Points 0 84 85 86 87 88 89 90 91 92 93 94 Sheet1 Sheet2 Sheet3 + Ready 100%