Question

In: Accounting

The beauty behind excel is that managers can perform what-if analysis just by changing the data,...

The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you have used cell references and formulas throughout.

Data Scenario: You have just been hired into a management position which requires the application of your budgeting skills. You find out that budgeting has not been a priority of the company and that they have been experiencing cash shortages. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a comprehensive budget.                                                                                                                              

The following is actual information that relates to the operations of a merchandiser named Sled Company, a wholesaler of sleds as of March 31.          Cash $1,000 Accounts receivable 13,680 Inventory 10,757 Accounts Payable $15,781

                                                                                               

Actual and Budgeted sales dollar Data-Sales Budget: March (actual) $38,000 April $36,000 May $37,000 June $39,000 July $35,000                                                                                      

                                                                                                                               

Sales are the following type: 64% Cash sales collected in month of sale 36% Credit sales collected in the following month of sale    Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result of March credit sales. Cost of goods sold equals    83%       of sales price                                                                     

At the end of each month, inventory is to be on hand(ending inventory) equal to 36% of following month's sales needs, stated at cost. Inventory purchases are paid 49% in month of purchase 51% in month after purchase. The accounts payable at March 31 is a result of March purchases of inventory. I have set up a calculation on the budget worksheet to show you this calculation.                                                                                                                      

Monthly selling expenses are as follows and are paid in the month incurred if it is a cash expense. Salaries and wages 8% of sales dollar Commissions 3% of sales dollar Advertising      $900     per month Utilities          $200 per month                                                                               

                                                                                                                               

Monthly general and administrative expenses are as follows and are paid in the month incurred if it is a cash expense. Rent $2,000 per month Depreciation $500 for month                                                                     

                                                                                                                               

Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet that contains the data.              Prepare the following second quarter budgets and answer the questions listed on the template provided on the BudgetSolution Worksheet. I have adapted the budget model to meet the needs of this company. If I bolded a line item, that is a header and does not need computation on that row. Please note the quarter column is for the quarter so not all lines should be added across in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter and should be brought over to the quarter column.                                                                                                                                                                                                                                                                                                                        

7. Prepare a budgeted income statement for the quarter ending June 30, 20XX. You do not need to show monthly columns.              I entered Sales for you on the income statement

                                                               

8. What do you think about the survivability of this business?                                                                                                                    

9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why? What is the New Net income(Loss) for the quarter?                                                                                                                         

If you have linked everything correctly, you should only have to change the monthly rent on this sheet to determine your answer to the questions asked. Please change the rent back to the original amount of 2,000 before you submit.               

Solutions

Expert Solution

7.

Sled Company
Budgeted Income Statement
For the quarter ended June 30, 20XX
Sales revenue $ ( 36,000 + 37,000 + 39,000) $ 112,000
Cost of Goods Sold ( $ 112,000 x 83 %) 92,960
Gross Profit 19,040
Selling and Administrative Expenses
Sales Commission Expense ( $ 112,000 x 3 %) $ 3,360
Advertising Expense ( $ 900 x 3) 2,700
Salaries and Wages Expense ( $ 112,000 x 8%) 8,960
Utilities Expense ( $ 200 x 3) 600
Rent ( $ 2,000 x 3) 6,000
Depreciation ( $ 500 x 3) 1,500
Total Selling and Administrative Expenses 23,120
Net operating income ( loss) $ (4,080)

8. At the current sales volume, the company is unable to earn a net operating income. Cost of goods sold accounts for 83% of sales revenue, and salaries and wages and sales commision accounts for another 11 %. Hence contribution margin ratio is only 6 %. That is not enough to absorb fixed costs, let alone earning profit.

Therefore, unless the company can substantially increase its selling price, the survivability of the business is very doubtful. Increasing the sales volume can also help improving the sustainability, provided the fixed expenses do not change.

9. Increase in monthly rent would impact the General and Administrative Expense Budget. The new net loss for the quarter would be $ 5,580.


Related Solutions

The beauty behind excel is that managers can perform what-if analysis just by changing the data,...
The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you have used cell references and formulas throughout. Data Scenario: You have just been hired into a management position which requires the application of your budgeting skills. You find out that budgeting has not been a priority of the company and that they have been experiencing cash shortages. You have contacted various areas on...
How can you perform simple linear regression analysis using Excel?
How can you perform simple linear regression analysis using Excel?
i. Use MS Excel Data Analysis ToolPak to perform a multiple regression analysis using Quality as...
i. Use MS Excel Data Analysis ToolPak to perform a multiple regression analysis using Quality as the response variable and Helpfulness and Clarity as the explanatory variables. Write down the corresponding coefficient estimates and provide the regression output. j. Perform an F-test for the overall usefulness of the model in part i) using a 5% significance level. Make sure you follow all the steps for hypothesis testing indicated in the Instructions section and clearly state your conclusion. k. Test manually...
You perform the analysis on EXCEL and write out your analysis result in a separate word...
You perform the analysis on EXCEL and write out your analysis result in a separate word or pdf file. Submit both EXCEL and WORD / PDF file. Download daily adjusted close price from AAPL and SPY for the last 10 years INTO Spreadsheet. Create daily return. 1. What is the daily mean return and daily stdev for AAPL and SPY return? What is the correlation between the two? Questions below DO NOT NEED EXCEL (only need numerical values from part...
What information might a changing stock price give to managers?
What information might a changing stock price give to managers?
The focus of the final paper is an evaluation of how data analysis is changing the...
The focus of the final paper is an evaluation of how data analysis is changing the healthcare industry. Choose one of the following topics: Chosen topic: Improving patient outcomes For your Final Paper: Include a discussion of the chosen topic and how it is changing the healthcare industry. Evaluate a minimum of three barriers of data analysis related to the topic. Describe any national initiatives related to the topic. Explain any financial incentives related to the topic. Describe any accreditation...
What is the theory behind GC and IR analysis?
What is the theory behind GC and IR analysis?
8. What are 3 ways in that CVP analysis can be used by managers to make...
8. What are 3 ways in that CVP analysis can be used by managers to make decisions?
Q1. What software other than Excel may work to perform substantive tests on data? Would it...
Q1. What software other than Excel may work to perform substantive tests on data? Would it make the task easier or more difficult? Q2. How can pivot tables be used to analyze data? What are some very useful formulas and shortcuts you have found to make tasks easier in Excel?
What kind of data lend themselves to perform an analysis of variance and/or an independent-samples t...
What kind of data lend themselves to perform an analysis of variance and/or an independent-samples t test? Given your answer above, why should you use analysis of variance instead of just using an independent-samples t test?
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT