Hi,

I need help answering this case study. It needs to be done in Excel (with the formula in each cell) and each answer should be numbered to correspond with the question. There are 16 questions to answer (please skip question one and begin at question 2).

Thanks.

This case study is to help integrate the managerial accounting concepts that were covered in class and apply them to a real-world business setting. Business Description You will assume the role of an entrepreneur to start a small company. Your company will produce and sell gourmet cupcakes through a storefront in a location of your choice. Your business is scheduled to launch on July 1, 2017. Cost information: 1 Cost of goods sold: a. Ingredients are .35 per cupcake b. Boxes and Cupcake Cups are .05 per cupcake 2 Equipment that will be required to be acquired at the start of business includes ovens, racks, display case, counter, cash register, and other baking equipment and will cost $150,000. The equipment is expected to last 10 years without salvage value. Straight-line method of depreciation should be used. 3 On average one person can make, bake, and decorate 8 dozen (96) cupcakes in an 8 hours shift. Each worker is paid $10 per hour. 4 Sales personnel are required 56 hours per week and are paid $8 per hour. 5 Monthly rent, which includes utilities, is $1,500. 6 Business insurance is purchased at a cost of $2,000 per year. 7 Advertising costs are expected to be $12,000 per year. Requirements: Using separate tabs in a spreadsheet, provide your answers for the following. 1 Name your company (5 points) 2 What and how much are the variable costs? Present each item in cost per cupcake basis (5 points). 3 What and how much are the fixed costs? Present each item in total cost per year (5 points). 4 Write out the annual cost formula in Y = a + bX format (5 points). 5 Calculate the total amount of cash that will be needed at the start of the business in order to buy all necessary equipment and machines and cover the first three months of fixed expenses. This amount will be your initial investment in the business. Note that the equipment will be paid in full on the first day of business. Other expenses will be paid on a monthly basis (10 points). 6 Develop a price using a target price (what do you think a customer will pay for one of your cupcakes? Provide support for your target price (10 points). 7 Develop a price using cost-based pricing assuming that you expect to sell 35,000 cupcakes the first year (10 points). 8 Using the price you calculated using cost based pricing, calculate contribution margin per cupcake and contribution margin ratio (5 points). 9 Based on the price you calculated, calculate how many cupcakes need to be sold in order to break-even. Calculate how much sales in dollars are needed to break-even (10 points). 10 Prepare a cost/volume/profit chart (15 points). 11 Prepare the company's forecasted functional income statement for the year ended on 6/30/2018 based on the sale of 40,000 cupcakes (10 points). 12 Prepare a contribution format income statement assuming sales of 44,000 cupcakes (10 points). 13 If sales could increase by 10% (to 44,000 from 40,000 cupcakes), by how much in dollars would net operating income increase? Use operating leverage to calculate the percentage increase in operating income? (5 points) 14 Calculate how many cupcakes need to be sold in order to make a $50,000 target profit for the year (5 points). 15 Based on the assumption that the number of cupcakes calculated in item 14 are made and sold during the first year of business, calculate the margin of safety and the operating leverage for the business. What do these figures tell you about how risky the business is? (15 points) 16 Prepare a cash budget for the company's first year of operations based on the sales calculated in item 14. Assume all sales are cash sales and that all costs and expenses are paid in cash. You decide to maintain a minimum cash balance of $5,000 (15 points). 17 After your thorough analyses of costs, sales, and profitability of your cupcake business throughout this project, what is your overall impression of the future potential of the business? Provide a short assessment (10 points). 1 Name your company Wonder Cake 2 What and how much are the variable costs? Present each item in cost per cupcake basis (5 points). Ingredients Boxes and Cupcake Cups Direct Labor Total Variable Cost per Cupcake 0.35 0.05 0.83 1.23 What and how much are the fixed costs? Present each item in total cost per yea Depreciation Sales personnel Rent Business insurance Advertising costs Total Fixed Cost 15000 23296 18000 2000 12000 70296 2.008457 4 Write out the annual cost formula in Y = a + bX format (5 points). y a b X total production cost Fixed Cost variable cost per unit Number of units Y =70296+1.23X 5 Calculate the total amount of cash that will be needed at the start of the business in order to buy all necessary equipmen Equipment Cost Sales personnel Rent Business insurance Advertising costs 150000 5824 4500 2000 3000 165324 o buy all necessary equipment and machines and cover the first three months of fixed expenses. This amount will be your initial invest t will be your initial investment in the business. Note that the equipment will be paid in full on the first day of business. Other expense business. Other expenses will be paid on a monthly basis( Annual Forcast Espected Margin Variable Cost Fixed Cost per Cup Cake Total Cost Requred Profit Sales Price per Cup cake 50000 Cup cake Assumption 10% 1.23 1.41 2.64 0.26 2.90 Annual Forcast Espected Margin Variable Cost Fixed Cost per Cup C Total Cost Requred Profit Sales Price per Cup c 35000 Cup cake Assumption 10% 1.23 2.01 3.24 0.32 3.57 Sales Variable Cost Contribution margin Contribution margin ratio Per Cup Cake 3.57 1.23 2.33 65.41% calculate how many cupcakes need to be sold in order to break-even. Break even in units Break even Dollar 30136 107464 11 Prepare the company's forecasted functional income statement for the year ended on 6/30/2018 based on the sale of 4 Sales 142639 Cost of Goods sold 49333 Gross Profit 93305 Operating Expenses Depreciation 15000 Sales personnel 23296 Rent 18000 Business insurance 2000 Advertising costs 12000 Total Operating Expense 70296 Net Income 23009 018 based on the sale of 40,000 cupcakes (10 points). 12 Prepare a contribution format income statement assuming sales of 44,000 cupcakes (10 points). Sales Variable Cost Contrbution Fixed Cost Net Income 156903 54267 102636 70296 32340 Degree of operating leverage 44000 Units Sales Variable Cost Contrbution Fixed Cost Net Income 156903 54267 102636 70296 32340 40000 Units Sales Variable Cost Contrbution Fixed Cost Net Income 142639 49333 93305 70296 23009 Degree of operating leverage = % change in operating income/% change in sales % change in operating income % change in sales Degree of operating leverage 40.55% 10.00% 405.51% 14 Calculate how many cupcakes need to be sold in order to make a $50,000 target profit for the year 51571 Cups margin of safety 21435 Cups Degree of operating leverage = % change in operating income/% change in sales % change in operating inco 40.55% % change in sales 10.00% Degree of operating leverag 405.51% Higher the Margin of Saftey ,lesser Risk of Loss 16 Prepare a cash budget for the company's first year of operations based on the sales calculated in item 14. Assume all sa Openining Balance Sales Receipts Less Out flow of Cash Purchase of Ingrediatents and Packing Equipment Cost Sales personnel Rent Business insurance Advertising costs Total outflow Defficency Money to be borrowed Closing Balance 0 183900 63604 150000 23296 18000 2000 12000 268900 -85000 90000 5000 ulated in item 14. Assume all sales are cash sales and that all costs and expenses are paid in cash. You decide to maintain a minimum c to maintain a minimum cash balance of $5,000 (15 points)