Case: We will be focusing on the master budget for Land O'Lakes butter division. The information below pertains to Land O'Lakes budgeting process for the upcoming year, 2024. Budgeting Info: All of this information is already placed into the INPUTS and Beginning Balance Sheet tabs from which you should work. - Land O'Lakes sells butter in one pound packages which include 4 sticks of butter. Each pound of butter is referred to as a finished good unit. Each package of butter sells for $6.10. - Budgeted sales in units are as follows: Quarter 1.2024=75,000,000 pounds of butter Quarter 22024=62,000,000 pounds of butter Quarter 3.2024=50,000,000 pounds of butter Quarter 42024=106,000,000 pounds of butter Quarter 12025=72,000,0000 pounds of butter Quarter 22025=60,000,000 pounds of butter - Each package of butter requires 0.05 hours ( 3 minutes) of labor time to make and the hourly employees are paid $18.00 per hour. Wages are paid in the quarter incurred. - Variable manufacturing overhead is $0.35 per pound of butter. Fixed manufacturing overhead is $20,000,000 per quarter including $14,000,000 in depreciation that is not a current cash outflow. All cash disbursements for manufacturing overhead are paid in the quarter incurred. - Variable selling and administrative expenses are $0.25 per pound of butter. Fixed selling and administrative expense is $68,000,000 per quarter including $19,000,000 in depreciation that is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the quarter incurred. - Land O'Lakes likes to keep a minimum cash balance on hand of $100,000,000. - Land O'Lakes had $120,000,000 borrowed as of December 31, 2023. The business repays the full $120,000,000 note plus $4,800,000 of interest on January 1, 2024. - Land O'Lakes is planning for a factory equipment purchase in the first quarter of 2024 which totals $80,000,000. The company is also planning for a corporate headquarters renovation throughout the third and fourth quarters of 2024 totaling $140,000,000 each quarter. Depreciation related to this capital spending is already built into the non-cash expenses provided. - The company uses variable costing. - The company shows the following beginning balance sheet as of December 31, 2023: - Land O'Lakes sells primarily to grocery stores. Data shows the company's collection pattern is: 85% of sales are collected in the quarter of sale 15% of sales are collected in the quarter following the sale - Land O'Lakes strives to have ending inventory of 4% of the next quarter's butter sales on hand at the end of each quarter. On December 31,2023 , the company has 3,000,000 pounds (packages) of butter on hand. - One quarter of cream is required to produce one pound of butter. Land O'Lakes desires to have raw materials on hand at the end of each quarter equal to 3% of the following quarter's production needs. On December 31,2023 , the company has 2,234,400 quarts of cream on hand. - Land O'Lakes contracts with suppliers to purchase the cream used in butter production for $2.75 per quart. The company's payment pattern for cream is: 90% of the quarter's purchases are paid for in the quarter of purchase 10% of the quarter's purchases are paid for in the quarter following the purchase - Each package of butter requires 0.05 hours ( 3 minutes) of labor time to make and the hourly employees are paid $18.00 per hour. Wages are Land O'Lakes ACCT 285 - Fall 2023 Excel Case \#3 INPUTS \begin{tabular}{|c|c|c|c|c|c|c|} \hline & \multicolumn{4}{|c|}{2024 (Projected) } & \multicolumn{2}{|c|}{2025 (Projected) } \\ \hline & Quarter 1 & Quarter 2 & Quarter 3 & Quarter 4 & Quarter 1 Q & Quarter 2 \\ \hline & & & & & & \\ \hline \multirow{2}{*}{\begin{tabular}{l} Pounds of Butter \\ Selling Price per Pound ( 4 sticks) \end{tabular}} & 75,000,000 & 62,000,000 & 50,000,000] & 106,000,000 & 72,000,00060 & 60,000,000 \\ \hline & 610 & & & & & \\ \hline \multicolumn{7}{|l|}{ Collections } \\ \hline \multirow{2}{*}{\begin{tabular}{|l|} Quarter of Sale \\ Quarter after Sale \\ \end{tabular}} & 85% & & & & & \\ \hline & 15% & & & & & \\ \hline \multicolumn{7}{|l|}{ Finished Goods Inventory } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Ending FG Inventory Requirement \\ Ending FG Inventory Units (December 31,2023 ) \end{tabular}} & 4% & of next quarte & lers sales & & & \\ \hline & 3,000,000 & units ( 1 poun & ind pkgw/4 st & & & \\ \hline \multicolumn{7}{|l|}{ Raw Materials Inventory } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Raw Materials (cream) Required per Pound of Butter \\ Raw Materials Cost per Quart \end{tabular}} & & quart & & & & \\ \hline & 275 & per quart & & & & \\ \hline \multirow{2}{*}{\begin{tabular}{l} Ending RM Inventory Requirement \\ Ending RM Inventory Units (December 31, 2023) \end{tabular}} & 3% & of next quarte & ier's production & in needs & & \\ \hline & 2.234,400 & quarts & & & & \\ \hline \multirow{2}{*}{\begin{tabular}{|l|} Paid in Quarter of Purchase \\ Paid in Following Quarter \\ \end{tabular}} & 90% & & & & & \\ \hline & 10% & & & & & \\ \hline \multicolumn{7}{|l|}{ Direct Labor } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Labor Required per Pound of Butter \\ Labor Cost per Hour \end{tabular}} & 0.05 & hours & & & & \\ \hline & 18.00 & per hour: & & & & \\ \hline \multicolumn{7}{|l|}{ Manufacturing Overhead } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Variable \\ Fixed \\ \end{tabular}} & 0.35 & per pound of & fbutter & & & \\ \hline & \$ 20,000,000 & per quarter & & & & \\ \hline Noncash Fixed (included in above) & $14,000,000 & per quarter & & & & \\ \hline \multicolumn{7}{|l|}{ Selling \& Administrative Expenses } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Variable \\ Fixed \end{tabular}} & 0.25 & per pound of & Tbutter & & & \\ \hline & 5.68,000,000 & per quarter & & & & \\ \hline Noncash Fixed (included in above) & $19,000,000 & per quarter & & & & \\ \hline \multicolumn{7}{|l|}{ Cash } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Minimum Cash Balance \\ Note Payable as of December 31,2023 \end{tabular}} & $100,000,000 & & & & E & \\ \hline & $120,000,000 & to be paid Jan & anuary 1,2024 & & & \\ \hline interest Payable as of December 31,2023 & $4,800,000 & to be paid Jar & anuary 1,2024 & & & \\ \hline \multicolumn{7}{|l|}{\begin{tabular}{l} Other \\ Factory Equipmont Putchase (Capatalized) \end{tabular}} \\ \hline Factory Equipment Purchase (Capitalized) & $8.0000,000 & 1stouarter & & & & \\ \hline Corporate Headquarters Renovation (capitalized) & $140,000.000 & 3rds ath Que & arter & & & \\ \hline \end{tabular} inputs tab Click 'OK' and this function will work through the math within your file to figure out what the sales price would need to be to reach this goal. If you have formula driven everything correctly, you should be able to click back to the inputs tab and see the new sales price listed as \$7.05. Manually change the butter sales price back to $6.10 on the inputs tab and then click over to the Goal Seek tab and manually enter your answer in the cell provided. This is the only cell in the project in which it is allowed that you manually type a number into the cell. 3. Compare your file against the project rubric below. You should be able to figure out your grade before you even submit your project by comparing yourself against the rubric. 4. Upload your completed Excel file to Canvas via the Excel case \#3 link to submit. entire budget. This becomes even more useful as your spreadsheets become more complex. 2. Complete the Goal Seek tab. Land O'Lakes would like to build a new production facility in 2025 which would require $300,000,000 of capital. However, interest rates are currently high, and they are looking to pay for this project without having to borrow the capital necessary. You have been asked to run an analysis that shows how much Land O'Lakes would need to increase their sales price for each package of butter to accommodate the cash necessary for this project. Remember: Land O'Lakes must maintain a minimum cash balance of $100,000,000. Use Goal Seek to find what the selling price of each package of butter would need to be to end 2024 with an ending cash balance of $400,000,000 ( $300,000,000 that will be used for the new production facility + the $100,000,000 minimum ending cash balance). HINT: To find goal seek... - USING MS OFFICE: click on the Data tab, and within the Forecast section click on What-If and find Goal Seek When you type your info into the template that pops up, you want to do the following: Set cell: cllck on the ending cash balance cell for 2024 in the cash budget quirements: 1. Prepare a master budget for the year ended December 31, 2024 that includes the following: Projected Sales Budget, Expected Cash Collections Budget, Production Budget, Variable Product Cost per Unit Calculation, Raw Materials Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling \& Administrative Budget, Cash Budget, Budgeted Income Statement, \& Budgeted Balance Sheet. Each budget should be completed as it appears in your template. There is a template provided to you in the assignments link in the Excel Project folder with an INPUTS tab you MUST use. Complete the yellow shaded areas of the template only. All of your spreadsheets must be formula driven! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values. You should NOT type a number the entire time you are working on this project. Any typed numbers will cost you 2 points per instance. There is a purpose to this requirement that we hope you understand. The beauty (usefulness) of linking your spreadsheets to an inputs page is when something changes, such as the cost per unit of raw materials, you don't have to go through the entire budget to fix every cell that used that value. Instead, when you change it on the inputs tab, it will automatically change every cell which used that value within your 1 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{\begin{tabular}{l} Land O'Lakes \\ Balance Sheet as of December 31, 2023 \end{tabular}} \\ \hline \multicolumn{3}{|l|}{ Assets } & \multicolumn{3}{|c|}{ Liabilities and Equities } \\ \hline Cash & $ & 318,000,000 & Accounts Payable & $ & 31,000,000 \\ \hline Accounts Receivable & $ & 79,000,000 & Notes Payable & $ & 120,000,000 \\ \hline Raw Materials Inventory & $ & 6,144,600 & Interest Payable & s & 4,800,000 \\ \hline Finished Goods Inventory & $ & 12,000,000 & Common Stock & $ & 800,000,000 \\ \hline \multirow{2}{*}{\begin{tabular}{l} Property, Plant and Equipment, net \\ Total Assets \end{tabular}} & & 1,300,000,000 & Retained Earnings & $ & 759,344,600 \\ \hline & & 1,715,144,600 & Total Liabilities and Equities & & ,715,144,600 \\ \hline \end{tabular} Case: We will be focusing on the master budget for Land O'Lakes butter division. The information below pertains to Land O'Lakes budgeting process for the upcoming year, 2024. Budgeting Info: All of this information is already placed into the INPUTS and Beginning Balance Sheet tabs from which you should work. - Land O'Lakes sells butter in one pound packages which include 4 sticks of butter. Each pound of butter is referred to as a finished good unit. Each package of butter sells for $6.10. - Budgeted sales in units are as follows: Quarter 1.2024=75,000,000 pounds of butter Quarter 22024=62,000,000 pounds of butter Quarter 3.2024=50,000,000 pounds of butter Quarter 42024=106,000,000 pounds of butter Quarter 12025=72,000,0000 pounds of butter Quarter 22025=60,000,000 pounds of butter - Each package of butter requires 0.05 hours ( 3 minutes) of labor time to make and the hourly employees are paid $18.00 per hour. Wages are paid in the quarter incurred. - Variable manufacturing overhead is $0.35 per pound of butter. Fixed manufacturing overhead is $20,000,000 per quarter including $14,000,000 in depreciation that is not a current cash outflow. All cash disbursements for manufacturing overhead are paid in the quarter incurred. - Variable selling and administrative expenses are $0.25 per pound of butter. Fixed selling and administrative expense is $68,000,000 per quarter including $19,000,000 in depreciation that is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the quarter incurred. - Land O'Lakes likes to keep a minimum cash balance on hand of $100,000,000. - Land O'Lakes had $120,000,000 borrowed as of December 31, 2023. The business repays the full $120,000,000 note plus $4,800,000 of interest on January 1, 2024. - Land O'Lakes is planning for a factory equipment purchase in the first quarter of 2024 which totals $80,000,000. The company is also planning for a corporate headquarters renovation throughout the third and fourth quarters of 2024 totaling $140,000,000 each quarter. Depreciation related to this capital spending is already built into the non-cash expenses provided. - The company uses variable costing. - The company shows the following beginning balance sheet as of December 31, 2023: - Land O'Lakes sells primarily to grocery stores. Data shows the company's collection pattern is: 85% of sales are collected in the quarter of sale 15% of sales are collected in the quarter following the sale - Land O'Lakes strives to have ending inventory of 4% of the next quarter's butter sales on hand at the end of each quarter. On December 31,2023 , the company has 3,000,000 pounds (packages) of butter on hand. - One quarter of cream is required to produce one pound of butter. Land O'Lakes desires to have raw materials on hand at the end of each quarter equal to 3% of the following quarter's production needs. On December 31,2023 , the company has 2,234,400 quarts of cream on hand. - Land O'Lakes contracts with suppliers to purchase the cream used in butter production for $2.75 per quart. The company's payment pattern for cream is: 90% of the quarter's purchases are paid for in the quarter of purchase 10% of the quarter's purchases are paid for in the quarter following the purchase - Each package of butter requires 0.05 hours ( 3 minutes) of labor time to make and the hourly employees are paid $18.00 per hour. Wages are Land O'Lakes ACCT 285 - Fall 2023 Excel Case \#3 INPUTS \begin{tabular}{|c|c|c|c|c|c|c|} \hline & \multicolumn{4}{|c|}{2024 (Projected) } & \multicolumn{2}{|c|}{2025 (Projected) } \\ \hline & Quarter 1 & Quarter 2 & Quarter 3 & Quarter 4 & Quarter 1 Q & Quarter 2 \\ \hline & & & & & & \\ \hline \multirow{2}{*}{\begin{tabular}{l} Pounds of Butter \\ Selling Price per Pound ( 4 sticks) \end{tabular}} & 75,000,000 & 62,000,000 & 50,000,000] & 106,000,000 & 72,000,00060 & 60,000,000 \\ \hline & 610 & & & & & \\ \hline \multicolumn{7}{|l|}{ Collections } \\ \hline \multirow{2}{*}{\begin{tabular}{|l|} Quarter of Sale \\ Quarter after Sale \\ \end{tabular}} & 85% & & & & & \\ \hline & 15% & & & & & \\ \hline \multicolumn{7}{|l|}{ Finished Goods Inventory } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Ending FG Inventory Requirement \\ Ending FG Inventory Units (December 31,2023 ) \end{tabular}} & 4% & of next quarte & lers sales & & & \\ \hline & 3,000,000 & units ( 1 poun & ind pkgw/4 st & & & \\ \hline \multicolumn{7}{|l|}{ Raw Materials Inventory } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Raw Materials (cream) Required per Pound of Butter \\ Raw Materials Cost per Quart \end{tabular}} & & quart & & & & \\ \hline & 275 & per quart & & & & \\ \hline \multirow{2}{*}{\begin{tabular}{l} Ending RM Inventory Requirement \\ Ending RM Inventory Units (December 31, 2023) \end{tabular}} & 3% & of next quarte & ier's production & in needs & & \\ \hline & 2.234,400 & quarts & & & & \\ \hline \multirow{2}{*}{\begin{tabular}{|l|} Paid in Quarter of Purchase \\ Paid in Following Quarter \\ \end{tabular}} & 90% & & & & & \\ \hline & 10% & & & & & \\ \hline \multicolumn{7}{|l|}{ Direct Labor } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Labor Required per Pound of Butter \\ Labor Cost per Hour \end{tabular}} & 0.05 & hours & & & & \\ \hline & 18.00 & per hour: & & & & \\ \hline \multicolumn{7}{|l|}{ Manufacturing Overhead } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Variable \\ Fixed \\ \end{tabular}} & 0.35 & per pound of & fbutter & & & \\ \hline & \$ 20,000,000 & per quarter & & & & \\ \hline Noncash Fixed (included in above) & $14,000,000 & per quarter & & & & \\ \hline \multicolumn{7}{|l|}{ Selling \& Administrative Expenses } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Variable \\ Fixed \end{tabular}} & 0.25 & per pound of & Tbutter & & & \\ \hline & 5.68,000,000 & per quarter & & & & \\ \hline Noncash Fixed (included in above) & $19,000,000 & per quarter & & & & \\ \hline \multicolumn{7}{|l|}{ Cash } \\ \hline \multirow{2}{*}{\begin{tabular}{l} Minimum Cash Balance \\ Note Payable as of December 31,2023 \end{tabular}} & $100,000,000 & & & & E & \\ \hline & $120,000,000 & to be paid Jan & anuary 1,2024 & & & \\ \hline interest Payable as of December 31,2023 & $4,800,000 & to be paid Jar & anuary 1,2024 & & & \\ \hline \multicolumn{7}{|l|}{\begin{tabular}{l} Other \\ Factory Equipmont Putchase (Capatalized) \end{tabular}} \\ \hline Factory Equipment Purchase (Capitalized) & $8.0000,000 & 1stouarter & & & & \\ \hline Corporate Headquarters Renovation (capitalized) & $140,000.000 & 3rds ath Que & arter & & & \\ \hline \end{tabular} inputs tab Click 'OK' and this function will work through the math within your file to figure out what the sales price would need to be to reach this goal. If you have formula driven everything correctly, you should be able to click back to the inputs tab and see the new sales price listed as \$7.05. Manually change the butter sales price back to $6.10 on the inputs tab and then click over to the Goal Seek tab and manually enter your answer in the cell provided. This is the only cell in the project in which it is allowed that you manually type a number into the cell. 3. Compare your file against the project rubric below. You should be able to figure out your grade before you even submit your project by comparing yourself against the rubric. 4. Upload your completed Excel file to Canvas via the Excel case \#3 link to submit. entire budget. This becomes even more useful as your spreadsheets become more complex. 2. Complete the Goal Seek tab. Land O'Lakes would like to build a new production facility in 2025 which would require $300,000,000 of capital. However, interest rates are currently high, and they are looking to pay for this project without having to borrow the capital necessary. You have been asked to run an analysis that shows how much Land O'Lakes would need to increase their sales price for each package of butter to accommodate the cash necessary for this project. Remember: Land O'Lakes must maintain a minimum cash balance of $100,000,000. Use Goal Seek to find what the selling price of each package of butter would need to be to end 2024 with an ending cash balance of $400,000,000 ( $300,000,000 that will be used for the new production facility + the $100,000,000 minimum ending cash balance). HINT: To find goal seek... - USING MS OFFICE: click on the Data tab, and within the Forecast section click on What-If and find Goal Seek When you type your info into the template that pops up, you want to do the following: Set cell: cllck on the ending cash balance cell for 2024 in the cash budget quirements: 1. Prepare a master budget for the year ended December 31, 2024 that includes the following: Projected Sales Budget, Expected Cash Collections Budget, Production Budget, Variable Product Cost per Unit Calculation, Raw Materials Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling \& Administrative Budget, Cash Budget, Budgeted Income Statement, \& Budgeted Balance Sheet. Each budget should be completed as it appears in your template. There is a template provided to you in the assignments link in the Excel Project folder with an INPUTS tab you MUST use. Complete the yellow shaded areas of the template only. All of your spreadsheets must be formula driven! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values. You should NOT type a number the entire time you are working on this project. Any typed numbers will cost you 2 points per instance. There is a purpose to this requirement that we hope you understand. The beauty (usefulness) of linking your spreadsheets to an inputs page is when something changes, such as the cost per unit of raw materials, you don't have to go through the entire budget to fix every cell that used that value. Instead, when you change it on the inputs tab, it will automatically change every cell which used that value within your 1 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{\begin{tabular}{l} Land O'Lakes \\ Balance Sheet as of December 31, 2023 \end{tabular}} \\ \hline \multicolumn{3}{|l|}{ Assets } & \multicolumn{3}{|c|}{ Liabilities and Equities } \\ \hline Cash & $ & 318,000,000 & Accounts Payable & $ & 31,000,000 \\ \hline Accounts Receivable & $ & 79,000,000 & Notes Payable & $ & 120,000,000 \\ \hline Raw Materials Inventory & $ & 6,144,600 & Interest Payable & s & 4,800,000 \\ \hline Finished Goods Inventory & $ & 12,000,000 & Common Stock & $ & 800,000,000 \\ \hline \multirow{2}{*}{\begin{tabular}{l} Property, Plant and Equipment, net \\ Total Assets \end{tabular}} & & 1,300,000,000 & Retained Earnings & $ & 759,344,600 \\ \hline & & 1,715,144,600 & Total Liabilities and Equities & & ,715,144,600 \\ \hline \end{tabular}
