week 2 sign
gysgtclarke
TUTORneedrevisionMasterBudget-Frenchies2.xlsxAssumptions
| Case Data | |||||||||
| Sales Information: | |||||||||
| Estimated % drop: | 20% | Estimated Growth: | 5% | Estimated Additional Holiday Increase: | 15% | ||||
| Sales: | Muffins | Cookies | Bread | Totals | |||||
| Last Year | |||||||||
| Q4 | 45,000 | 65,000 | 85,000 | 195,000 | c | ||||
| Current Year | |||||||||
| Q1 | 36,000 | 52,000 | 68,000 | 156,000 | c | ||||
| Q2 | 37,800 | 54,600 | 71,400 | 163,800 | c | ||||
| Q3 | 39,690 | 57,330 | 74,970 | 171,990 | c | ||||
| Q4 | 47,628 | 68,796 | 89,964 | 206,388 | c | ||||
| Next Year | |||||||||
| Q1 | 50,009 | 72,236 | 94,462 | 216,707 | 43758 | 63206 | 82654 | ||
| Q2 | 52,510 | 75,848 | 99,185 | 227,543 | 45946 | 66367 | 86787 | ||
| Sales Price/Item: | $ | $ | $ | ||||||
| 6.00 | 5.25 | 5.75 | c | ||||||
| Collection Information: | |||||||||
| Cash sales as a percentage of total sales | |||||||||
| Percentage of cash sales | 10% | ||||||||
| percentage of credit sales paid per quarter | |||||||||
| Q1 | 30% | ||||||||
| Q2 | 45% | ||||||||
| Q3 | 25% | c | |||||||
| Q4 | |||||||||
| Manufacturing Information: | |||||||||
| Desired inventory | |||||||||
| Finished goods | Rate/Percentage | ||||||||
| Desired ending inventory (2 days of the next quarter)=2/90 | 0.02 | ||||||||
| Raw materials | |||||||||
| Desired ending inventory as a percentage of next quarter | 15% | ||||||||
| Production time | Mixing time (minutes) | Baking time (minutes) | |||||||
| 12 dozen cookies and 8 dozen muffins | 15 | 30 | |||||||
| 4 dozen loaves | 15 | 30 | |||||||
| Packaging time (minutes) | Packaging time (minutes) per dozen | ||||||||
| 2 dozen of cookies | 15 | 7.5 | |||||||
| 2 dozen of muffins | 15 | 7.5 | |||||||
| 8 dozen loaves of bread | 15 | 1.9 | |||||||
| Labor Costs | Rate per hour | ||||||||
| Mixers | $7.50 | ||||||||
| Bakers | $8.00 | ||||||||
| Packers | $6.50 | ||||||||
&"Arial,Bold"&12Buns BakeryBasic InformationFor Year Ended December 31, Year 2
Sales Budget
| Sales Budget | |||||||||
| Quarter | Total | ||||||||
| Q1 | Q2 | Q3 | Q4 | 2017 | where is 2018? | ||||
| Muffins | |||||||||
| Budgeted Sales | 36,000 | 37,800 | 39,690 | 47,628 | 161,118 | ||||
| Selling price per dozen | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | ||||
| Total Sales Revenue from Muffins | $216,000 | $226,800 | $238,140 | $285,768 | $966,708 | correct | |||
| Cookies | |||||||||
| Budgeted Sales | 52,000 | 54,600 | 57,330 | 68,796 | 232,726 | ||||
| Selling price per dozen | $5.25 | $5.25 | $5.25 | $5.25 | $5.25 | ||||
| Total Sales Revenue for Cookies | $273,000 | $286,650 | $300,983 | $361,179 | $1,221,812 | correct | |||
| Bread | |||||||||
| Budgeted Sales | 68,000 | 71,400 | 74,970 | 89,964 | 304,334 | ||||
| Selling price per loaf | $5.75 | $5.75 | $5.75 | $5.75 | $5.75 | ||||
| Total Sales Revenue for Bread | $391,000 | $410,550 | $431,078 | $517,293 | $1,749,921 | correct | |||
| Total Sales Revenue | $880,000 | $924,000 | $970,200 | $1,164,240 | $3,938,440 | correct | |||
| Schedule of Cash Collections | |||||||||
| Collections in Quarter | Total | ||||||||
| Q1 | Q2 | Q3 | Q4 | 2017 | |||||
| Collections from last year | $651,625 | $250,625 | $902,250 | ||||||
| Q1 | Sales | $325,600 | $356,400 | $198,000 | $880,000 | ||||
| Q2 | Sales | $341,880 | $374,220 | $207,900 | $924,000 | ||||
| Q3 | Sales | $358,974 | $392,931 | $751,905 | |||||
| Q4 | Sales | $430,769 | $430,769 | ||||||
| Total Cash Collections | $977,225 | $948,905 | $931,194 | $1,031,600 | $3,888,924 | ||||
| correct | correct | ||||||||
| 586463 | 225563 | ||||||||
| 325600 | 356400 | ||||||||
| 341880 | |||||||||
| 912063 | 923843 |
&"Arial,Bold"&12
Production Budget
| Production Budget | ||||||||||
| 1st Quarter | 2nd Quarter | 3rd Quarter | 4th Quarter | Please provide 2018 data | ||||||
| Budgeted sales | 156,000 | 163,800 | 171,990 | 206,388 | ||||||
| Add desired ending inventory | 3640 | 3822 | 4586.4 | 4815.72 | ||||||
| Total needs | 159,640 | 167,622 | 176,576 | 211,204 | ||||||
| Less beginning inventroy | 4333 | 3640 | 3822 | 4586.4 | ||||||
| Units to be produced | 155,307 | 163,982 | 172,754 | 206,617 | ||||||
| Budgeted sales | 156000 | |||||||||
| Add desired ending inventory | 3640 | |||||||||
| Total needs | 159640 | |||||||||
| Less beginning inventroy | 3467 | |||||||||
| Units to be produced | 156174 | |||||||||
| Please break out by muffins, cookies, and bread |
Recommendations.
| Recommendations. The management should adopt the below measures to improve production: Consider procurement of equipment with a higher processing speed i.e. the management should seek to minimize the packaging, mixing, and processing time. Undertake to administer a training program to the employees, aimed at improving their efficiency and productivity. Allocate funds for the repair and maintenance of the processing machines. Well maintained machines churn huge volumes of processed goods in a shorter period, compared to poorly maintained equipment. |
| interesting comments |
| need more indepth |
