Mario A Corporation is preparing the budgeted financial statements for the year 2018. The actual balance sheet as of 12/31/17 follows: Assets Liabilities & Shareholders Equity Current assets Current liabilities Cash and equivalents $36,383,000 Accounts payable $32,842,000 Short term investments $47,574,000 Warranties $7,500,000 Net receivables $11,456,000 Accrued expenses $2,210,000 Inventories $11,850,000 Revolving line of credit $0 Other current assets $6,843,000 Other current liabilities $1,348,000 Total current $114,106,000 Total current $43,900,000 Non-current liabilities Non-current assets Long term debt $64,093,500 Long term investments $64,257,000 Total non-current $64,093,500 Property plant and equipment $42,000,000 Total liabilities $107,993,500 Other assets $7,985,000 Total non-current $114,242,000 Total assets $228,348,000 Shareholders equity Common stock $10,350,000 Treasury stock -$15,219,000 Retained earnings $125,223,500 Total shareholders equity $120,354,500 Total liabilities & shareholders equity $228,348,000 Mario A Corporation Balance Sheet Year end 12/31/2017 Mario A Corporation operates many divisions. One of their divisions, Enzo Legacy sells three models of a luxury automobile built for speed. (Assume all information, financials and data provided pertain to the Enzo Legacy division.) The Testarossa is the largest version which boasts seating for four but still has the ability to reach top speeds in seconds. The Mondial is the top seller with its sleek design, modern technologies, and seating for two. The Scaglietti is the smallest model very similar to the Mondial but slightly faster. Mario As automobiles are highly specialized and they are only sold in Dallas, Texas. People travel from all over the world to see and purchase Mario As automobiles but the President is unhappy with the current profit margins, or lack of, for the Enzo Legacy division. Mario A Corporation is exploring new branding and marketing ideas in an effort to increase business for the Enzo Legacy division. In doing so, the President has asked the Chief Financial Officer, the Chief Development Officer, the Chief Marketing Officer, and you, the business consultant for input and ideas on how to increase profit margins. If the company does nothing different for 2018, the President expects the following sales: Model Sales Price Units Sold Total Testarossa $1,500,000 45 $67,500,000 Mondial $975,000 140 $136,500,000 Scaglietti $600,000 100 $60,000,000 Based upon historical data, 80% of sales revenue is collected in the year of the sale with 10% collected in the year following the sale. 10% of sales is the estimated bad debt amount. In following GAAP, Mario A makes this estimate at year end and records the expense in the year of sale. The net receivables balance on 12/31/2017 consists of the 10% uncollected balance of 2017 sales. On January 1, 2018, beginning inventory is 7 Testarossas, 5 Mondials, and 4 Scagliettis. Desired ending inventory for 2018 is 10 units of each model. Mario A outsources the manufacturing of their automobiles and purchases the finished product. The Testarossa cost is $1,000,000, the Mondial cost is $650,000, and the Scaglietti cost is $400,000. These costs apply to both 2017 and 2018. The current sales prices are based on cost plus a 50% markup. Based upon historical data, 80% of inventory is paid for in the year of purchase with the remaining 20% paid for in the year following the purchase. The accounts payable account is used specifically for inventory purchases. The accounts payable balance on 12/31/17 consists of the 20% unpaid balance of 2017 inventory purchases. Budgeted fixed administrative expenses for 2018 follow: Utilities, maintenance, and facilities $2,000,000 Executive and administrative salaries $9,000,000 Depreciation $2,432,000 Budgeted variable administrative expenses for 2018 follow: Marketing and promotions 6% of Sales Bad debt expense 10% of Sales Sales commission 3% of Sales Warranties 5% of Sales Shipping and handling costs 2% of Sales Insurance 5% of Inventory cost (Purchases) Of the above payable fixed and variable expenses everything except warranties will be paid in cash during 2018. Remember, the depreciation and bad debt expense are non-cash expenses. Based upon historical data, 100% of warranty claims are paid in the year following the sale. The warranty liability on the 12/31/2017 balance sheet is the estimate for 2017 that will be paid in 2018. The three other current liabilities (accounts payable, accrued expenses, and other current liabilities) on the 12/31/2017 balance sheet will be paid with cash during 2018. 15% of the 2018 beginning long-term debt balance will be paid with cash during 2018. At year end, interest is accrued at an annual rate of 7% but is not paid until the following year. Mario A has a $20,000,000 minimum cash balance policy. If the year-end cash balance falls below $20,000,000 Mario A will utilize their revolving line of credit with the bank and borrow the amount necessary to meet the $20,000,000 cash requirement. (This requires an if, then formula in excel and is provided for you on the budgets tab, cell B90.) Mario A budgets an annual 4% return on short-term investments and an annual 8% return on long-term investments. Mario A receives these returns in cash at year-end. Mario A plans to pay out a dividend of 15% of net income on 12/31/2018. (IF, THEN formula provided in excel template, budgets tab, cell B86.) Mario As pretax income is budgeted to be taxed at a rate of 21% and will be paid in cash during 2018. (IF, THEN formula provided in excel template, budgets tab, cell B87.) Requirements: Using the data above, prepare a master budget for Mario A Corporation including the following. Use the excel spreadsheet provided. a. Data sheet b. Revenue budget for the year ending 12/31/2018 c. Purchases budget for the year ending 12/31/2018 d. Fixed administrative expense budget e. Variable administrative expense budget f. Interest expense budget g. Schedule of cash collections on sales h. Schedule of cash collections on investments i. Schedule of cash payments for inventory j. Schedule of cash payments for warranties k. Cash budget for the year ending 12/31/2018 l. Budgeted income statement for 12/31/2018 m. Budgeted cost of goods sold schedule for the year ending 12/31/2018 n. Budgeted statement of retained earnings for the year ending 12/31/2018 o. Budgeted balance sheet for the year ending 12/31/2018 1. Input all of the above estimates proposed by the President if the company does nothing to change the Enzo Legacy division. Note: once you have your excel spreadsheet set up properly (see below), you will only need to make adjustments to your input sheet and everything will flow through. Include the Presidents numbers in the first tab of your excel file and compute the ratios before moving to the CFO. In your analysis, provide a discussion regarding the current operations of the Enzo Legacy division. Is it profitable? Organize the assignment as follows: A. Use the first tab as your title page. The title page SHOULD NOT have any linked cells. Your budgets and financial statements will change depending on your input sheet. Once you change your input values for each of the five options, your budgets and financial statements will change accordingly after you link everything correctly. Take the values generated and put the hard numbers on the title sheet. B. The second tab is the financial statements for 12/31/2017 shown above and should not be changed. You will use these numbers to link for beginning balances. C. The third tab is your input sheet. All of the lines are provided for you, but you need to input the values in the highlighted cells. D. The fourth tab is your budgets sheet. The lines are provided for you, but it is your responsibility to link the cells correctly. This includes b-k above. (No hard numbers*) Once your spreadsheet is linked correctly, you should NOT need to adjust this tab. You will ONLY make changes to the Data tab and everything else should flow through. E. The fifth tab is your projected financial statements including linked cells to budgets sheet and input sheet. Again, the lines are provided for you, but it is your responsibility to link the cells correctly. This will include l-o above. (No hard numbers*) Again, once your spreadsheet is linked correctly, you should NOT need to adjust this tab. You will ONLY make changes to the Data tab and everything else should flow through. F. The sixth tab will be your ratio computations. Link to the other tabs in the spreadsheet to calculate the correct ratios. IMPORTANT NOTE: Once you set the spreadsheet up correctly, you will be able to change minor items on the data sheet to see how it affects the budgeted financial statements, ratios, etc. This is a true method of what-if analysis. You can ask yourself, what if we increase mark up by x amount? Or what if we expand to this many locations? What if we change these expenses? Etc. With just a few changes to the data tab, you can see how this will affect everything! Play with the spreadsheet once you have it set up and look at the big picture. This will help in your analysis and recommendations to the President. IMPORTANT NOTE 2: You will turn in your analysis Word/pdf document AND your Excel spreadsheet. I want the spreadsheet that you turn in to have the Presidents projected numbers for the Enzo Legacy division assuming no changes are made. You will make several changes to the data tab throughout the project and your analysis, but change it back to the Presidents suggestions before submitting it. I will provide check figures before the project is due so you can ensure your spreadsheet is correct, and the check figures will be based on the Presidents estimates. G. A separate word document will be the discussion and your proposals/memos to the President. The word document will include an analysis of the Presidents numbers and memos from the CFO, CDO, CMO, and business consultant. *The budgets sheet and the financial statement sheet cannot have any hard numbers. Each cell should link to another cell within the workbook or should have an equation. When completing the suggestions below, you will include the numbers generated in the first tab of your excel file and compute the ratios before moving on to the next suggestion.