Accounting Question

I’ve attached The File; which has Three Sections : “Assumptions”, “Sales Budget”, and “Production Budget”.

–

If u can’t open it and|or toggle through the tabs, I attached screenshots of all Three Sections.

–

The First Section : “Assumptions”, has the information, context, and description for The Assignment. Here it is :

“Frenchies is a medium-sized regional bakery that specializes in providing orders to grocery and convenience stores. Because of the popularity of its brand, it has also opened a small café for walk-in business. In order to maintain its high quality standard, Frenchies produces only three products: breakfast muffins, fresh bread, and chocolate chip cookies. Although business has been good in the past few years, a lucky contact with a large chain has recently allowed it to expand its brand out of the local region. Growth has been high since the new contract went into effect. In 2020, Frenchies sold 45,000 one-dozen packages of muffins for $5.50 each, 65,000 one-dozen packages of cookies for $4.75 each, and 85,000 one-dozen loaves of bread for $5.25 each. They will raise prices to  $6.00 for muffins, $5.25 for cookies, and $5.75 for bread for 2021. Sales will drop some in the first quarter of 2021. They always drop off a bit after the holidays anyway, but with the increase in sales prices, a 20 percent drop from the fourth quarter of 2020 results is forecasted. Sales will grow steadily at about 5 percent a quarter with these new prices during 2021. Fourth-quarter sales will be high because of the holidays let’s say 20 percent, instead of 5 percent, from the third to the fourth quarter. The first quarter of the 2022 will continue the 5 percent growth as though the holiday jump didn’t occur. Frenchies gives their business customers a lot of leeway in paying. They collect 30 percent of the credit sales within the current quarter, 45 percent in the following quarter, and 25 percent in the quarter after that. Total sales were $802,000 in third quarter and $1,002,500 in the fourth quarter of 2020.  They will be still collecting quite a bit of that money in 2021 based on our collection breakdown. Frenchies cookies and bread would dry out if they kept them too long so they normally keep only about two days’ worth of inventory on hand to avoid shipping issues or problems with the café based on a 90-day quarter. Frenchies’ raw materials are needed for the next quarter, so they keep 15 percent of the next quarter’s raw materials on hand at all times. Using this information, complete the three tabs in the worksheet. Just fill in the forecasted data in the blue boxes and copy the formulas to complete the sheets.  DO NOT PUT IN HARD NUMBERS, TO GET THE MAXIMUM POINTS, YOU NEED TO COMPLETE THE SHEET WITH FORMULAS ONLY ON THE FIRST PAGE.”

Buns BakeryBasic InformationFor Year Ended December 31, Year 2
Accounting 202 – Managerial Fundamentals
Frenchies is a medium-sized regional bakery that specializes in providing orders to grocery and
convenience stores. Because of the popularity of its brand, it has also opened a small café for walkin business. In order to maintain its high quality standard, Frenchies produces only three products:
breakfast muffins, fresh bread, and chocolate chip cookies. Although business has been good in
the past few years, a lucky contact with a large chain has recently allowed it to expand its brand out
of the local region. Growth has been high since the new contract went into effect. In 2020,
Frenchies sold 45,000 one-dozen packages of muffins for $5.50 each, 65,000 one-dozen packages
of cookies for $4.75 each, and 85,000 one-dozen loaves of bread for $5.25 each. They will raise
prices to $6.00 for muffins, $5.25 for cookies, and $5.75 for bread for 2021. Sales will drop some in
the first quarter of 2021. They always drop off a bit after the holidays anyway, but with the increase
in sales prices, a 20 percent drop from the fourth quarter of 2020 results is forecasted. Sales will
grow steadily at about 5 percent a quarter with these new prices during 2021. Fourth-quarter sales
will be high because of the holidays let’s say 20 percent, instead of 5 percent, from the third to the
fourth quarter. The first quarter of the 2022 will continue the 5 percent growth as though the holiday
jump didn’t occur. Frenchies gives their business customers a lot of leeway in paying. They collect
30 percent of the credit sales within the current quarter, 45 percent in the following quarter, and 25
percent in the quarter after that. Total sales were $802,000 in third quarter and $1,002,500 in the
fourth quarter of 2020. They will be still collecting quite a bit of that money in 2021 based on our
collection breakdown. Frenchies cookies and bread would dry out if they kept them too long so they
normally keep only about two days’ worth of inventory on hand to avoid shipping issues or problems
with the café based on a 90-day quarter. Frenchies’ raw materials are needed for the next quarter,
so they keep 15 percent of the next quarter’s raw materials on hand at all times. Using this
information, complete the three tabs in the worksheet. Just fill in the forecasted data in the blue
boxes and copy the formulas to complete the sheets. DO NOT PUT IN HARD NUMBERS, TO GET
THE MAXIMUM POINTS, YOU NEED TO COMPLETE THE SHEET WITH FORMULAS ONLY ON
THE FIRST PAGE.
Case Data
Estimated % drop:
Sales:
Estimated Growth:
Muffins
Cookies
Bread
= Signifies original
Estimated Additional Holiday Increase:
Sales Numbers Trasposed for Copying
Last Year
Q4
45,000
65,000
85,000
Muffins
Buns BakeryBasic InformationFor Year Ended December 31, Year 2
Current Year
Q1
Q2
Q3
Q4
2021
Q2
Q1
0
0
0
0
Cookies
0
0
Bread
2021
Q2
Q1
Next Year
Q1
Q2
0
$6.00
$5.25
$5.75
0
Q4
0
Q3
0
2021
Q2
Q1
Sales Price/Item:
Q3
0
Q4
0
Q3
0
0
0
Q4
0
0
Buns BakeryBasic InformationFor Year Ended December 31, Year 2
Collection Information:
Credit Policy:
Cash Sales
Credit Sales
Collection in Current Quarter
Collection in Following Quarter
Collection in 3rd Quarter
Prior Year
Current Year
10%
10% Collection Schedule Transposed for Copying
90%
90%
Q1
Q2
Q3
30%
30%
37.0%
40.5%
22.5%
45%
45%
25%
25%
Collections from Last Year’s Sales:
Quarter
Q3
Q4
Sold
Manufacturing Information:
Ending Inventory Goal:
Raw Materials Goal:
Received
$0
$0
Q1
$0
$0
$0
Payments in Current Year
Q2
Q3
$0
$0
$0
$0
$0
$0
Q4
$0
$0
$0
Buns BakeryBasic InformationFor Year Ended December 31, Year 2
Buns BakeryBasic InformationFor Year Ended December 31, Year 2
Buns BakeryBasic InformationFor Year Ended December 31, Year 2
2022
Q1
Q2
0
0
2022
Q1
Q2
0
0
2022
Q1
Q2
0
0
Sales Budget
Quarter
Total
2021
2022
Q1
Q2
Q3
Q4
Muffins
Budgeted Sales
Selling price per dozen
Total Sales Revenue from Muffins
0
$6.00
$0
0
$6.00
$0
0
$6.00
$0
0
$6.00
$0
0
$6.00
$0
0
$6.00
$0
Cookies
Budgeted Sales
Selling price per dozen
Total Sales Revenue for Cookies
0
$5.25
$0
0
$5.25
$0
0
$5.25
$0
0
$5.25
$0
0
$5.25
$0
0
$5.25
$0
Bread
Budgeted Sales
Selling price per loaf
Total Sales Revenue for Bread
0
$5.75
$0
0
$5.75
$0
0
$5.75
$0
0
$5.75
$0
0
$5.75
$0
0
$5.75
$0
$0
$0
$0
$0
$0
$0
Total Sales Revenue
Q1
Schedule of Cash Collections
Quarter
Q1
Collections from last year
Q1
Q2
Q3
Q4
Total Cash Collections
Q2
$0
$0
$0
$0
$0
$0
Q3
$0
$0
$0
$0
$0
$0
Total
2021
Q4
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
Accts. Rec.
12/31/2021
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
2022
Q2
0
$6.00
$0
0
$5.25
$0
0
$5.75
$0
$0
Production Budget
Quarter
Q1
Q2
Q3
Total
2021
Q4
2022
Q1
Q2
Muffins
Budgeted Sales
Desired Ending Inventory
Total Units Needed
Less: Beginning Inventory
Required Muffin Production
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Cookies
Budgeted Sales
Desired Ending Inventory
Total Units Needed
Less: Beginning Inventory
Required Cookie Production
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Bread
Budgeted Sales
Desired Ending Inventory
Total Units Needed
Less: Beginning Inventory
Required Bread Production
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0