This course contains a Course Project, which will be due at the end of Week 7. There is a template for the project: Link to Course Project TemplateLinks

This course contains a Course Project, which will be due at the end of Week 7. There is a template for the project:

Link to Course Project TemplateLinks to an external site.Open this document with ReadSpeaker docReader Links to an external site. 

The key information and instructions related to the project is listed below:

The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods:

1. The gross margin is 25% of sales.
2. Actual and budgeted sales data:

1. Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result of March credit sales.
2. Each month’s ending inventory should equal 80% of the following month’s budgeted cost of goods sold.
3. One-half of a month’s inventory purchases is paid for in the month of purchase; the other half is paid for in the following month. The accounts payable at March 31 are the result of March purchases of inventory.
4. Monthly expenses are as follows: commissions, 12% of sales; rent, $2,500 per month; other expenses (excluding depreciation), 6% of sales. Assume that these expenses are paid monthly. Depreciation is $900 per month (includes depreciation on new assets).
5. Equipment costing $1,500 will be purchased for cash in April.
6. Management would like to maintain a minimum cash balance of at least $4,000 at the end of each month. The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $20,000. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter.

Using the data provided above, use the Excel Template provided to prepare the following budget schedules:

1. Sales Budget (Merely enter the sales data provided.)
2. Schedule of Expected Cash Collections
3. Merchandise Purchases Budget
4. Schedule of Expected Cash Disbursements – Merchandise Purchases
5. Schedule of Expected Cash Disbursements – Selling and Administrative Expenses
6. Cash Budget

Please note that Excel formulas or functions should be used throughout the budget wherever possible, instead of entering numbers into each cell.

                                                Grading Rubric

LMA: ACCT346/360: W7 Course Project – NOV21CriteriaRatingsPtsThis criterion is linked to a Learning OutcomePreparation of the Sales Budget6 to >5.0 ptsExcellentThe student has properly entered all sales figures into the sales budget, with no errors.5 to >4.0 ptsGoodThe student has entered the sales figures into the sales budget, with one error.4 to >3.0 ptsFairThe student has entered the sales figures into the sales budget with two errors.3 to >0.0 ptsPoorThe student has entered the sales figures into the sales budget with three errors.0 ptsUnacceptableThe student has entered the sales figures into the sales budget with more than three errors, or has not entered the sales figures at all.6 ptsThis criterion is linked to a Learning OutcomePreparation of the Schedule of Cash Collections20 to >17.0 ptsExcellentThe student has correctly identified the sales figures, collection percentages, and has properly calculated the monthly and quarterly cash collections without error.17 to >15.0 ptsGoodThe student has properly identified the sales figures, collection percentages, and has calculated the monthly and quarterly cash collections figures with minor errors.15 to >13.0 ptsFairThe student has properly identified the sales figures, collection percentages, and has calculated the monthly and quarterly cash collections with moderate error, or 70% accuracy.13 to >0.0 ptsPoorThe student is not fully able to identify the sales figures and collection percentages, and therefore unable to accurately calculate the monthly and quarterly cash collections.0 ptsUnacceptableThe student is unable to identify sales figures and collection percentages, leading to completely inaccurate monthly and quarterly cash collections results.20 ptsThis criterion is linked to a Learning OutcomePreparation of the Merchandise Purchases Budget20 to >17.0 ptsExcellentThe student correctly determines the budgeted cost of goods sold, desired ending inventory and beginning inventory figures to calculate the monthly and quarterly merchandise purchases without error.17 to >15.0 ptsVery goodThe student determines the budgeted cost of goods sold, desired ending inventory and beginning inventory figures to calculate the monthly and quarterly merchandise purchases minor errors.15 to >13.0 ptsFairThe student determines the budgeted cost of goods sold, desired ending inventory and beginning inventory figures with minor error, and calculates the monthly and quarterly merchandise purchases with moderate errors.13 to >0.0 ptsPoorThe student incorrectly identifies and calculates the budgeted cost of goods sold, desired ending inventory, and beginning inventory, and calculates the merchandise purchases with significant errors.0 ptsUnacceptableThe student is unable to calculate the budgeted cost of goods sold, desired ending inventory, and beginning inventory, and is unable to calculate the merchandise purchases.20 ptsThis criterion is linked to a Learning OutcomePreparation of the Cash Disbursements Schedule for Merchandise Purchases20 to >17.0 ptsExcellentThe student is able to identify the monthly accounts payable for each month’s purchases, and calculates the monthly and quarterly cash payments without error.17 to >15.0 ptsVery GoodThe student is able to identify the monthly accounts payable for each month’s purchases, and calculates the monthly and quarterly cash payments with minor errors.15 to >13.0 ptsFairThe student is not able to completely identify the monthly accounts payable for each month’s purchases, and calculates the monthly and quarterly cash payments with moderate errors.13 to >0.0 ptsPoorThe student is not able to fully identify the monthly accounts payable for each month’s purchases, and calculates the monthly and quarterly cash payments with significant errors.0 ptsUnacceptableThe student is not able to identify the monthly accounts payable for each month’s purchases, and is unable to calculate the monthly and qua. with significant errors.20 ptsThis criterion is linked to a Learning OutcomePreparation of the Cash Disbursements Schedule for Selling and Administrative Expenses10 to >8.0 ptsExcellentThe student is able to identify and calculate all selling and administrative costs and is able to derive correct monthly and quarterly totals of these costs.8 to >7.0 ptsGoodThe student is able to identify and calculate most selling and administrative costs and is able to derive mostly correct monthly and quarterly totals of these costs.7 to >6.0 ptsFairThe student is able to identify and calculate most selling and administrative costs and is able to derive monthly and quarterly totals of these costs with moderate error.6 to >0.0 ptsPoorThe student is able to identify and calculate most selling and administrative costs and is able to derive monthly and quarterly totals of these costs with moderate error.0 ptsUnacceptableThe student is unable to identify and calculate the selling and administrative costs is unable to derive monthly and quarterly totals of these costs.10 ptsThis criterion is linked to a Learning OutcomePreparation of the Cash Budget30 ptsExcellentThe student is able to identify data from the previous schedules, and appropriately includes them in the cash budget. In addition, the student is able to properly calculate the excess or deficiency of cash, and determine the proper financing, repayment and interest payment.26 ptsVery GoodThe student is able to mostly identify data from the previous schedules, and appropriately includes them in the cash budget. In addition, the student is able to properly calculate the excess or deficiency of cash, and determine the proper financing, repayment and interest payment, with minor errors.23 ptsFairThe student is somewhat able to identify data from the previous schedules, and appropriately includes them in the cash budget. In addition, the student is able to properly calculate the excess or deficiency of cash, and determine the proper financing, repayment and interest payment with moderate error.20 ptsPoorThe student is somewhat able to identify some data from the previous schedules, and includes it in the cash budget. In addition, the student is able to properly calculate the excess or deficiency of cash, and determine the proper financing, repayment and interest payment with significant error.0 ptsUnacceptableThe student is unable to identify data from the previous budget schedules to be included in the cash budget, nor is the student able to identify the excess/deficiency of cash, the amount of financing, repayment, nor the appropriate interest amount.30 ptsThis criterion is linked to a Learning OutcomeUse of Excel Functions and Formulas10 ptsFull MarksThe student is able to utilize Excel formulas and functions to properly calculate results for at least 90% of the calculated items in the workbook.8 ptsGoodThe student is able to utilize Excel formulas and functions to properly calculate results for at 80-89% of the calculated items in the workbook.7 ptsFairThe student is able to utilize Excel formulas and functions to properly calculate results for 70-79% of the calculated items in the workbook.6 ptsPoorThe student is able to utilize Excel formulas and functions to properly calculate results for 60-69% of the calculated items in the workbook.0 ptsUnacceptableThe student is able to utilize Excel formulas and functions to properly calculate results for less than 60% of the calculated items in the workbook.10 pts This criterion is linked to a Learning OutcomeBSAC.ACCT360.CO8.BudgetingEvaluate master budget, flexible budget performance reports, direct labor variances, fixed overhead variances, and create budgets for planning purposes and performance evaluation using techniques such as standard costs, variance analysis, and financial statement analysis.threshold: 3.0 pts4 ptsExcellent: The student submission fully and properly analyzes the budgeting requirements and creates a thorough and accurate master budget.3 ptsGood: The student submission properly analyzes the requirements and creates a mostly thorough and accurate master budget.2 ptsFair: The student submission demonstrates partial analysis of the requirements, and produces a master budget that is mostly complete, and contains moderate errors.1 ptsPoor: The student submission demonstrate little or no analysis of the requirements and produces a mostly incomplete or inaccurate master budget.0 ptsUnacceptable: The student’s submission does not meet the criteria of the outcome.4 ptsTotal Points: 120