Expert answer:tableau case

Expert answer:here is a instruction file and you need to finish Excel.
tableau_case.docx

student_copy_cvp_budget_supplement__store_80_master_budget___1_.xlsx

Unformatted Attachment Preview

Copyright © 2017 Marko Begonja, William Goldman (the Authors) and Northeastern University
Copyright © 2017 Marko Begonja, William Goldman (the Authors) and Northeastern University
All rights reserved. For Educational Purposes Only. No part of the Bibitor, LLC dataset nor the
supporting materials may be reproduced, distributed, or transmitted in any form or by any means,
including photocopying, recording, or other electronic or mechanical methods, without the prior
written permission of the Authors or Northeastern University, except in the case of brief quotations
embodied in critical reviews and certain other noncommercial uses permitted by copyright law.
Indirect use for commercial purposes (i.e., materials referencing or using these materials) is also
Sales Budget
National Annual Revenue
Expected Total Revenue
$0
Month
National Monthly Sales
Monthly % of Total Sales Revenue
Expected Monthly Revenue
Projected Sales Revenue Distributed By Month Based on Total National Sales Revenues
Budgeted Sales
Cash Sales
Collectible Sales on Account
Monthly Change Allowence for Doubtful Accounts*
Total Collectible Sales
0%
Cash Collections
Current Cash Collections
Collections of A/R*
Collections on Prior A/R
Total Budgeted Collections
0%
Pro-Forma Sales Revenue
$
–
$
–
Total Sales on Credit
Total Collections on Sales on Credit
Pro-Forma Gross Accounts Receivable
Pro-Forma AFDA
Beginning Balance
Additions
Write-Offs
Total AFDA
*Assuming full amount is used:
Pro-Forma Bad Debt Expense
Pro-Forma Net Accounts Receivable
#DIV/0!
$
#DIV/0!
Input Assumptions
Input from Source Data
July
August
September
October
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$0.00
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
(negative indicates a credit balance)
#DIV/0!
#DIV/0!
November
December
January
February
March
April
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
May
June
Total
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Inventory Purchase Budget
Projected Annual COGS for Store 80:
National Annual COGS
$
July
Month
Average National Monthly COGS
National COGS/National Sales Ratio
#DIV/0!
Expected COGS for new store per month
#DIV/0!
Cost of Budgeted Sales
Plus: Desired Ending Inventory
Total Desired Inventory/Month
Less: Beginning Inventory
Amount to Purchase
#DIV/0!
#DIV/0!
#DIV/0!
Amount owed on inventory prior to payments
#DIV/0!
Payment of Current Month’s Purchase
Payment of Prior Month’s Purchase
Total Cash Paid Towards Inventory
#DIV/0!
#DIV/0!
$0
0%
Pro-Forma Ending Inventory
#DIV/0!
Pro-Forma Cost of Goods Sold (COGS)
#DIV/0!
Inventory Purchased
Amount Paid for Inventory
Pro-Forma Accounts Payable for Inventory
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Input Assumptions
Input from Source Data
August
September
October
November
December
January
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
February
March
April
May
June
Total
$
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
–
Expenses to be Allocated
Input Assumptions
Input from Source Data
Number of Stores to be Opened:
Number of Sq. Ft. to be Opened:
Number of Bottles Expected to be Sold:
Current Number of Stores
Total Square Footage
Total Quantity Sold
For the purposes of this model, the “Number of Store to be Opened” should be set to 1 (Store 80). “Sq. Ft. to be Opened” and “Number of
1a) Expense Allocation
Choose an Allocation Method from Each
Cell’s Drop Down List:
Based on Square Footage
Based on Quantity Sold
Expenses to be Allocated
Salary Expense
Advertising
Rent
Miscellaneous
Repairs and Maintenance
Telecommunications
Utilities
Freight
Total Cost for Year
be Opened” and “Number of Bottles Expected to be Sold” should be equal to the national medians, respectively (unless instructed differently).
Additional Cost Assumptions: Allocated Monthly Cost
#DIV/0!
#DIV/0!
Payment Terms
Selling, Administrative, & General Expenses Budget
Projected SG&A Expenses
Month
Salary Expense
Advertising
Rent
Miscellaneous
Repairs and Maintenance
Telecommunications
Utilities
Freight
Total SG&A Expense Before Interest
Schedule of Cash Payments
Salary Expense
Advertising
Rent
Miscellaneous
Repairs and Maintenance
Telecommunications
Utilities
Freight
Total SG&A Expense Before Interest
Pro-Forma Operating Expenses (w/o COGS)
#DIV/0!
Salaries Expense
Salaries Paid
Pro-Forma Salaries Payable
$
$
$
–
Advertising Expense
Advertising Paid
Pro-Forma Advertising Payable
$
$
$
–
Rent Expense
Rent Paid
Pro-Forma Rent Payable
$
$
$
–
Miscellaneous Expense
$
–
Miscellaneous Paid
Pro-Forma Miscellaneous Payable
$
$
–
Repairs and Maintenance Expense
$
Repairs and Maintenance Paid
$
Pro-Forma Repairs and Maintenance Payable $
–
Telecommunications Expense
Telecommunications Paid
Pro-Forma Telecommunications Payable
–
Utility Expense
Utility Paid
Pro-Forma Utilities Payable
$
$
$
#DIV/0!
#DIV/0!
#DIV/0!
Input Assumptions
Input from Source Data
July
August
September
October
November
December
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
January
February
March
April
May
June
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Total
$
$
$
$
$
$
#DIV/0!
#DIV/0!
–
#DIV/0!
$
$
$
$
$
$
#DIV/0!
#DIV/0!
#DIV/0!
Cash Budget
Input Assumptions
Input from Source Data
Month
Cash Receipts
Beginning Cash Balance
Add: Cash Receipts
Total Cash Available
Cash Disbursements
Inventory Purchases
SG&A Expenses
Interest Expense
Marketing Start-up Costs
July
August
$
–
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$0
#DIV/0!
#DIV/0!
#DIV/0!
Total Disbursements
#DIV/0!
#DIV/0!
Surplus/Shortage for current time period
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Financing Activity
Borrowing/Repayment
Borrowing Balance
Required Cushion
Cash Balance
Pro-Forma Interest Expense
Pro-Forma Start Up Costs
Pro-Forma Line of Credit
Pro-Forma Cash
#DIV/0!
$
#DIV/0!
#DIV/0!
September
October
November
December
January
February
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
March
April
May
June
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Pro-Forma Income Statement
Sales Revenue
COGS
Gross margin
SG&A Expenses
Bad Debt Expense
Interest Expense
Start up Expense
Net Income
$
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$
#DIV/0!
Pro-Forma Balance Sheet
Assets
Cash
Gross A/R
AFDA
Net A/R
Inventory
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Total Assets
#DIV/0!
Liabilities
Accounts Payable
A/P Salary
A/P Advertising
A/P Rent
A/P Miscellaneous
A/P Repairs/Maintenance
A/P Telecommunications
A/P Utilities
A/P Inventory
A/P Line of Credit
$
$
$
$
$
$
#DIV/0!
#DIV/0!
#DIV/0!
Total Liabiities
#DIV/0!
Owner’s Equity
Retained Earnings
#DIV/0!
Total Liabilities + Owner’s Equity
#DIV/0!
Difference:
#DIV/0!
Pro-Forma Statement of Cash Flows
Operating Activities
Cash Receipts from Customers
Cash Payments for Inventory
Cash Disbursments for SG&A
Cash Paid for Interest
Cash Paid for Start-up Expense
Net Change in Cash from Operations
Investing Activities
Cash From (Used In) Investing
Net Change in Cash from Investing
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$
#DIV/0!
$
$
Financing Activites
Cash Borrowings (LOC)
Cash Repayments (LOC)
Net Change in Cash from Financing
Net Change in Cash
Cash Beginning of the Year
Cash End of Year
–
–
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$
#DIV/0!
…
Purchase answer to see full
attachment

How it works

1. Paste your instructions in the instructions box. You can also attach an instructions file
2. Select the writer category, deadline, education level and review the instructions 
3. Make a payment for the order to be assignment to a writer
4.  Download the paper after the writer uploads it 

Will the writer plagiarize my essay?

You will get a plagiarism-free paper and you can get an originality report upon request.

Is this service safe?

All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades