| Cost Volume Profit Analysis | | Data: | | | |
| Recent Quarterly Data | | Budgeted Sales Units for 4th Quarter | 385,000 | | |
| Sales | Manufacturing | Selling | Administrative | | Standard Deviation for Budgeted Sales Units | 7,000 | | |
| Units | Costs | Costs | Costs | | Budgeted Selling Price | $8.00 | | |
| 376,000 | $1,580,000 | $450,000 | $265,000 | | Link for Probability Computation | www.hackmath.net/en/calculatorormal-distribution |
| 404,000 | 1,660,000 | 469,000 | 262,000 | | | | | |
| 418,000 | 1,750,000 | 480,000 | 267,000 | | | | | |
| 432,000 | 1,677,000 | 476,000 | 267,000 | | Place Your Answers In The Space Provided Below | |
| 446,000 | 1,808,000 | 529,000 | 271,000 | | 1. | Fixed Cost | Variable Cost Rate | |
| 474,000 | 1,957,000 | 547,000 | 276,000 | | Manufacturing | | | |
| 502,000 | 1,907,000 | 539,000 | 274,000 | | Selling | | | |
| 516,000 | 1,946,000 | 521,000 | 277,000 | | Administrative | | | |
| 530,000 | 2,012,000 | 552,000 | 279,000 | | Total | | | |
| 558,000 | 2,083,000 | 543,000 | 281,000 | | | | | |
| 488,000 | 1,884,000 | 567,000 | 278,000 | | 2.a. Compute Margin of Safety in Units | | | |
| 460,000 | 1,758,000 | 459,000 | 271,000 | | 2.b. Compute Margin of Safety in dollar | | | |
| | | | | | 2.c.Compute Margin of Safety in % | | | |
| | | | | | | | | |
| | | | | | 3. | | | |
| | | | | | Budgeted Income Statement For 4th Quarter of 2022 | | |
| | | | | | Sales | | | |
| | | | | | Cost of Goods Sold (Manufacturing) | | | |
| | | | | | Gross Profit | | | |
| | | | | | Operating Expenses: | | | |
| | | | | | Selling Expenses | | | |
| | | | | | Administrative Expenses | | | |
| | | | | | Total Operating Expense | | | |
| | | | | | Operating Income | | | |
| | | | | | | | | |
| | | | | | 4. Confidence Interval | | | |
| | | | | | Upper Level | | | |
| | | | | | Mean | | | |
| | | | | | Lower Level | | | |
| | | | | | | | | |
| | | | | | 5. Probability of Profit > $800,000 | | | |
Place your answers for the two problems below in the space provided on the Fxcel file provided, then, submit your Excel file through the dropbox on D2L by 11:59 pm today. Show your computations on the Excel fille in support of your answers. Name the Excel file your LastName-FirstName-Final. Problem 1. Based on article "Using Ercel fo Deal with Uncertainty in Managerial Decision Making" and materials in Assignment 1 Robert Company manufactures and sells a product called "Zoom". Currently company is producing and selling 410,000 units of Zoom per quarter at a selling price $8 per unit. The ptoduction capacity is up to 600,000 units per quarter. Due to inflation and expected recession, company's management has started panicking. To deal with uncertainty associated with future sales and profit, Robert Company has hired you as consultant to generate useful information to deal with uncertainty. You are asked to provide answer to the following questions in the cells highlighted in yellow on the Exeel Tab named "CVP Analysis". 1. Using the 12 quarterly data on sales units, manufacturing costs, selling costs, and administrative costs, determine the behavior of each of the three expenses'costs using regression analysis. Rather than typing the numbers in the Answers section, link the cells to the regression results using formula. 2. Management wants to know the amount by which current sales can drop before incurring losses. a. Compute Margin of Safety in Units b. Compute Margin of Safety in dollar c. Compute Margin of Safety in % 3. Robert company has revised its budgeted sales for 4th quarter to 385,000 units. Using your answers for Part I and other data provided, complete the content of cells of the Budgeted Income Statement for the 4th quarter of 2022 on the I" Tab of Exeel file. The company management plans to simulate the badgeted income statement numbers for various scenarios. Thus, the budget on the Excel should be created in a manner that allows for simulation (i.e., by using only formulas inside of each cell of the budget). 4. Due to expected decline in sales, Robert company has revised its budgeted sales for 4th quarter to 385.000 units, Standard deviation associated with budgeted sales units for the 4th quarter is 7,000 units based upon the variations in the past. Simulate the number of sales units for 100 simulation and compute profit for cach scenario. Given the discerned mean and standard deviation of the profits, develop the confidence interval (the range of possible profits) for the 4ti quarter's profit at 95% confidence level. 5. Based on your work for Pat 4, what is the probability of 4t quarter profit to be $800,000 or higher assuraing normal distribution? Problem 2. Hellen Company manufactures and sells two types of control devices - Jidoka and Poka Yoke. - A portion of each month's sales is collected in the month of salc, and the rest is collocted in the following month. - Production requires two types of materials - Material A and Material B. - A portion of each month's purchase of raw materials is paid in the month of purchase, and the rest is paid in the following month. - All manufacturing, sellang and administrative expenses are paid in cash during the month incurred. As reflected on the Excel sheet, specifie cash dividends and capital expenditares for purchase of machines are planned. - Minimum required cash balance at the end of each month is $50,000. - If necessary, the company will borrow cash from a bank. The borrowing wall be in multiples of $1,000 and will bear interest at 2% per month. All bocrowing will take place at the beginning of the month. A month's interest will be paid in cash at the end of month. Any cash available will be used to pay off the eutstanding debt in multiples of $1,000 at the end of each month. The amount of borrowing, interest, and payoff must be computed manually and entered in the data section in the space provided and highlighted in yellow. The company's policy and plan for the first three months of upcoming year is summarized on Excel file in the tab entitled "Budgeting". The accountant of company, after preparing sales badget, prodaction bodget, and RM purchases budget, has taken a vacation to go to Hawaii. You have been hired to prepare the Cash Budget for the company: The conpany management plans to simulate the budgeted numbers fot vanious scenarios. Thus, the budgets on the Excel should be created in a manner that allows for simulation (i.c., by asing only formulas inside of each cell of the budgets). Budget lines must have appropriate, clear, complete, and understandable hoadings with necessary formatting of the cells. Place your answers for the two problems below in the space provided on the Fxcel file provided, then, submit your Excel file through the dropbox on D2L by 11:59 pm today. Show your computations on the Excel fille in support of your answers. Name the Excel file your LastName-FirstName-Final. Problem 1. Based on article "Using Ercel fo Deal with Uncertainty in Managerial Decision Making" and materials in Assignment 1 Robert Company manufactures and sells a product called "Zoom". Currently company is producing and selling 410,000 units of Zoom per quarter at a selling price $8 per unit. The ptoduction capacity is up to 600,000 units per quarter. Due to inflation and expected recession, company's management has started panicking. To deal with uncertainty associated with future sales and profit, Robert Company has hired you as consultant to generate useful information to deal with uncertainty. You are asked to provide answer to the following questions in the cells highlighted in yellow on the Exeel Tab named "CVP Analysis". 1. Using the 12 quarterly data on sales units, manufacturing costs, selling costs, and administrative costs, determine the behavior of each of the three expenses'costs using regression analysis. Rather than typing the numbers in the Answers section, link the cells to the regression results using formula. 2. Management wants to know the amount by which current sales can drop before incurring losses. a. Compute Margin of Safety in Units b. Compute Margin of Safety in dollar c. Compute Margin of Safety in % 3. Robert company has revised its budgeted sales for 4th quarter to 385,000 units. Using your answers for Part I and other data provided, complete the content of cells of the Budgeted Income Statement for the 4th quarter of 2022 on the I" Tab of Exeel file. The company management plans to simulate the badgeted income statement numbers for various scenarios. Thus, the budget on the Excel should be created in a manner that allows for simulation (i.e., by using only formulas inside of each cell of the budget). 4. Due to expected decline in sales, Robert company has revised its budgeted sales for 4th quarter to 385.000 units, Standard deviation associated with budgeted sales units for the 4th quarter is 7,000 units based upon the variations in the past. Simulate the number of sales units for 100 simulation and compute profit for cach scenario. Given the discerned mean and standard deviation of the profits, develop the confidence interval (the range of possible profits) for the 4ti quarter's profit at 95% confidence level. 5. Based on your work for Pat 4, what is the probability of 4t quarter profit to be $800,000 or higher assuraing normal distribution? Problem 2. Hellen Company manufactures and sells two types of control devices - Jidoka and Poka Yoke. - A portion of each month's sales is collected in the month of salc, and the rest is collocted in the following month. - Production requires two types of materials - Material A and Material B. - A portion of each month's purchase of raw materials is paid in the month of purchase, and the rest is paid in the following month. - All manufacturing, sellang and administrative expenses are paid in cash during the month incurred. As reflected on the Excel sheet, specifie cash dividends and capital expenditares for purchase of machines are planned. - Minimum required cash balance at the end of each month is $50,000. - If necessary, the company will borrow cash from a bank. The borrowing wall be in multiples of $1,000 and will bear interest at 2% per month. All bocrowing will take place at the beginning of the month. A month's interest will be paid in cash at the end of month. Any cash available will be used to pay off the eutstanding debt in multiples of $1,000 at the end of each month. The amount of borrowing, interest, and payoff must be computed manually and entered in the data section in the space provided and highlighted in yellow. The company's policy and plan for the first three months of upcoming year is summarized on Excel file in the tab entitled "Budgeting". The accountant of company, after preparing sales badget, prodaction bodget, and RM purchases budget, has taken a vacation to go to Hawaii. You have been hired to prepare the Cash Budget for the company: The conpany management plans to simulate the budgeted numbers fot vanious scenarios. Thus, the budgets on the Excel should be created in a manner that allows for simulation (i.c., by asing only formulas inside of each cell of the budgets). Budget lines must have appropriate, clear, complete, and understandable hoadings with necessary formatting of the cells
