Answered step by step
Verified Expert Solution
Question
1 Approved Answer
In the problem below, I am having an issue computing the less beginning inventory for year 2 quarters 2, 3, 4, year, and Year 3,
In the problem below, I am having an issue computing the "less beginning inventory for year 2 quarters 2, 3, 4, year, and Year 3, quarter 1. Can you provide me with the formula?
1. Enter all cells that contain question marks in worksheet '1st Part'. 2. Complete the following requirments in worksheet '2nd Part'. The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $6 to $4. The marketing manager would like to use the following projections in the budget: Table Summary: First row of table gives budgeted unit sales for six quarters--four quarters for Year 2 and two quarters for Year 3. The spanner heading Year 2 Quarter appears over columns for four quarters. The spanner heading Year 3 Quarter appears over columns for two quarters. The second row shows the selling price per unit. Budgeted unit sales Selling price per unit Year 3 Quarter First, Reconstruct the Sales Budgets, Schuedule of Cash Collections, Production Budgets and Raw Materials Purchases budgets using new budgeted units sales in '2nd part' worksheet. Then, Answer the following Questions a. What are the total expected cash collections for the year under this revised budget? b. What is the total required production for the year under this revised budget? c. What is the total cost of raw materials to be purchased for the year under this revised budget? \begin{tabular}{|c|c|c|c|c|c|c|c|} \hline \multirow[b]{2}{*}{ Budgeted unit sales } & \multirow{2}{*}{\multicolumn{2}{|c|}{50,000}} & \multirow{2}{*}{80,000} & & & \\ \hline & & & & 40,000 & 60,000 & 70,000 & \\ \hline & \multirow{2}{*}{\multicolumn{2}{|c|}{$6 per unit }} & & & & & \\ \hline - Selling price per unit & & & & & & & \\ \hline - Accounts receivable, beginning balance & \multicolumn{2}{|l|}{$45,000} & & & & & \\ \hline - Sales collected in the quarter sales are made & \multicolumn{2}{|l|}{80%} & & & & & \\ \hline - Sales collected in the quarter after sales are made & \multirow{2}{*}{\multicolumn{2}{|c|}{\begin{tabular}{l} 20% \\ 25% of the budget \end{tabular}}} & & & & & \\ \hline - Desired ending finished goods inventory is & & & ed unit sales & of the next q & quarter & & \\ \hline - Finished goods inventory, beginning & \multicolumn{2}{|c|}{8,000 units } & & & & & \\ \hline - Raw materials required to produce one unit & \multicolumn{2}{|c|}{7 pounds } & & & & & \\ \hline - Desired ending inventory of raw materials is & \multicolumn{2}{|c|}{20% of the next qu } & Jarter's produc & ction needs & & & \\ \hline - Raw materials inventory, beginning & 13,000 & pounds & & & & & \\ \hline - Raw material costs & \multirow{2}{*}{\begin{tabular}{r} $0.70 \\ 65% \end{tabular}} & per pound & & & & & \\ \hline - Raw materials purchases are paid & & \multirow{2}{*}{\multicolumn{3}{|c|}{ in the quarter the purchases are made }} & & & \\ \hline and & \multirow{2}{*}{\begin{tabular}{r} 35% \\ $71,500 \end{tabular}} & & & & & & \\ \hline - Accounts payable for raw materials, beginning balance & & \multicolumn{3}{|c|}{ in the quarter following purchase } & & & \\ \hline \multicolumn{8}{|l|}{ Enter a formula into each of the cells marked with a ? below } \\ \hline & & & & & & & \\ \hline \multirow{2}{*}{ Construct the sales budget } & \multicolumn{3}{|c|}{ Year 2 Quarter } & & \multicolumn{2}{|c|}{ Year 3 Quarter } & \\ \hline & 1 & 2 & 3 & 4 & 1 & 2 & \\ \hline Budgeted unit sales & 30,000 & 50,000 & 80,000 & 40,000 & 60,000 & 70,000 & \\ \hline Selling price per unit & $6 & $6 & $6 & $6 & $6 & $6 & \\ \hline Total sales & $180,000 & $300,000 & $480,000 & $240,000 & $360,000 & $420,000 & \\ \hline \multirow[t]{2}{*}{ Construct the schedule of expected cash collections } & \multicolumn{4}{|c|}{ Year 2 Quarter } & \multirow[b]{2}{*}{ Year } & & \\ \hline & 1 & 2 & 3 & 4 & & & \\ \hline Accounts receivable, beginning balance & 45,000 & & & & $45,000 & & \\ \hline First-quarter sales & 144,000 & $36,000 & & & 180,000 & & \\ \hline Second-quarter sales & & 240,000 & $60,000 & & 300,000 & & \\ \hline Third-quarter sales & & & 384,000 & $96,000 & 480,000 & & \\ \hline Fourth-quarter sales & & & & 192,000 & 192,000 & & \\ \hline Total cash collections & $189,000 & $276,000 & $444,000 & $288,000 & $1,197,000 & & \\ \hline Construct the production budget & & Year 2Q & Quarter & & & Year 3 & 3 Quarter \\ \hline & 1 & 2 & 3 & 4 & Year & r & 2 \\ \hline Budgeted unit sales & 30,000 & 50,000 & 80,000 & 40,000 & 200,000 & 60,000 & 70,000 \\ \hline Add desired finished goods inventory & 12,500 & 20,000 & 10,000 & 15,000 & 15,000 & 17,500 & \\ \hline Total needs & 42,500 & 70,000 & 90,000 & 55,000 & 215,000 & 77,500 & \\ \hline Less beginning inventory & 8,000 & ? & ? & ? & ? & ? & \\ \hline Required production & 34,500 & ? & ? & ? & ? & ? & \\ \hline \end{tabular}
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started