Please Format into excel sheet

Joe Davis is thinking about starting a company to produce hand carved coo coo clocks. He loves making these clocks and sees it as an opportunity to be his own boss while making a living doing what he likes best. Joe paid $300 for the plans for the first clock, and he has already purchased new equipment costing $2,000 to manufacture the clocks. He estimates that it will cost $30 in materials (wood, clock mechanism, bird and spring, and so on) to make each clock. If he decides to build clocks full time, he would forego his job as a manager at a lumber mill making $75,000 per year. He will need to rent office and manufacturing space, which he thinks would cost $2,500 per month for rent plus another $300 per month for various utility bills (of the $300, Joe notes that $120 of his utility bill is the minimum amount he always has to pay, whether he uses his utilities or not1). Joe would perform all the manufacturing work and run the office himself, so he would like to pay himself a salary of $3,000 per month (enough to live on...). He plans to hire two sales-people at a base salary of $1,000 each per month plus a $7 per clock commission. This way, Joe would not have to take time away from manufacturing to sell the clocks. Joe plans to sell each clock for $225. He believes that he can sell 300 clocks in December for Christmas, but he is not sure what the sales will be during the rest of the year. However, he is fairly sure that the clocks will be popular because he has been selling these clocks as a side hustle for several years now. Overall, he is confident that he can pay all of his business costs, his salary of $3,000 per month, and have at least $4,000 of income (per month and ignoring taxes) that would stay within the business. Youre Job: Youve been hired as a financial analyst to evaluate Joes situation. You must do the following: 1. Perform an analysis of Joes financial situation to estimate the number of clocks Joe would need to sell each year for his business to be financially successful. a. List all of the costs described and indicate whether each cost is i. Fixed cost ii. Variable cost iii. Mixed cost iv. Sunk cost (or not relevant) v. Opportunity Cost b. Calculate the contribution margin per unit and the contribution margin ratio c. Calculate the annual breakeven amount in units and sales (annually, not monthly). d. How many clocks would Joe need to sell annually to earn the Companys target income? 2. Entrepreneurial mindset evaluation: a. Identify uncertainties about the information Joe gave you. What else should you consider in this evaluation? b. How would the information you gathered from #2a above change or affect the breakeven analysis results? c. Recalculate steps #1c and #1d with this new information using your estimates. 1 For purposes of this project, since we cant really determine how much of the utilities Joe spends on each clock, treat utilities as a fixed cost when calculating breakeven and target income. Classify what it really is in part 1a.

Your Deliverables: 1. Write a professional memo using Microsoft Word addressed to Joe with your recommendations. Your memo should reference the schedule (created in #2 of the deliverables) where appropriate and discuss your thoughts and concerns as you reviewed the situation as outlined in this problem. Your job is not to conclude on the situation (tell him go for it, or hes out of his mind), but to indicate important information (data organized in a logical manner) he should consider before he makes his decision. Remember this is a professional memo and should be structured as such. It should NOT be longer than three pages total including the schedule (normal margins, double spaced, Times New Roman font, 11 pt font, black font). 2. Excel spreadsheets showing your calculations, costs, and analysis based on Joes information, and a separate sheet showing the students new calculations if the student believes Joe did not assess his situation to the fullest extent. Student must use correct information from the problem by listing all costs in excel and identifying what type of costs they are as well as identifying any additional costs Joe did not account for and those type of costs. Students must use links and formulas for calculating the required answers. Hardcoding (i.g. simply writing the answer into the cell) will significantly reduce the overall grade of the project. Spreadsheet must be formatted professionally and be presented in a neat and concise manner.