**
Fill This Form To Receive Instant Help**

Homework answers / question archive / Organic DARK Chocolate Chip Cookies Investment-2021 Your grandmother made delicious DARK Chocolate Chip Cookies from scratch, and she left you the recipe

Organic DARK Chocolate Chip Cookies Investment-2021 Your grandmother made delicious DARK Chocolate Chip Cookies from scratch, and she left you the recipe. Everyone raved about them. Now, you are also making DARK Chocolate Chip Cookies just as good as your grandmother’s. But, you don’t want to divulge the recipe. As a hobby you make a dozen DARK Chocolate Chip Cookies every Friday and sell them to a local bakery. The bakery’s customers have come to expect this, and all your DARK Chocolate Chip Cookies 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 a brilliant MBA from IUN and has figured out what resources are needed to produce and sell DARK Chocolate Chip Cookies on a larger scale. Your spouse has found a likely production site in town, some refurbished DARK Chocolate Chip Cookies 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 DARK Chocolate Chip Cookies. You think you could sell whatever number of DARK Chocolate Chip Cookies you make each day. The question that is bothering you is whether you think a profit can be made. Your DARK Chocolate Chip Cookies feature delicious organic ingredients of high quality. You bake the DARK Chocolate Chip Cookies for 30 minutes at 400 degrees. A production oven can bake 10 DARK Chocolate Chip Cookies at a time. Production would occur in 45-minute blocks: 2 or 3 minutes to load DARK Chocolate Chip Cookies into the oven, 40 minutes for baking, and 2 or 3 minutes to put the baked DARK Chocolate Chip Cookies 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 DARK Chocolate Chip Cookies in a day (16*10). You have five ovens, which is the maximum capacity of the premises. Thus, you could produce up to 800 DARK Chocolate Chip Cookies a day using five production workers at a time. You and your spouse would prepare the mix for day’s first batch of DARK Chocolate Chip Cookies. Thereafter while one batch bakes, workers would create the mix for the next batch, and put the DARK Chocolate Chip Cookies together for baking. At the end of the day, while the last batch of DARK Chocolate Chip Cookies 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 DARK Chocolate Chip Cookies on Sundays. 1 You think that a delivery truck can be rigged to hold 100-boxed DARK Chocolate Chip Cookies. 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 DARK Chocolate Chip Cookies in. In some cases, the driver would shelve the DARK Chocolate Chip Cookies, 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 $30,000 per year to pay for renting the premises, heat, power, and so on. Although you expect demand for your DARK Chocolate Chip Cookies 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 other 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 2024, which would be the third year of operations. You want to use Excel to estimate profits for 2022-24. 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 DARK Chocolate Chip Cookiesmaking business. The spreadsheet skeleton is given for you – DARK Chocolate Chip Cookies-Investment-Data2021.xlsx. Details for completing the spreadsheet are as given below: Constants: 2 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 DARK Chocolate Chip Cookies produced per day—you can make 800 DARK Chocolate Chip Cookies per day at most. Number of DARK Chocolate Chip Cookies a truck can carry—A truck can be set up to comfortably store 100 DARK Chocolate Chip Cookies 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 $30,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 $25,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. 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. 3 Calculations: Per day demand for DARK Chocolate Chip Cookies—This value is the number of DARK Chocolate Chip Cookies that stores and bakeries want to buy each day. The amount is a function of advertising level. In 2022, if the more expensive campaign is used, 480 DARK Chocolate Chip Cookies per day will be needed; otherwise, 320 DARK Chocolate Chip Cookies per day will be needed. In 2023, if the more expensive campaign is used, the demand will be twice the 2022 demand; otherwise, the demand will be 1.5 times the 2022 demand. In 2024, if the more expensive campaign is used, the demand will be 1.5 times the 2023 demand; otherwise the demand will be 1.25 times the 2023 demand. However, the demand cannot exceed the constant 800; you can deal with this constraint by using the MIN() function or an IF() statement. DARK Chocolate Chip Cookies produced in a year—This amount is a function of the number of DARK Chocolate Chip Cookies 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. 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. 4 Selling price of a DARK Chocolate Chip Cookies —The price is $15 each year if the more expensive advertising campaign is used; otherwise, the price is $13. Variable cost of a DARK Chocolate Chip Cookies—This amount is the cost of ingredients, supplies, power, and other items used to make a DARK Chocolate Chip Cookies. The cost is a function of food price increase during the year and the prior year’s variable cost. Thus, the 2022 cost is based on the 2021 cost and the 2021 food cost increase. The 2023 cost is based on the 2022 cost and the 2022 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 DARK Chocolate Chip Cookies needed and the selling price. Both of these values are from the Calculations section. Cost of DARK Chocolate Chip Cookies produced—this value is a function of the number of DARK Chocolate Chip Cookies produced during the year and the variable cost of a DARK Chocolate Chip Cookies. 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 DARK Chocolate Chip Cookies 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). 5 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 DARK Chocolate Chip Cookies business. Explain. 2. You want to model three economic combinations of food price inflation and gasoline price. 6 Favorable condition: Food inflation 1% each year; gallon of gasoline $3.50 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. 7 Dark Chocolate Chip Cookies Investment Constants 2021 Tax Rate Minimum cash needed at start of year Number of production days Maximum number of DarkCCs produced per day Number of DarkCCs a truck can carry Cost to lease a truck per day Number of truck miles per day Truck miles per gallon Production worker pay per hour Truck driver pay per hour Administrative costs in year High cost advertising campaign Low cost advertising campaign Interest rate on debt owed NA NA NA NA NA NA NA NA NA NA NA NA NA NA Inputs $ $ $ $ $ $ 20% 10.000 300 800 100 50 150 15 15 20 30.000 25.000 10.000 4% 2022 NA NA NA Summary of Key Results 2021 Net income after taxes End-of-year cash on hand End-of-year debt owed 2022 NA NA NA Calculations Per day demand for DarkCCs DarkCCs produced in year Number of production workers needed Number of delivery trucks needed Truck miles per day Gallons of gas used per day Production worker pay Truck driver pay Cost of gasoline Selling price of a DarkCC Variable cost of a DarkCC Advertising cost Truck leasing cost $ 2021 Advertising Level (1=High, 2=Low) Food inflation in year (.xx) Cost of gallon of gas in year ($x.xx) 2022 2021 NA NA NA NA NA NA NA NA NA NA $ 7,00 NA NA 2022 Income and Cash Flow Statements 2021 Beginning-of-year cash on hand NA Revenue Costs: Cost of DarkCCs produced Production worker pay Truck driver pay Truck leasing Advertising Gasoline Administrative Total costs Income before interest and taxes Interest expense Income before taxes Income tax expense Net income after taxes NA Net cash position (NCP) Borrowing from bank Repayment to bank End-of-year cash on hand NA NA NA 10.000 NA NA NA NA NA NA NA NA NA NA NA NA NA $ Debt Owed Beginning-of-year debt owed Borrowing from bank Repayment to bank End-of-year debt owed 2021 $ NA NA NA 50.000 2022 2023 $ $ $ $ $ $ $ 20% 10.000 300 800 100 50 150 15 15 20 30.000 20.000 7.000 5% 2024 $ $ $ $ $ $ $ 20% 10.000 300 800 100 50 150 15 15 20 30.000 15.000 4.000 6% 2023 2024 NA NA 2023 2024 2023 2024 2023 2024

Already member? Sign In