Your mother made delicious Muffins from scratch, and she left you the recipe. Everyone raved about them.
Question:
Your mother made delicious Muffins from scratch, and she left you the recipe. Everyone raved about them. Now, you are also making Muffins just as good as mom’s. But, you don’t want to divulge the recipe. As a hobby you make a dozen Muffins every Friday and sell them to a local bakery. The bakery’s customers have come to expect this, and all your Muffins are usually sold in a couple of hours. This success has made you and your spouse think that you could try the business on a larger scale. Your spouse has business experience and an MBA from IUN and has figured out what resources are needed to produce and sell Muffins on a larger scale. Your spouse has found a likely production site in town, some refurbished Muffin making machinery, and delivery trucks. The plant, equipment, and beginning working capital could be financed by some of your own funds and a $50,000 loan that your local banker says would be available. In addition, you have taken samples of your work to local bakeries and grocery stores, and many have offered to sell your Muffins.
You think you could sell whatever number of Muffins you make each day. The question that is bothering you is whether you think a profit can be made. Your Muffins feature delicious organic ingredients of high quality. You bake the Muffins for 30 minutes at 400 degrees. A production oven can bake 10 Muffins at a time. Production would occur in 45-minute blocks: 2 or 3 minutes to load Muffins into the oven, 40 minutes for baking, and 2 or 3 minutes to put the baked Muffins into paper boxes and shelve them. A workday would last 12 hours, which is sixteen 45-minute production blocks. Thus, a single worker could produce 160 Muffins in a day (16*10). You have five ovens, which is the maximum capacity of the premises. Thus, you could produce up to 800 Muffins a day using five production workers at a time. You and your spouse would prepare the mix for day’s first batch of Muffins. Thereafter while one batch bakes, workers would create the mix for the next batch, and put the Muffins together for baking. At the end of the day, while the last batch of Muffins bakes, workers would thoroughly clean the premises. Your production facility would be open 6 days a week for 50 weeks a year, which would be 300 production days per year. You would not produce Muffins on Sundays. You think that a delivery truck can be rigged to hold 100-boxed Muffins. The truck driver would be required to load the truck and then drive to the bakery or grocery store. The driver would unload the truck and take the Muffins in. In some cases, the driver would shelve the Muffins; otherwise, store or bakery workers would perform this task. On average, you think a truck driver could cover 150 miles per day; most of the mileage would be city driving.
Your trucks would get 15 miles per gallon. You would lease the needed trucks for $50 per day each. Production workers would be paid $15 an hour for 12-hour day. Truck drivers would be paid $20 per hour for a 12-hour day. Overtime rates would not apply for workdays in which employees worked more than 8 hours. You and your spouse would take care of the office work. You expect fixed administrative expenses of $20,000 per year to pay for renting the premises, heat, power, and so on. Although you expect demand for your Muffins to be stimulated by word of mouth and social media, you know that you will need an advertising campaign. Your research shows that such campaigns vary in cost and effectiveness. You expect advertising to affect demand and the price you can charge stores and bakeries. Two possible campaigns have been identified. The more expensive campaign should stimulate sales and selling prices more than another campaign. Each campaign will be more active—and thus more expensive—in the first year. As a result of bad weather around the world, the cost of food has increased in recent years. Thus, you are concerned about the rising cost of ingredients. These cost increases probably could not be fully recouped by increasing your selling prices. In the same way, you are concerned about the rising cost of gasoline, which has been about $4 a gallon recently. You would open for business if you think you could make a profit by 2023, which would be the third year of operations. You want to use Excel to estimate profits for 2021-23. Your Excel model needs to account for possible sales levels and for changes in ingredient and gasoline costs. Your model will let you develop “what-if” scenarios with the inputs, see the financial results, and then help you decide if you should go into the cupcake-making business. The spreadsheet skeleton is given for you – Muffins-Investment-Data-2020.xlsx. Details for completing the spreadsheet are as given below:
Constants: Tax Rate—applied to income before taxes. The rate is expected to be the same each year. Minimum cash needed to start year—You want to have at least $10,000 in cash at the beginning of each year. Your banker will lend you the needed (if needed) amount at the end of a year to begin the New Year with $10,000. Number of production days—Your production facility will operate 300 days a year. Maximum number of Muffins produced per day—you can make 800 Muffins per day at most. Number of Muffins a truck can carry—A truck can be set up to comfortably store 100 Muffins on racks. Cost to lease a truck per day—A truck can be leased for $50 a day. Number of truck miles per day—On average, a driver can cover 150 miles per day in and around the city. Truck miles per gallon—A truck is expected to average 15 miles per gallon. Production worker pay per hour—The rate is $15 and hour, with no overtime. Truck driver pay per hour—The rate is $20 an hour, with no overtime. Administrative costs in year—Fixed costs are expected to be $20,000 per year for rent, maintenance, insurance, electricity, and so on. These costs do not fluctuate with changes in production. High-cost advertising campaign—The more expensive campaign will cost $20,000 in the first year, with costs declining as the years go on. Low-cost advertising campaign—The less expensive campaign will cost $10,000 in the first year, with costs declining as the years go on. The interest rate on debt owed—Your banker will charge interest for any debt owed in the year. The rate can vary; the banker says that interest rates are expected to rise as the economy continues to recover. Summary: For each year, the spreadsheet should show net income after taxes, cash on hand at the end of the year and debt owed to the bank at the end of the year. The net income, cash and debt cells should be formatted as currency with zero decimal places. These values are computed elsewhere in the spreadsheet and should be copied here. Calculations: Per day demand for Muffins—This value is the number of Muffins that stores and bakeries want to buy each day. The amount is a function of advertising level. In 2021, if the more expensive campaign is used, 480 Muffins per day will be needed; otherwise, 320 Muffins per day will be needed. In 2022, if the more expensive campaign is used, the demand will be twice the 2021 demand; otherwise, the demand will be 1.5 times the 2021 demand. In 2023, if the more expensive campaign is used, the demand will be 1.5 times the 2022 demand; otherwise the demand will be 1.25 times the 2022 demand. However, the demand cannot exceed the constant 800; you can deal with this constraint by using the MIN() function or an IF() statement. Muffins produced in a year—This amount is a function of the number of Muffins needed per day and the number of production days, which is a value from the Constants section. Number of production workers needed—This amount is a function of the daily demand and the output per worker, 160, a value you can hard-code in your formula. Use the Round() function with zero decimal places to avoid specifying a part of a worker.
A number of delivery trucks needed—This amount is a function of the daily demand and truck capacity, a constant value. Use the ROUNDUP() function with zero decimal places to avoid specifying a part of truck. Truck miles per day—This amount is a function of the number of trucks and truck mileage per day from the Constants section. Gallons of gas used per day—This amount is a function of truck mileage per day and miles per gallon from the Constants section. Use the ROUNDUP() function with zero decimal places to avoid specifying a part of a gallon. Production worker pay—This amount is the production worker pay for all workers during the year. The value is a function of the number of workers, the pay per hour, the number of hours per day (12, a constant you can hard-code), and the number of business days. Truck driver pay—This amount is the truck driver pay for all drivers during the year. The value is a function of the number of drivers, the pay per hour, the number of hours per day (12, a constant you can hard-code), and the number of business days. Cost of gasoline—This amount is a function of the number of gallons used per day, the cost of a gallon of gasoline during the year (a value in the Inputs section), and the number of business days. Selling price of a Muffin —The price is $12 each year if the more expensive advertising campaign is used; otherwise, the price is $11. Variable cost of a Muffin—This amount is the cost of ingredients, supplies, power, and other items used to make a cupcake. The cost is a function of food price increase during the year and the prior year’s variable cost. Thus, the 2021 cost is based on the 2020 cost and the 2020 food cost increase. The 2022 cost is based on the 2021 cost and the 2021 food cost increase, and so on. Advertising cost—This amount is a function of the campaign value from the Inputs section. Campaign costs are shown in the Constants section. Truck leasing cost—This is amount is a function of the number of trucks, the leasing cost per day and the number of business days. Income and Cash Flow Statements: Beginning-of-year cash on hand—This value is the cash on hand at the end of the prior year. Revenue—this amount is a function of the number of Muffins needed and the selling price.
Both of these values are from the Calculations section. Cost of Muffins produced—this value is a function of the number of Muffins produced during the year and the variable cost of a Muffin. Both of these values are taken from the Calculations section. Production worker pay—From Calculations section can be copied here. Truck driver pay—From Calculations section can be copied here. Truck leasing—From Calculations section. Advertising—From Calculations section. Gasoline—From Calculations section. Administrative—From Constants section. Total costs—This amount is the total cost of Muffin production, production worker pay, truck driver pay, truck leasing, advertising, gasoline, and administrative costs. Income before interest and taxes—This amount is the difference between revenue and total costs. Interest expense—This amount is the product of the debt owed to the bank at the beginning the year and the interest rate for the year (a value from the Constants section). Income before taxes—This amount is the difference between income before interest and taxes and interest expense. Income tax expense—This amount is zero if income before taxes is zero or less than zero (a loss). Otherwise, income tax expense is the product of the year’s tax rate (a value from the Constants section) and income before taxes. Net income after taxes—This amount is the difference between income before taxes and income tax expense. Net cash position—(NCP)—The NCP at the end of a year equals the cash at the beginning of the year plus the year’s net income after taxes. Borrowing from bank—Assume that your bank will lend enough money at the end of the year to reach the minimum cash needed to start the next year. If the NCP is less than this minimum, the company must borrow enough to start the next year with the minimum. Borrowing increases the cash on hand. Repayment to bank—If the NCP is more than the minimum cash needed and some debt is owed at the beginning of the year, you must pay off as much debt as possible (without taking the cash below the minimum amount required to start the next year). Repayments reduce cash on hand. End-of-year cash on hand—This amount is the NCP plus any borrowing and minus any repayments. Debt Owed Section: Beginning-of-year debt owed—Debt owed at the beginning of a year equals the debt owed at the end of the prior year. Borrowing from bank—This amount has been calculated elsewhere and can be echoed to this section. Borrowing increases the amount of debt owed. Repayment to bank—This amount has been calculated elsewhere and can be echoed to this section. Repayments reduce amount of debt owed. End-of-year debt owed—In all years, this is the amount owed at the beginning of a year, plus borrowing during the year, and minus repayments during the year. Deliverables 1. Using the data determine if you can make money in the cupcake business. Explain. 2. You want to model three economic combinations of food price inflation and gasoline price. Favorable condition: Food inflation 1% each year; gallon of gasoline $3.00 each SoSo condition: Food inflation 3% each year; gallon of gasoline $4.50 each Unfavorable condition: Food inflation is 5% each year; a gallon of gasoline is $5.00 With these three conditions each of advertising campaigns need to be combined to get six scenarios: Favorable-High---Favorable Economic Condition and High advertising campaign Favorabe-Low---Favorable Economic Condition and Low advertising campaign SoSo-High---SoSo Economic Condition and High advertising campaign SoSo-Low--- SoSo Economic Condition and Low Advertising campaign Unfavorable-High—Unfavorable Economic Condition and High advertising campaign Unfavorable-Low---Unfavorable Economic condition and Low advertising campaign
Run “what if” scenarios using the Scenario Manager by using 6 sets of input values.
Put all the Scenario Manager results in a separate spreadsheet tab.
Submit your analysis.
Explain.
Expert Answer:
Stepbystep solution Comment Step 1 of 3 There are many physiological disorders related to ... View the full answer
