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
