Please answer the last 3 photos
You work for a company that produces two types of Workbench. You are required to create a spreadsheet that can conduct a multi-product Cost Volume Profit Analysis. As products are largely made to order, you can assume that all products made are sold. You can also assume that sales price, fixed costs, and the sales mix stay constant. You will need to calculate the fixed costs, matierial costs and variable costs and then work out the break even point using a weighted average contribution margin. You have also been asked to show a torecast for different unit volumes and sales mixes and produce a break even analysis line chart. Download the full instructions and a mark breakdovin from iLearn. Use the data above to answer the folowing multiple choice questions. For each question change the correct cption to TRUE (t will 1 How many rows in an excel worksheet? A Infinite FALSE B 1048576 TRUE C 1024 FALSE D 16000 FALSE 2 Which of the following is a relative cell reference? A 583 FALSE B B:3 FALSE C $B$3 FALSE D B 3 TRUE 3 The Client IDs have been changed to centre align, by default they would align: A Bottom Right FALSE B Bottom Left TRUE C Top Right FALSE D Top Left FALSE 4 Which formula correctly calcualates the average client spend for 2022 in L13? A=16.115/10 FALSE B =AVERAGEIFS (16.115,2022) C=AVERAGEIFS(I6:115) TRUE D=AVERAGE(16:115) FALSE 5 Which of the following would calculate the date of the last order placed in 2022?! A=DATE(C6:Cl15) B=VLOOKUP(C6:C15) c=LAST(CiCl15) D=MAX(C61C15) 6 The cell H8 is empty. What will the formaula = COUNT(H6:H15) return? A 10 FALSE B An error FALSE C 0 TRUE D 9 FALSE Fixed Costs Bill of Materials Cost Volume Profit Analysis Profit Volume Forecast JB Compact JB Master Unit Increments Sales Volume in Units Total \$ Sales Value \begin{tabular}{|r|l|l|l|l|} \hline 60% & & Tax Rates \\ \hline 40% & & State Tax \\ \hline 1,000 & & Federal Tax \\ \hline 1,000 & & & & \\ \hline & & & & \\ \hline \end{tabular} Variable Costs Direct Material Direct Labor Other Vary-with-Unit Costs Sales Commissions Total Variable Costs \$ Profit Before Tax (Contribution Margin - Fixed Costs) Income Tax State Tax Federal Tax Total Income Tax \begin{tabular}{|r|r|r|r|r|r|} & & & & & \\ \hline$0.00 & $0.00 & $0.00 & $0.00 & $0.00 & \\ \hline \end{tabular} $ Profit after Tax \begin{tabular}{|l|l|l|l|l|} \hline & & & & \\ \hline \end{tabular} % Profit (of Sales) Total Costs Total Costs (Fixed, Variable \& Tax) You work for a company that produces two types of Workbench. You are required to create a spreadsheet that can conduct a multi-product Cost Volume Profit Analysis. As products are largely made to order, you can assume that all products made are sold. You can also assume that sales price, fixed costs, and the sales mix stay constant. You will need to calculate the fixed costs, matierial costs and variable costs and then work out the break even point using a weighted average contribution margin. You have also been asked to show a torecast for different unit volumes and sales mixes and produce a break even analysis line chart. Download the full instructions and a mark breakdovin from iLearn. Use the data above to answer the folowing multiple choice questions. For each question change the correct cption to TRUE (t will 1 How many rows in an excel worksheet? A Infinite FALSE B 1048576 TRUE C 1024 FALSE D 16000 FALSE 2 Which of the following is a relative cell reference? A 583 FALSE B B:3 FALSE C $B$3 FALSE D B 3 TRUE 3 The Client IDs have been changed to centre align, by default they would align: A Bottom Right FALSE B Bottom Left TRUE C Top Right FALSE D Top Left FALSE 4 Which formula correctly calcualates the average client spend for 2022 in L13? A=16.115/10 FALSE B =AVERAGEIFS (16.115,2022) C=AVERAGEIFS(I6:115) TRUE D=AVERAGE(16:115) FALSE 5 Which of the following would calculate the date of the last order placed in 2022?! A=DATE(C6:Cl15) B=VLOOKUP(C6:C15) c=LAST(CiCl15) D=MAX(C61C15) 6 The cell H8 is empty. What will the formaula = COUNT(H6:H15) return? A 10 FALSE B An error FALSE C 0 TRUE D 9 FALSE Fixed Costs Bill of Materials Cost Volume Profit Analysis Profit Volume Forecast JB Compact JB Master Unit Increments Sales Volume in Units Total \$ Sales Value \begin{tabular}{|r|l|l|l|l|} \hline 60% & & Tax Rates \\ \hline 40% & & State Tax \\ \hline 1,000 & & Federal Tax \\ \hline 1,000 & & & & \\ \hline & & & & \\ \hline \end{tabular} Variable Costs Direct Material Direct Labor Other Vary-with-Unit Costs Sales Commissions Total Variable Costs \$ Profit Before Tax (Contribution Margin - Fixed Costs) Income Tax State Tax Federal Tax Total Income Tax \begin{tabular}{|r|r|r|r|r|r|} & & & & & \\ \hline$0.00 & $0.00 & $0.00 & $0.00 & $0.00 & \\ \hline \end{tabular} $ Profit after Tax \begin{tabular}{|l|l|l|l|l|} \hline & & & & \\ \hline \end{tabular} % Profit (of Sales) Total Costs Total Costs (Fixed, Variable \& Tax)
