I have to make an Excel document providing all the information given. The class is CIS. Basically what you have to do is make an Excel spreadsheet with all the assumptions and costs given. Then you have to change the costs with the future assumptions and show how they get affected and what prices they change to.
Background Information: Your boss, Mr. Banks has asked you to complete the following problems. He is going out of the country, so you will not show him your work at this time. If you have any questions, he set up a Blackboard discussion board called Excel Assignments," where these questions should go. He will return on the morning of Monday, March 1st, when your assignment should be already submitted to Blackboard for his review (Submission Due by Monday, March 1st, 10 AM). Problem 1: Assume that August 2019 results are actual. BBP, Bernard Baruch's Paper Co., is using its financial results for August 2019 (Table 1) as a base for projecting the company's budget for the remaining four months of the year (September-December). Table 1: Financial results for August 2019 (000) REVENUE August Wedding Invitations 4,507 Birthday Party 3,841 Invitations Business Party 2,652 Invitations Total Sales Revenue 11,000 EXPENSES Printing Costs Salaries Administration Advertising Legal Fees Shipping Total Expenses 4,400 3,500 550 1,544 788 928 11,710 Net Income before Taxes Taxes Net Profit (loss) -710 0 -710 Here are the assumptions about how costs relate to revenues and the growth assumptions for the future: 1. Sales revenue from wedding invitations, birthday party invitations and business party invitations will grow at 4%, 8% and 4% per month, respectively. 2. Printing costs are calculated as 40% of Sales Revenue for the month, and Administration expenses are calculated as 5% of the Sales Revenue for the month. 3. Salaries are fixed for the period. Please create an assumption. 4. The other expense items will grow at the following rates (per month): Advertising and Shipping at 5% each and Legal Fees at -3% 5. The tax rate is 28% on profits for the month. Assume that taxes are calculated and paid each month. Note that when the company loses money, it does not pay taxes. Enter the figures for August of 2019 on your spreadsheet. Make sure that you use formulas where appropriate for this base month. All figures should be expressed in thousands of dollars (as they are here), amounts should be formatted with commas and no decimals, and growth rates should appear as percentages with no decimals unless indicated otherwise. Place all growth assumptions and other parameters used in your calculations together in the lower left corner of your spreadsheet with a boxed outline (similar to lab examples) and use named ranges and relative references where appropriate in your formulas. There are five parts to this problem: 1. Forecast the budget for the next four months of 2019 (Sept-Dec) on a worksheet of your spreadsheet and enter the name Budget in the index tab. 2. Submit 2 well-designed charts that include all required labels and looks professional: (1) A line chart showing the trend over the entire period in total sales revenue, total expenses and net profits. Be sure to select the right graph type and label the chart well in order to indicate that you are displaying the results in thousands of dollars. Save the chart on a separate worksheet, enter the name "Trend" in the index tab. The chart should not be "embedded" (which looks copied with gridlines in the background), but on a new sheet clearly, large and presentable with all proper labels. (2) A pie chart showing the proportional distribution of expenses in December. Be sure to label the chart well. Save the chart on a different sheet and enter the name "Distribution" in the index tab. The chart should not be "embedded" (which looks copied with gridlines in the background), but on a new sheet clearly, large and presentable with all proper labels. 3. Copy the budget to another sheet of your spreadsheet in order to make some changes to the assumptions. Enter the name "WhatIF" on the index tab of this worksheet. Suppose that wedding invitation sales will grow at only 3%, birthday party invitation sales at 7% and business party invitations at only 2%. Suppose also that the growth in shipping expenses will be 2%. In what month will BBP first show a profit under these assumptions? You should indicate which month is the first profitable one by adding an on- screen comment (not a text box) comment to the appropriate cell of the spreadsheet. No comments should block any of the data! 4. Copy the original budget to another sheet in the spreadsheet. Again, suppose that wedding invitation sales will grow at only 3%, business party invitations at 2% and advertising and shipping at 5%, what percentage growth in birthday party invitation revenue is needed for PPP to show a net profit of $100 in December? Format the cell with your answer using two decimal place precision (e.g. 5.87%). DO NOT change the content of this cell, just format it. Add an on-screen comment (not a text box) to indicate which birthday party invitation growth rate will lead to the target net profit in December. Enter the name Target" on the index tab of this worksheet. 5. Using the Budget" worksheet, use Conditional Formatting on the data for the following. If any of the numbers for Net Income before Taxes, Taxes and Net Profit (loss) is greater than 0, then the font for that cell should be green. If any of the numbers for Net Income before Taxes, Taxes and Net Profit (loss) is less than 0, then the font for that cell should be red; otherwise the font for that cell should be orange
