Mario A Corporation is preparing the budgeted financial statements for the year 2018. The actual balance sheet as of 12/31/17 follows:

Assets

Current assets Cash and equivalents $36,383,000

Short term investments $47,574,000

Net receivables $11,456,000

Inventories $11,850,000 Other current assets $6,843,000

Total current $114,106,000

Non-current assets Long term investments $64,257,000

Property plant and equipment $42,000,000

Other assets $7,985,000

Total non-current $114,242,000

Total assets $228,348,000

Liabilities & Shareholders Equity

Current liabilities Accounts payable $32,842,000 Warranties $7,500,000

Accrued expenses $2,210,000

Revolving line of credit $0

Other current liabilities $1,348,000

Total current $43,900,000

Non-current liabilities

Long term debt $64,093,500

Total non-current $64,093,500

Total liabilities $107,993,500

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 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.

Data sheet

Revenue budget for the year ending 12/31/2018

Purchases budget for the year ending 12/31/2018

Fixed administrative expense budget

Variable administrative expense budget

Interest expense budget

Schedule of cash collections on sales

Schedule of cash collections on investments

Schedule of cash payments for inventory

Schedule of cash payments for warranties

Cash budget for the year ending 12/31/2018

Budgeted income statement for 12/31/2018

Budgeted cost of goods sold schedule for the year ending 12/31/2018

Budgeted statement of retained earnings for the year ending 12/31/2018

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:

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.

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.

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.

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.

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.

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.

2. The Chief Financial Officer (CFO) suggests that the long-term debt be paid down by an additional $32,000,000 by liquidating $20,000,000 of the companys interest bearing short-term investments and using $12,000,000 of cash on hand. The CFO also suggests that $10,000,000 of the short-term investments be moved to long-term investments to earn a higher interest rate since the companys current assets are quite high. A summary of the CFOs suggestions: Sell $30,000,000 short-term investments, purchase $10,000,000 long-term investments, and make an additional long-term debt payment of $32,000,000. This should improve the profit margin and allow the Company the flexibility needed to explore new branding and marketing ideas. Prepare a memo to the President from the CFO analyzing the results of his proposed changes. This should include the ratio results.

The President would like input from his CDO and CMO on possible expansion into new locations and/or an increase in the markup on costs. The President has prepared estimates shown below if expanding to a new location were to take place and/or if the markup on costs for the sales price of the products is raised from the current 50%.

Expansion per location will increase the number of cars sold in each category by 40%. The President has expressed his concerns with expansion and has made it clear he is only comfortable with a maximum of 2 new locations.

For each 10% increase in markup on costs, the number of cars sold in each category will decrease by 20%. (Increasing the sales price to cost + 60% results in a 20% decrease in cars sold, increasing the sales price to cost + 70% results in a 40% decrease in cars sold, etc.)

Budgeted fixed administrative expenses for future planning purposes:

Utilities, maintenance, and facilities	$250,000 increase per location. **
Executive and administrative salaries	$800,000 increase per new location. **
Depreciation	$20,000 increase per new location. **

Budgeted variable administrative expenses follow for future planning purposes: Marketing and promotions	0.2% increase of total sales per new location and/or 0.5% increase of total sales for each 10% increase in the markup on costs.
Bad debt expense	No change.
Sales commission	No change
Warranties	No change.
Shipping and handling costs	No change.
Insurance	No change.

**no change for increase in markup on costs.

Assume you are hired as the CDO. The Chief Development Officer (CDO) has a comprehensive knowledge of all matters related to the business of the organization with an eye towards identifying new sales prospects and driving business growth and expansion. The CDO is exploring expansion options. Prepare a memo style proposal from the CDO to the President. Play with the numbers above and determine whether or not they should expand. If they should, how many new locations will be most beneficial? Keep in mind, the President has expressed his concerns with expansion and has made it clear he is only comfortable with a maximum of 2 new locations.

Assume you are hired as the CMO. The Chief Marketing Office (CMO) is responsible for facilitating growth, sales and marketing strategy. He or she must work towards objectives such as revenue generation, marketing communications (including advertising and promotions), pricing, market research, and customer service. The CMO is exploring an increase in markup. Prepare a memo style proposal from the CMO to the President. Play with the numbers above and determine whether or not they should increase their markup on costs. If so, by how much? Why?

Be sure to compute the following ratios for the President, CFO, CDO, and CMO: gross margin percentage, operating income percentage, debt to equity, debt to assets, current ratio, inventory turnover, asset turnover, and net profit margin. Include the ratio results in the first tab of your excel file AND discuss the ratios in your analysis.

5. Now assume you are the Presidents business consultant. After you have reviewed the four options above, prepare your recommendation to the President as the best proposal for the Companys future performance. This should be a combination of changes, not choosing one of the four above. As part of your proposal include a brief discussion of the above ratio results and include them on the first tab of your excel file. In addition, you must create a data visualization component using Excel to include in your memo. Data visualization helps managers understand the significance of data by placing it in a visual context. Analytics presented visually help managers grasp difficult concepts, summarize numerous options and identify patterns. You must include a minimum of 3 data visualizations in your memo. If you do not know how to use a data visualization function within Excel, you can use the Excel help function by clicking on the question mark. Search chart, graph, or data visualization. Your data visualization component should include data from all five scenarios: President, CFO, CDO, CMO, and Business Consultant.