Answered step by step
Verified Expert Solution
Link Copied!

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?

image text in transcribed

image text in transcribed

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Accounting For MBAs

Authors: Peter D. Easton, John J. Wild, Robert F. Halsey, Mary Lea McAnally

6th Edition

161853100X, 978-1618531001

More Books

Students also viewed these Accounting questions

Question

3 When is it a good idea to use the internal supply of labour?

Answered: 1 week ago

Question

5 What are the main aims of talent management?

Answered: 1 week ago