Narrative only
Brian_1234
SignatureAssignment.xlsxAssumptions
| Juliette's Pastries | |||||||||||
| Sales Information: | |||||||||||
| Quarterly Growth Assumption: | 5% | ||||||||||
| Extra Holiday Sales: | 15% | ||||||||||
| Muffins | Cookies | Bread | |||||||||
| Prior Year: | |||||||||||
| Q4 | 45,000 | 65,000 | 85,000 | Dozen | |||||||
| Current Year: | |||||||||||
| Q1 | 36,000 | 52,000 | 68,000 | ||||||||
| Q2 | 37,800 | 54,600 | 71,400 | ||||||||
| Q3 | 39,690 | 57,330 | 74,970 | ||||||||
| Q4 | 47,628 | 68,796 | 89,964 | ||||||||
| Next Year: | |||||||||||
| Q1 | 50,009 | 72,236 | 94,462 | ||||||||
| Price per Dozen: | $ 6.00 | $ 5.25 | $ 5.50 | ||||||||
| Collection Information: | |||||||||||
| Historic Cash Collections: | Current collections from last year's credit sales: | ||||||||||
| Cash Sales: | 10% | ||||||||||
| Credit Sales: | 90% | Collected in Current Year | |||||||||
| Collected in current qtr. | 30% | LY Sales | Received | Q1 | Q2 | ||||||
| Collected in next qtr. | 45% | Q3 | $ 802,000 | $ 621,550 | $ 180,450 | ||||||
| Collected in following qtr. | 25% | Q4 | $ 1,002,500 | $ 370,925 | $ 406,013 | $ 225,563 | |||||
| Total: | $ 586,463 | $ 225,563 | |||||||||
| Manufacturing Information: | |||||||||||
| Ending Inventory Goal: | 2% | ||||||||||
Sales
| Sales Budget | ||||||||
| Current Year | Next Year | |||||||
| Q1 | Q2 | Q3 | Q4 | Total | Q1 | |||
| Muffins | ||||||||
| Budgeted Sales | 36,000 | 37800 | 39690 | 47628 | 161,118 | 50009 | ||
| Price per Dozen | $ 6.00 | $ 6.00 | $ 6.00 | $ 6.00 | $ 6.00 | |||
| Total Revenue | $ 216,000 | 226,800.00 | 238,140.00 | 285,768.00 | $ 966,708 | |||
| Cookies | ||||||||
| Budgeted Sales | 52000 | 54600 | 57330 | 68796 | 232726 | 72236 | ||
| Price per Dozen | $ 5.25 | $ 5.25 | $ 5.25 | $ 5.25 | $ 5.25 | |||
| Total Revenue | 273,000.00 | 286,650.00 | 300,982.50 | 361,179.00 | 1,221,811.50 | |||
| Bread | ||||||||
| Budgeted Sales | 68000 | 71400 | 74970 | 89964 | 304334 | 94462 | ||
| Price per Dozen | $ 5.50 | $ 5.50 | $ 5.50 | $ 5.50 | $ 5.50 | |||
| Total Revenue | 374,000.00 | 392,700.00 | 412,335.00 | 494,802.00 | 1,673,837.00 | |||
| Total Sales Revenue | $ 863,000 | 906,150.00 | 951,457.50 | 1,141,749.00 | 3,862,356.50 |
Collections
| Cash Collections Budget | ||||||||
| Collected This Year in Quarter | Acct. Rec. | |||||||
| Q1 | Q2 | Q3 | Q4 | Total | Balance | |||
| Prior year sales | 586462.5 | 225562.5 | 812025 | 0 | ||||
| This year's sales: | ||||||||
| Q1 | 319310 | 349515 | 194175 | 863000 | 0 | |||
| Q2 | 335,275.50 | 366,990.75 | 203,883.75 | 906150 | 0 | |||
| Q3 | 352,039.28 | 385,340.29 | 737379.5625 | 214,077.94 | ||||
| Q4 | 422,447.13 | 422447.13 | 719,301.87 | |||||
| Total Collected | 905772.5 | 910353 | 913205.025 | 1011671.1675 | 3741001.6925 | 933379.8075 |
Production
| Production Budget | ||||||||
| Current Year | Next Year | |||||||
| Q1 | Q2 | Q3 | Q4 | Total | Q1 | |||
| Muffins | ||||||||
| Budgeted Sales | 36000 | 37800 | 39690 | 47628 | 161118 | 50009 | ||
| plus Target Ending Inventory | 840 | 882 | 1058.4 | 1111.32 | 1111.32 | |||
| less Beginning Inventory | 800 | 840 | 882 | 1058.4 | 800 | |||
| Required Production | 36040 | 37842 | 39866.4 | 47680.92 | 161429.32 | |||
| Cookies | ||||||||
| Budgeted Sales | 52000 | 54600 | 57330 | 68796 | 232726 | 72236 | ||
| plus Target Ending Inventory | 1213.3333333333 | 1274 | 1528.8 | 1605.24 | 1605.24 | |||
| less Beginning Inventory | 1155.5555555556 | 1213.3333333333 | 1274 | 1528.8 | 1155.5555555556 | |||
| Required Production | 52057.7777777778 | 54660.6666666667 | 57584.8 | 68872.44 | 233175.684444444 | |||
| Bread | ||||||||
| Budgeted Sales | 68000 | 71400 | 74970 | 89964 | 304334 | 94462 | ||
| plus Target Ending Inventory | 1586.6666666667 | 1666 | 1999.2 | 2099.16 | 2099.16 | |||
| less Beginning Inventory | 1511.1111111111 | 1586.6666666667 | 1666 | 1999.2 | 1511.1111111111 | |||
| Required Production | 68075.5555555556 | 71479.3333333333 | 75303.2 | 90063.96 | 304922.048888889 |
