Please include formulas for the Excel calculations so I am able to understand how to do this problem again in the future.
Morrison Corporation is a small company that sells digital thermometers and other supplies to hospitals in the Phoenix, AZ area. The company employs one sales agent who earns both a salary and commission. Last year, Morrison Corporation purchased a new Subaru Impreza for $30,000 for the sales agent to use for 100% business transportation. The company controller estimated that the Impreza would have a five-year life and a residual value of $6,000. The company uses straight-line depreciation for all plant assets. The costs for this year specifically related to the Impreza are insurance for $1,000, fuel for $5,500 and other costs. The controller estimates that with the increase in fuel costs, that the amount next year for fuel will increase by 12%. The controller believes that the insurance cost will stay constant. The other costs include maintenance, fixing flat tires, oil changes, tickets, parking, car washes/detailing, etc. The other costs for the Impreza for each month of last year is below. The controller had prepared a Budgeted Income Statement for next year, but didn't include the costs for the Impreza. The Budgeted Income Statement is below: 1. Using the high-low method, determine the portion of "Other" costs that are fixed (rounded to the nearest dollar) and the variable costs per mile (rounded two decimal places). 2. Prepare a budgeted contribution format income statement with the costs related to the Impreza included. It is estimated that the sales agent will drive 55,000 miles next year. 3. Using your budged contribution format income statement, calculate break even sales in dollars. 4. The owner of the company would like to have profit of $300,000 next year, what dollar volume of sales would be necessary to meet this target? 5. Write a memo to the owner of Morrison Corporation briefly explaining your work and giving recommendations as to how Morrison might achieve the $300,000 target profit. Deliverables An Excel workbook using formulas for all required calculations. The workbook must be easy to Morrison Corporation is a small company that sells digital thermometers and other supplies to hospitals in the Phoenix, AZ area. The company employs one sales agent who earns both a salary and commission. Last year, Morrison Corporation purchased a new Subaru Impreza for $30,000 for the sales agent to use for 100% business transportation. The company controller estimated that the Impreza would have a five-year life and a residual value of $6,000. The company uses straight-line depreciation for all plant assets. The costs for this year specifically related to the Impreza are insurance for $1,000, fuel for $5,500 and other costs. The controller estimates that with the increase in fuel costs, that the amount next year for fuel will increase by 12%. The controller believes that the insurance cost will stay constant. The other costs include maintenance, fixing flat tires, oil changes, tickets, parking, car washes/detailing, etc. The other costs for the Impreza for each month of last year is below. The controller had prepared a Budgeted Income Statement for next year, but didn't include the costs for the Impreza. The Budgeted Income Statement is below: 1. Using the high-low method, determine the portion of "Other" costs that are fixed (rounded to the nearest dollar) and the variable costs per mile (rounded two decimal places). 2. Prepare a budgeted contribution format income statement with the costs related to the Impreza included. It is estimated that the sales agent will drive 55,000 miles next year. 3. Using your budged contribution format income statement, calculate break even sales in dollars. 4. The owner of the company would like to have profit of $300,000 next year, what dollar volume of sales would be necessary to meet this target? 5. Write a memo to the owner of Morrison Corporation briefly explaining your work and giving recommendations as to how Morrison might achieve the $300,000 target profit. Deliverables An Excel workbook using formulas for all required calculations. The workbook must be easy to
